Shuo
Shuo I'm a DBA(Database Administrator), we can share and discuss MySQL, MongoDB, Redis and other databases here, also including learning Python, Shell, Golang together.

MySQL Partition Tables: Split New Partition From Maxvalue Partition

MySQL Partition Tables: Split New Partition From Maxvalue Partition

一、环境准备

建表:

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)

参考:Management of RANGE and LIST Partitions

comments powered by Disqus