手动创建KEY-*二级分区

创建KEY-*二级分区

创建KEY-HASH二级分区

模板化用法

CREATE TABLE tb_k_h_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY KEY(bid, name) 
PARTITIONS 2
SUBPARTITION BY HASH(id) 
SUBPARTITIONS 4

非模板化用法

CREATE TABLE tb_k_h_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY KEY(bid, name) 
PARTITIONS 2
SUBPARTITION BY HASH(id) 
(
  PARTITION p1 SUBPARTITIONS 2,
  PARTITION p2 SUBPARTITIONS 4
)

创建KEY-KEY二级分区

模板化用法

CREATE TABLE tb_k_k_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY KEY(bid,name) 
PARTITIONS 2
SUBPARTITION BY KEY(id) 
SUBPARTITIONS 4

非模板化用法

CREATE TABLE tb_k_k_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY KEY(bid,name) 
PARTITIONS 2
SUBPARTITION BY KEY(id) 
(
  PARTITION p1 SUBPARTITIONS 2,
  PARTITION p2 SUBPARTITIONS 4
)

创建KEY-RANGE二级分区

模板化用法

CREATE TABLE tb_k_r_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY KEY(bid,name) 
PARTITIONS 2
SUBPARTITION BY RANGE(id) (
  SUBPARTITION sp1 VALUES LESS THAN(1000),
  SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE)
)

非模板化用法

CREATE TABLE tb_k_r_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY KEY(bid,name) 
PARTITIONS 2
SUBPARTITION BY RANGE(id)
(
  PARTITION p1 (
  	SUBPARTITION sp1 VALUES LESS THAN(1000),
  	SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE)
	),
  PARTITION p2 (
  	SUBPARTITION sp3 VALUES LESS THAN(1000),
  	SUBPARTITION sp4 VALUES LESS THAN(2000),
  	SUBPARTITION sp5 VALUES LESS THAN(MAXVALUE)
  )
)

创建KEY-RANGE COLUMNS二级分区

模板化用法

CREATE TABLE tb_k_rc_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY KEY(bid,name) 
PARTITIONS 2
SUBPARTITION BY RANGE COLUMNS(birthday,id) (
  SUBPARTITION sp1 VALUES LESS THAN('2000-01-01',1000),
  SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
)

非模板化用法

CREATE TABLE tb_k_rc_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY KEY(bid,name) 
PARTITIONS 2
SUBPARTITION BY RANGE COLUMNS(birthday,id)
(
  PARTITION p1 (
  	SUBPARTITION sp1 VALUES LESS THAN('2000-01-01',1000),
  	SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
	),
  PARTITION p2 (
  	SUBPARTITION sp3 VALUES LESS THAN('2000-01-01',1000),
    SUBPARTITION sp4 VALUES LESS THAN('2020-01-01',2000),
  	SUBPARTITION sp5 VALUES LESS THAN(MAXVALUE,MAXVALUE)
	)
)

创建KEY-LIST二级分区

模板化用法

非模板化用法

CREATE TABLE tb_k_l_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY KEY(bid,name) 
PARTITIONS 2
SUBPARTITION BY LIST(id)
(
  PARTITION p1 (
  	SUBPARTITION sp1 VALUES IN (1000,2000),
  	SUBPARTITION sp2 VALUES IN (3000,4000),
  	SUBPARTITION sp3 VALUES In (DEFAULT)
	),
  PARTITION p2 (
  	SUBPARTITION sp4 VALUES IN (5000,6000),
  	SUBPARTITION sp5 VALUES In (DEFAULT)
	)
)

创建KEY-LIST COLUMNS二级分区

模板化用法

CREATE TABLE tb_k_lc_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY KEY(bid,name) 
PARTITIONS 2
SUBPARTITION BY LIST COLUMNS(birthday,id)
(
  	SUBPARTITION sp1 VALUES IN (('2000-01-01',1000),('2000-02-01',2000)),
  	SUBPARTITION sp2 VALUES IN (('2001-01-01',3000),('2001-02-01',4000)),
  	SUBPARTITION sp3 VALUES In (DEFAULT)
)

非模板化用法

CREATE TABLE tb_k_lc_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY KEY(bid,name) 
PARTITIONS 2
SUBPARTITION BY LIST COLUMNS(birthday,id)
(
  PARTITION p1 (
  	SUBPARTITION sp1 VALUES IN (('2000-01-01',1000),('2000-02-01',2000)),
  	SUBPARTITION sp2 VALUES IN (('2001-01-01',3000),('2001-02-01',4000)),
  	SUBPARTITION sp3 VALUES In (DEFAULT)
	),
  PARTITION p2 (
  	SUBPARTITION sp4 VALUES IN (('2002-01-10',5000),('2002-02-10',6000)),
  	SUBPARTITION sp5 VALUES In (DEFAULT)
	)
)