专业网站设计建设公司,响应式网站模板百度云,访问wap网站,可以做网站的公司最近在用MySQL开发新功能时#xff0c;使用到了alter table range partition的功能#xff0c;在此总结下mysql innodb支持的alter table range partition相关功能。mysql的版本是8.0.22, os: linux ubuntu
对alter range partition的操作主要由以下几个#xff1a;
analy…最近在用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))ENGINEinnodb partition by range (aa)
partitions 3
(partition p1 values less than (5) ENGINEGreatDB,partition p2 values less than (10) ENGINEGreatDB,partition p3 values less than (50) ENGINEGreatDB);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_namett;
(1)、optimize partition
innodb会提示The storage engine for the table doesnt 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 doesnt 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 doesnt 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即可。
