RANGE 分区(支持删除分区)
create table user(
id int primary key,
username varchar(255)
)engine=innodb
partition by range(id)(
partition p0 values less than(100),
partition p1 values less than(200),
partition p2 values less than maxvalue
);
LIST 分区(支持删除分区)
create table user(
id int,
username varchar(255),
password varchar(255),
gender int,
primary key(id, gender)
)engine=innodb
partition by list(gender)(
partition man values in (1),
partition woman values in (0));
HASH 分区(只支持数字分区,不支持删除)
create table user(
id int,
username varchar(255),
password varchar(255),
gender int,
primary key(id, gender)
)engine=innodb partition by hash(id) partitions 4;
KEY 分区(默认主键或唯一索引分区)
create table user(
id int,
username varchar(255),
password varchar(255),
gender int,
primary key(id, gender)
)engine=innodb partition by key(id) partitions 4;
常见分区命令
添加分区:
alter table user add partition (partition p3 values less than (4000)); -- range 分区
alter table user add partition (partition p3 values in (40)); -- lists分区
删除表分区(会删除数据):
alter table user drop partition p30;
删除表的所有分区(不会丢失数据):
alter table user remove partitioning;
重新定义 range 分区表(不会丢失数据):
alter table user partition by range(salary)(
partition p1 values less than (2000),
partition p2 values less than (4000));
重新定义 hash 分区表(不会丢失数据):
alter table user partition by hash(salary) partitions 7;
合并分区:把 2 个分区合并为一个,不会丢失数据:
alter table user reorganize partition p1,p2 into (partition p1 values less than (1000));