Syntax
In LIST COLUMNS partitioning, you must list the partition key values of each partition. Each of the listed partition key values must be unique. You can use DEFAULT to define a catch-all partition for rows that are not covered by other partitions.
CREATE TABLE ...
PARTITION BY LIST COLUMNS(partition_column_list) [PARTITIONS number]
(
PARTITION part_name VALUES IN (list_bound_value_se),
PARTITION part_name VALUES IN (list_bound_value_set),
...
)
partition_column_list:
partition_column[, partition_column, partition_column, ...]
list_bound_value_set:
list_bound_value[, list_bound_value, list_bound_value, ...]For more information about the differences between LIST partitioning and LIST COLUMNS partitioning, see the Comparison between LIST partitioning and LIST COLUMNS partitioning table of the "Overview" topic.
Usage notes
LIST COLUMNS partitioning does not support partitioning functions.
By default, a partitioned table can contain up to 8,192 partitions.
By default, a partition key can consist of up to five partition key columns.
The name of each partition must be unique and can be up to 16 characters in length by default.
If you use a column of a time zone-sensitive data type such as TIMESTAMP as a partition key column, you must use the UNIX_TIMESTAMP partitioning function for the partition key column.
Examples
Use the vector partition key that consists of the birthday and id columns to perform LIST COLUMNS partitioning.
CREATE TABLE tb_lc(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY LIST COLUMNS(birthday, id)
(
PARTITION p1 VALUES IN (('2020-01-01', 1000),('2020-01-01', 2000)),
PARTITION p2 VALUES IN (('2021-01-01', 1000),('2021-01-01', 2000)),
PARTITION p3 VALUES IN (('2022-01-01', 1000),('2022-01-01', 2000)),
PARTITION pm VALUES IN (DEFAULT)
)Limits on data types
Integer types: BIGINT, BIGINT UNSINGEDINT, INT, INT UNSINGED, MEDIUMINT, MEDIUMINT UNSINGED, SMALLINT, SMALLINT UNSINGED, TINYINT, and TINYINT UNSINGED
Date and time types: DATETIME, DATE, and TIMESTAMP
String types: CHAR and VARCHR
Fixed-point type: DECIMAL, for which the number of digits in the fractional part must be 0