mysql中创建partition表的几种方式-ag真人游戏

os : linux

数据库：mysql 8.0.25

mysql中创建partition表的几种方式如下，这几种方式都是经过验证过的，只需将engine=xxx修改即可：

1. partition by range

drop table if exists employees;
create table employees (id int not null, fname varchar(30), lname varchar(30), hired date not null default '1970-01-01', separated date not null default '9999-12-31', job_code int not null, store_id int not null) engine=xxx partition by range (store_id) (partition p0 values less than (6), partition p1 values less than (11), partition p2 values less than (16), partition p3 values less than (21));
insert into employees(id,job_code,store_id) values(1,1001,5),(2,1002,10),(3,1003,15),(4,1004,20);

2.partition by list

drop table if exists employees;
create table employees (s1 int) engine=xxxx partition by list (s1) (partition p1 values in (1), partition p2 values in (2), partition p3 values in (3), partition p4 values in (4), partition p5 values in (5));
insert into employees values (1), (2), (3), (4), (5);

3.partition by list columns

drop table if exists employees;
create table employees(c1 int, c2 int, c3 int, c4 int, primary key (c1,c2)) engine=xxxx partition by list columns (c2) (partition p1 values in (1,2), partition p2 values in (3,4));
insert into employees values (1, 1, 1, 1), (2, 3, 1, 1);
insert into employees values (1, 2, 1, 1), (2, 4, 1, 1);
select * from employees;

4.partition by hash

drop table if exists employees;
create table employees (c1 int primary key, c2 varchar(50))engine=xxxx partition by hash (c1) partitions 2 (partition p1, partition p2);
insert into employees values(1, 'aa'), (2, 'bb'), (3, 'cc'), (4, 'dd');

5.partition by key

drop table if exists employees;
create table employees (c1 int primary key, c2 varchar(50)) engine = xxx partition by key(c1) partitions 2 (partition p1, partition p2);
insert into employees values(1, 'aa'), (2, 'bb'), (3, 'cc'), (4, 'dd');

6.subpartition by hash

drop table if exists employees;
create table employees (c1 int primary key, c2 int) engine=xxxx partition by range(c1) subpartition by hash (c1) subpartitions 2 (partition p0 values less than (100) (subpartition s0, subpartition s1), partition p1 values less than (200) (subpartition s2, subpartition s3));
insert into employees values(1, 1), (10, 10), (101, 101), (199, 199);

其中，个人比较喜欢的方式是partition by hash(c) partitions xxx.

欢迎补充其他的方式。