MySQL中alter table range partition

最近在用MySQL开发新功能时，使用到了alter table range partition的功能，在此总结下mysql innodb支持的alter table range partition相关功能。mysql的版本是8.0.22, os: linux ubuntu

对alter range partition的操作主要由以下几个：

analyze partition
add partition
drop partition
truncate partition
remove partitioning
check partition
repair partition
rebuild partition
reorganize partition
coalesce partition

每个操作对应的具体功能就不多说了，网上的说明太多了，自己找吧，主要讲述SQL语句的写法，建表语句如下：

CREATE TABLE tt (
aa int not null,
bb int not null,
cc int not null,
primary key(aa,bb))ENGINE=innodb partition by range (aa)
partitions 3
(partition p1 values less than (5) ENGINE=GreatDB,
 partition p2 values less than (10) ENGINE=GreatDB,
 partition p3 values less than (50) ENGINE=GreatDB);

INSERT into tt values (1, 1, 1);
INSERT into tt values (6, 1, 1);
INSERT into tt values (10, 1, 1);
INSERT into tt values (15, 1, 1);


查询分区name的SQL语句如下：
select partition_name part, table_rows from information_schema.partitions where table_name='tt';

(1)、optimize partition

innodb会提示：The storage engine for the table doesn't support optimize  使用的mysql的版本是8.0.22. innodb也不支持这一功能

alter table tt optimize partition p1;

(2)、analyze partition

alter table tt analyze partition p1;
alter table tt analyze partition all;

(3)、check partition

innodb会提示：The storage engine for the table doesn't support check  使用的mysql的版本是8.0.22. innodb也不支持这一功能

alter table tt check partition p2;
alter table tt check partition all;

(4)、repair partition

innodb会提示：The storage engine for the table doesn't support repair  使用的mysql的版本是8.0.22. innodb也不支持这一功能

alter table tt repair partition p3;

(5)、rebuild partition

alter table tt rebuild partition all;
alter table tt rebuild partition p3;

(6)、reorganize partition

alter table tt reorganize partition p1, p2 into (partition p1 values less than(20));

(7)、coalesce partition

alter table tt coalesce partition 1;

(8)、truncate partition

alter table tt truncate partition all;

(9)、add partition

ALTER TABLE tt ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000)); 

(10)、drop partition

alter table tt drop partition p3;

(11)、remove partition

alter table tt remove partitioning;

总之，innodb支持的alter table range partition功能就是上面的这些，代码看mysql 8.0.22即可。