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

创建LIST COLUMNS-HASH二级分区

模板化用法

CREATE TABLE tb_lc_h_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST COLUMNS(birthday,name) 
SUBPARTITION BY HASH(bid) 
SUBPARTITIONS 4
(
  PARTITION p1 VALUES IN (('2000-01-01','Jack'),('2001-01-01','Bob')),
  PARTITION p2 VALUES IN (DEFAULT)
)

非模板化用法

CREATE TABLE tb_lc_h_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST COLUMNS(birthday,name) 
SUBPARTITION BY HASH(bid) 
(
  PARTITION p1 VALUES IN (('2000-01-01','Jack'),('2001-01-01','Bob')) SUBPARTITIONS 3,
  PARTITION p2 VALUES IN (DEFAULT) SUBPARTITIONS 2
)

创建LIST COLUMNS-KEY二级分区

模板化用法

CREATE TABLE tb_lc_k_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST COLUMNS(birthday,name) 
SUBPARTITION BY KEY(bid,id) 
SUBPARTITIONS 4
(
  PARTITION p1 VALUES IN (('2000-01-01','Jack'),('2001-01-01','Bob')),
  PARTITION p2 VALUES IN (DEFAULT)
)

非模板化用法

CREATE TABLE tb_lc_k_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST COLUMNS(birthday,name) 
SUBPARTITION BY KEY(bid,id) 
(
  PARTITION p1 VALUES IN (('2000-01-01','Jack'),('2001-01-01','Bob')) SUBPARTITIONS 3,
  PARTITION p2 VALUES IN (DEFAULT) SUBPARTITIONS 2
)

创建LIST COLUMNS-RANGE二级分区

模板化用法

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

非模板化用法

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

创建LIST COLUMNS-RANGE COLUMNS二级分区

模板化用法

CREATE TABLE tb_lc_rc_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST 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 IN (('2000-01-01','Jack'),('2001-01-01','Bob')),
  PARTITION p2 VALUES IN (DEFAULT)
)

非模板化用法

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

创建LIST COLUMNS-LIST二级分区

模板化用法

CREATE TABLE tb_lc_l_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST COLUMNS(birthday,name) 
SUBPARTITION BY LIST(id) 
(
  SUBPARTITION sp1 VALUES IN (1000,2000,3000),
  SUBPARTITION sp2 VALUES IN (DEFAULT)
)
(
  PARTITION p1 VALUES IN (('2000-01-01','Jack'),('2001-01-01','Bob')),
  PARTITION p2 VALUES IN (DEFAULT)
)

非模板化用法

CREATE TABLE tb_lc_l_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST COLUMNS(birthday,name) 
SUBPARTITION BY LIST(id) 
(
  PARTITION p1 VALUES IN (('2000-01-01','Jack'),('2001-01-01','Bob')) (
  	SUBPARTITION sp1 VALUES IN (1000,2000,3000),
  	SUBPARTITION sp2 VALUES IN (DEFAULT)
	),
  PARTITION p2 VALUES IN (DEFAULT) (
  	SUBPARTITION sp3 VALUES IN (1000,2000),
    SUBPARTITION sp4 VALUES IN (3000,4000,5000),
  	SUBPARTITION sp5 VALUES IN (DEFAULT)
	)
)

创建LIST COLUMNS-LIST COLUMNS二级分区

模板化用法

CREATE TABLE tb_lc_lc_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST COLUMNS(birthday,name) 
SUBPARTITION BY LIST COLUMNS(bid,id) 
(
  SUBPARTITION sp1 VALUES IN ((100,1000),(200,2000),(300,3000)),
  SUBPARTITION sp2 VALUES IN (DEFAULT)
)
(
  PARTITION p1 VALUES IN (('2000-01-01','Jack'),('2001-01-01','Bob')),
  PARTITION p2 VALUES IN (DEFAULT)
)

非模板化用法

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