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

创建RANGE COLUMNS-HASH二级分区

模板化用法

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

非模板化用法

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

创建RANGE COLUMNS-KEY二级分区

模板化用法

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

非模板化用法

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

创建RANGE COLUMNS-RANGE二级分区

模板化用法

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

非模板化用法

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

创建RANGE COLUMNS-RANGE COLUMNS二级分区

模板化用法

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

非模板化用法

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

创建RANGE COLUMNS-LIST二级分区

模板化用法

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

非模板化用法

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

创建RANGE COLUMNS-LIST COLUMNS二级分区

模板化用法

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

非模板化用法

CREATE TABLE tb_rc_lc_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE COLUMNS(birthday,name)
SUBPARTITION BY LIST COLUMNS(bid,id) 
(
  PARTITION p1 VALUES LESS THAN('2000-01-01','Jack') (
  	SUBPARTITION sp1 VALUES IN ((100,1000),(200,2000)),
  	SUBPARTITION sp2 VALUES IN ((300,3000),(400,2000)),
  	SUBPARTITION sp3 VALUES IN (DEFAULT)
	),
  PARTITION p2 VALUES LESS THAN(MAXVALUE,MAXVALUE) (
  	SUBPARTITION sp4 VALUES IN ((400,4000),(500,5000)),
  	SUBPARTITION sp5 VALUES IN (DEFAULT)
	)
)