CO_HASH-* 组合分区

创建CO_HASH-HASH二级分区

模板化用法

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

非模板化用法

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

创建CO_HASH-KEY二级分区

模板化用法

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

非模板化用法

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

创建CO_HASH-RANGE二级分区

模板化用法

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

非模板化用法

CREATE TABLE tb_ch_r_ntp(
 id bigint not null auto_increment, 
 bid int, 
 uid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY CO_HASH(RIGHT(bid, 4),RIGHT(uid, 4)) 
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)
  )
)

创建CO_HASH-RANGE COLUMNS二级分区

模板化用法

CREATE TABLE tb_ch_rc_tp(
 id bigint not null auto_increment, 
 bid int, 
 uid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY CO_HASH(RIGHT(bid, 4),RIGHT(uid, 4)) 
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_ch_rc_ntp(
 id bigint not null auto_increment, 
 bid int, 
 uid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY CO_HASH(RIGHT(bid, 4),RIGHT(uid, 4)) 
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)
	)
)

创建CO_HASH-LIST二级分区

模板化用法

CREATE TABLE tb_ch_l_tp(
 id bigint not null auto_increment, 
 bid int, 
 uid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY CO_HASH(RIGHT(bid, 4),RIGHT(uid, 4)) 
PARTITIONS 2
SUBPARTITION BY LIST(id) (
  SUBPARTITION sp1 VALUES IN (1000,2000),
  SUBPARTITION sp2 VALUES IN (3000,4000),
  SUBPARTITION sp3 VALUES In (DEFAULT)
)

非模板化用法

CREATE TABLE tb_ch_l_ntp(
 id bigint not null auto_increment, 
 bid int, 
 uid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY CO_HASH(RIGHT(bid, 4),RIGHT(uid, 4)) 
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)
	)
)

创建CO_HASH-LIST COLUMNS二级分区

模板化用法

CREATE TABLE tb_ch_lc_tp(
 id bigint not null auto_increment, 
 bid int, 
 uid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY CO_HASH(RIGHT(bid, 4),RIGHT(uid, 4)) 
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_ch_lc_ntp(
 id bigint not null auto_increment, 
 bid int, 
 uid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY CO_HASH(RIGHT(bid, 4),RIGHT(uid, 4)) 
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)
	)
)

创建CO_HASH-CO_HASH二级分区

模板化用法

CREATE TABLE tb_ch_ch_tp(
 id bigint not null auto_increment, 
 bid int, 
 uid int,
 name varchar(30),
 nick varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY CO_HASH(RIGHT(bid, 4),RIGHT(uid, 4)) 
PARTITIONS 2
SUBPARTITION BY CO_HASH(RIGHT(name, 6),RIGHT(nick, 6)) 
SUBPARTITIONS 4

非模板化用法

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