mysql分区操作

RANGE分区

创建分区

create table user_temp(
id int not null,
name varchar(30),
age int
)
PARTITION by range(id)(
PARTITION part1 values less than (20),
PARTITION part2 values less than (40),
PARTITION part3 values less than (60)
);

添加分区

• 如果已有分区，新增分区

alter table user_temp add paritition (
partition by range(id) part4 values less than (80)
);

• 如果没有分区，新增分区

alter table user_temp PARTITION by range(id)(
PARTITION part0 values less than (4),
PARTITION part1 values less than (8),
PARTITION part2 values less than MAXVALUE
)

删除分区

alter table user_tempdrop partition part1

LIST分区

LIST创建、添加、删除分区和RANGE分区一样，只不过关键词换了下

创建分区

create table user_temp(
id int not null,
name varchar(30),
age int
)
PARTITION by list(id)(
PARTITION part1 values less than (20),
PARTITION part2 values less than (40),
PARTITION part3 values less than (60)
);

添加分区

• 如果已有分区，新增分区

alter table user_temp add paritition (
partition by list(id) part4 values less than (80)
);

• 如果没有分区，新增分区

alter table user_temp PARTITION by list(id)(
PARTITION part0 values less than (4),
PARTITION part1 values less than (8),
PARTITION part2 values less than MAXVALUE
)

删除分区

alter table user_tempdrop partition part1

不管是RANGE分区还是LIST分区，只支持整数分区，为解决该问题，引出了COLUMNS分区

RANGE COLUMNS分区

创建分区

create table user_temp(
id int not null,
name varchar(30),
age int
)
PARTITION by range COLUMNS(id)(
PARTITION part1 values less than (20),
PARTITION part2 values less than (40),
PARTITION part3 values less than (60)
);

添加分区

• 如果已有分区，新增分区

alter table user_temp add paritition (
partition by range COLUMNS(id) part4 values less than (80)
);

• 如果没有分区，新增分区

alter table user_temp PARTITION by range COLUMNS(id)(
PARTITION part0 values less than (4),
PARTITION part1 values less than (8),
PARTITION part2 values less than MAXVALUE
)

删除分区

alter table user_tempdrop partition part1

LIST COLUMNS分区

LIST分区同理

HASH分区

这个我没用过，就先不多做赘述了，以免误人子弟

使用分区查询

select * from 表名 PARTITION(分区名) where 条件

在mybatis代码中使用分区

select * from 表名 PARTITION(${分区名}) where 条件