RANGE-* 组合分区_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

创建RANGE-HASH二级分区

模板化用法

CREATE TABLE tb_r_h_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE(TO_DAYS(birthday)) 
SUBPARTITION BY HASH(bid) 
SUBPARTITIONS 4
(
  PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')),
  PARTITION p2 VALUES LESS THAN(MAXVALUE)
)

非模板化用法

CREATE TABLE tb_r_h_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE(TO_DAYS(birthday)) 
SUBPARTITION BY HASH(bid) 
(
  PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')) SUBPARTITIONS 2,
  PARTITION p2 VALUES LESS THAN(MAXVALUE) SUBPARTITIONS 3
)

创建RANGE-KEY二级分区

模板化用法

CREATE TABLE tb_r_k_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE(TO_DAYS(birthday)) 
SUBPARTITION BY KEY(bid,name) 
SUBPARTITIONS 4
(
  PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')),
  PARTITION p2 VALUES LESS THAN(MAXVALUE)
)

非模板化用法

CREATE TABLE tb_r_k_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE(TO_DAYS(birthday)) 
SUBPARTITION BY KEY(bid,name) 
(
  PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')) SUBPARTITIONS 2,
  PARTITION p2 VALUES LESS THAN(MAXVALUE) SUBPARTITIONS 3
)

创建RANGE-RANGE二级分区

模板化用法

CREATE TABLE tb_r_r_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE(TO_DAYS(birthday)) 
SUBPARTITION BY RANGE(bid) 
(
  SUBPARTITION sp1 VALUES LESS THAN(100),
  SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE)
)
(
  PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')),
  PARTITION p2 VALUES LESS THAN(MAXVALUE)
)

非模板化用法

CREATE TABLE tb_r_r_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE(TO_DAYS(birthday)) 
SUBPARTITION BY RANGE(bid) 
(
  PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')) (
  	SUBPARTITION sp1 VALUES LESS THAN(100),
  	SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE)
	),
  PARTITION p2 VALUES LESS THAN(MAXVALUE) (
    SUBPARTITION sp3 VALUES LESS THAN(100),
  	SUBPARTITION sp4 VALUES LESS THAN(200),
  	SUBPARTITION sp5 VALUES LESS THAN(MAXVALUE)
	)
)

创建RANGE-RANGE COLUMNS二级分区

模板化用法

CREATE TABLE tb_r_rc_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE(TO_DAYS(birthday)) 
SUBPARTITION BY RANGE COLUMNS(bid, name) 
(
  SUBPARTITION sp1 VALUES LESS THAN(100, 'Jack'),
  SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE, MAXVALUE)
)
(
  PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')),
  PARTITION p2 VALUES LESS THAN(MAXVALUE)
)

非模板化用法

CREATE TABLE tb_r_rc_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE(TO_DAYS(birthday)) 
SUBPARTITION BY RANGE COLUMNS(bid, name) 
(
  PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')) (
  	SUBPARTITION sp1 VALUES LESS THAN(100, 'Jack'),
  	SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE, MAXVALUE)
	),
  PARTITION p2 VALUES LESS THAN(MAXVALUE) (
  	SUBPARTITION sp3 VALUES LESS THAN(100, 'Tom'),
    SUBPARTITION sp4 VALUES LESS THAN(200, 'Bob'),
  	SUBPARTITION sp5 VALUES LESS THAN(MAXVALUE, MAXVALUE)
	)
)

创建RANGE-LIST二级分区

模板化用法

CREATE TABLE tb_r_l_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE(TO_DAYS(birthday)) 
SUBPARTITION BY LIST(name) 
(
  SUBPARTITION sp1 VALUES IN ('Jack'),
  SUBPARTITION sp2 VALUES IN (DEFAULT)
)
(
  PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')),
  PARTITION p2 VALUES LESS THAN(MAXVALUE)
)

非模板化用法

CREATE TABLE tb_r_l_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE(TO_DAYS(birthday)) 
SUBPARTITION BY LIST(name) 
(
  PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')) (
  	SUBPARTITION sp1 VALUES IN ('Jack'),
  	SUBPARTITION sp2 VALUES IN (DEFAULT)
	),
  PARTITION p2 VALUES LESS THAN(MAXVALUE) (
  	SUBPARTITION sp3 VALUES IN ('Tom'),
    SUBPARTITION sp4 VALUES IN ('Bob'),
  	SUBPARTITION sp5 VALUES IN (DEFAULT)
	)
)

创建RANGE-LIST COLUMNS二级分区

模板化用法

CREATE TABLE tb_r_lc_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE(TO_DAYS(birthday)) 
SUBPARTITION BY LIST COLUMNS(name,bid) 
(
  SUBPARTITION sp1 VALUES IN (('Jack', 100), ('Bob',200)),
  SUBPARTITION sp2 VALUES IN (DEFAULT)
)
(
  PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')),
  PARTITION p2 VALUES LESS THAN(MAXVALUE)
)

非模板化用法

CREATE TABLE tb_r_lc_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE(TO_DAYS(birthday)) 
SUBPARTITION BY LIST COLUMNS(name,bid) 
(
  PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')) (
  	SUBPARTITION sp1 VALUES IN (('Jack', 100), ('Bob',200)),
  	SUBPARTITION sp2 VALUES IN (DEFAULT)
	),
  PARTITION p2 VALUES LESS THAN(MAXVALUE) (
  	SUBPARTITION sp3 VALUES IN (('Jack', 100), ('Bob',200)),
    SUBPARTITION sp4 VALUES IN (('Lily', 200), ('Marry',400)),
  	SUBPARTITION sp5 VALUES IN (DEFAULT)
	)
)