一、环境准备
建表：
1
2
3
4
5
6
7
8
create table test_part(id int auto_increment ,
days int unsigned not null default 19700101,
primary key (id,days))
partition by range(days) (
partition p202206 values less than (20220601),
partition p202207 values less than (20220701),
partition p2022 values less than maxvalue
);
（1）在已有maxvalue的分区表中添加新的分区
1
2
3
ALTER TABLE test_part ADD PARTITION (PARTITION p202208 VALUES LESS THAN (20220801) ENGINE = InnoDB);
报错：
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition
（2）重新定义分区分布
1
2
3
4
5
6
7
alter table test_part
PARTITION BY RANGE (`days`)
(PARTITION p202206 VALUES LESS THAN (20220601) ENGINE = InnoDB,
PARTITION p202207 VALUES LESS THAN (20220701) ENGINE = InnoDB,
PARTITION p202208 VALUES LESS THAN (20220801) ENGINE = InnoDB,
PARTITION p2022 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
可以执行，会迁移在原来p2022分区中的数据，迁移过程加share lock：
- Only permits ALGORITHM=DEFAULT, LOCK=DEFAULT. Does not copy existing data for tables partitioned by RANGE or LIST. Concurrent queries are permitted for tables partitioned by HASH or LIST. MySQL copies the data while holding a shared lock.
（3）直接拆分maxvalue的分区
MySQL支持重新分配分区：
1
alter table test_part REORGANIZE PARTITION p2022 into ( partition p202209 VALUES LESS THAN (20220901) ENGINE = InnoDB, PARTITION p2022 VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
