创建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)
)
)文档内容是否对您有帮助？