创建list分区

语法:
PARTITION BY LIST(expr)
(partition_definition [, partition_definition] ...)
[SUBPARTITION BY]
{ [LINEAR] HASH(expr) | [LINEAR] KEY(column_list) }
[SUBPARTITIONS num]
partition_definition:
PARTITION partition_name
VALUES IN (value_list)
[(subpartition_definition [, subpartition_definition] ...)]
subpartition_definition:
SUBPARTITION logical_name

注意:
expr是某列值或一个基于某个列值、并返回一个整数值的表达式
value_list是一个通过逗号分隔的整数列表

创建 LIST 分区表

create table t_list (a int, b varchar(10))
partition by list(a) (
partition p0 values in (1,3,5,7,9),
partition p1 values in (2,4,6,8,10)
);
创建 LIST 分区表，带 hash 子分区，每个LIST分区四个子分区，不指定子分区名

create table t_list (a int, b int)
partition by list(a)
subpartition by hash(b)
subpartitions 4
(
partition p0 values in (1,3,5,7,9),
partition p1 values in (2,4,6,8,10)
)
创建 LIST 分区表，带 hash 子分区，每个LIST分区四个子分区，指定子分区名

create table t1 (a int, b int)
partition by list(a)
subpartition by hash (b)
(
partition p0 values in (1,3,5,7,9)
(
subpartition part0_q1,
subpartition part0_q2,
subpartition part0_q3,
subpartition part0_q4
),
partition p1 values in (2,4,6,8,10)
(
subpartition part1_q1,
subpartition part1_q2,
subpartition part1_q3,
subpartition part1_q4
)
);
创建 LIST 分区表，带 key 子分区，每个LIST分区四个子分区，不指定子分区名

create table t_list (a int, b int)
partition by list(a)
subpartition by key (b)
subpartitions 4
(
partition p0 values in (1,3,5,7,9),
partition p1 values in (2,4,6,8,10)
);
创建 LIST 分区表，带 key 子分区，每个LIST分区四个子分区，指定子分区名

create table t_list (a int, b int)
partition by list(a)
subpartition by key (b)
(
partition p0 values in (1,3,5,7,9),
(
subpartition part0_q1,
subpartition part0_q2,
subpartition part0_q3,
subpartition part0_q4
),
partition p1 values in (2,4,6,8,10)
(
subpartition part1_q1,
subpartition part1_q2,
subpartition part1_q3,
subpartition part1_q4
)
);