MySQL分区表MAXVALUE can only be used in last partition definition错误
添加新的分区，报错代码如下：
代码语言：javascript复制mysql> alter table t1 ADD PARTITION(partition p3 values less than(200));
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition表结构：
代码语言：javascript复制CREATE TABLE t1 (
id int NOT NULL AUTO_INCREMENT,
fname varchar(30) DEFAULT NULL,
lname varchar(30) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (50) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (150) ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB);插入数据：
代码语言：javascript复制insert into t1 values(129,'jim','smith'),(137,'mary','jones'),(16,'frank','withe'),(105,'linda','black');观察分区表中的数据：
代码语言：javascript复制mysql> select partition_name,table_rows from information_schema.partitions where table_name='t1';
---------------- ------------
| PARTITION_NAME | TABLE_ROWS |
---------------- ------------
| p0 | 0 |
| p1 | 0 |
| p2 | 3 |
| pmax | 0 |
---------------- ------------
4 rows in set (0.00 sec)
mysql> select * from t1;
----- ------- -------
| id | fname | lname |
----- ------- -------
| 16 | frank | withe |
| 105 | linda | black |
| 129 | jim | smith |
| 137 | mary | jones |
----- ------- -------
4 rows in set (0.00 sec)解决方案
通过REORGANIZE PARTITION语法重新添加新分区P3：
代码语言：javascript复制mysql> ALTER TABLE t1 REORGANIZE PARTITION pmax
-> INTO ( PARTITION p3 VALUES LESS THAN (200)
-> , PARTITION pmax VALUES LESS THAN MAXVALUE);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t1G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int NOT NULL AUTO_INCREMENT,
`fname` varchar(30) DEFAULT NULL,
`lname` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=138 DEFAULT CHARSET=utf8mb3
/*!50100 PARTITION BY RANGE (`id`)
(PARTITION p0 VALUES LESS THAN (50) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (150) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (200) ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */检查：
代码语言：javascript复制mysql> insert into t1 values(188,'jack','red');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
----- ------- -------
| id | fname | lname |
----- ------- -------
| 16 | frank | withe |
| 105 | linda | black |
| 129 | jim | smith |
| 137 | mary | jones |
| 188 | jack | red |
----- ------- -------
5 rows in set (0.00 sec)
mysql> select partition_name,table_rows from information_schema.partitions where table_name='t1';
---------------- ------------
| PARTITION_NAME | TABLE_ROWS |
---------------- ------------
| p0 | 0 |
| p1 | 0 |
| p2 | 3 |
| p3 | 1 |
| pmax | 0 |
---------------- ------------
5 rows in set (0.00 sec)
mysql> alter table t1 CHECK PARTITION p3;
--------- ------- ---------- ----------
| Table | Op | Msg_type | Msg_text |
--------- ------- ---------- ----------
| test.t1 | check | status | OK |
--------- ------- ---------- ----------
1 row in set (0.00 sec)
