LIST COLUMNS partitioning is a variant of LIST partitioning that supports multiple partition key columns and does not require a partition function to convert column values to integers. You can use string, date, and datetime columns directly as partition keys. For a comparison of the two methods, see the Comparison between LIST partitioning and LIST COLUMNS partitioning table in the overview.
Syntax
Each partition lists the exact column values that belong to it. Values must be unique across all partitions. Use the optional DEFAULT partition as a catch-all for rows that do not match any other partition.
CREATE TABLE ...
PARTITION BY LIST COLUMNS(partition_column_list) [PARTITIONS number]
(
PARTITION part_name VALUES IN (list_bound_value_set),
PARTITION part_name VALUES IN (list_bound_value_set),
...
[ PARTITION part_name VALUES IN (DEFAULT) ]
)
partition_column_list:
partition_column[, partition_column, partition_column, ...]
list_bound_value_set:
list_bound_value[, list_bound_value, list_bound_value, ...]Limitations
LIST COLUMNS partitioning does not support partition functions.
A partitioned table can contain up to 8,192 partitions by default.
A partition key can consist of up to five columns by default.
Each partition name must be unique and can be up to 16 characters long by default.
To use a TIMESTAMP column as a partition key column, apply the
UNIX_TIMESTAMPfunction to that column.
Supported data types
LIST COLUMNS partitioning supports the following data types for partition key columns:
Integer types: BIGINT, BIGINT UNSIGNED, INT, INT UNSIGNED, MEDIUMINT, MEDIUMINT UNSIGNED, SMALLINT, SMALLINT UNSIGNED, TINYINT, TINYINT UNSIGNED
Date and time types: DATETIME, DATE, TIMESTAMP
String types: CHAR, VARCHAR
Fixed-point type: DECIMAL (fractional digits must be 0)
Examples
Partition by string column
A common use case is routing rows to partitions based on a string column, such as a region or category. The following example creates a customer table partitioned by city name, grouping cities into sales regions:
CREATE TABLE customers (
id BIGINT NOT NULL AUTO_INCREMENT,
name VARCHAR(50),
city VARCHAR(30),
PRIMARY KEY (id)
)
PARTITION BY LIST COLUMNS(city)
(
PARTITION p_north VALUES IN ('Beijing', 'Tianjin', 'Shenyang'),
PARTITION p_east VALUES IN ('Shanghai', 'Nanjing', 'Hangzhou'),
PARTITION p_south VALUES IN ('Guangzhou', 'Shenzhen', 'Xiamen'),
PARTITION p_other VALUES IN (DEFAULT)
);p_other catches all rows where city does not match any of the listed values.
Partition by composite partition key
Use a composite partition key (multiple columns) when rows need to be routed based on a combination of values. The following example partitions orders by both birthday and id:
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)
);pm is a DEFAULT partition that catches rows not matched by p1, p2, or p3.
When the list of discrete values grows large (for example, hundreds of dates), RANGE COLUMNS partitioning is usually more practical to define and maintain.