MySQL中创建partition表的几种方式

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.

欢迎补充其他的方式。