添加新的分区，报错代码如下：
mysql> alter table t1 ADD PARTITION(partition p3 values less than(200));
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition
表结构：
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);
插入数据：
insert into t1 values(129,'jim','smith'),(137,'mary','jones'),(16,'frank','withe'),(105,'linda','black');
观察分区表中的数据:
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：
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 t1\G
*************************** 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) */
检查：
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)
