LIST partitioning assigns rows to partitions based on whether a column value matches a predefined set of discrete values. Use it when data falls into well-defined categories — such as sales regions, product types, or status codes — and you want to isolate each category in its own partition for efficient queries and partition-level operations like bulk deletion.
PolarDB for MySQL supports two variants:
LIST — partitions by a single INT expression.
LIST COLUMNS — partitions by one or more columns of type INT, string, DATE, or DATETIME.
Syntax
CREATE TABLE ... PARTITION BY LIST {(expr) | COLUMNS(column_list)}
(
partition_definition [, partition_definition] ...
);Each partition_definition follows this form:
PARTITION partition_name
VALUES IN (value_list)Parameters
| Parameter | Description |
|---|---|
expr | The partition expression. Must return an INT value. String expressions are not supported. |
column_list | The list of partition key columns used with LIST COLUMNS. Expressions are not supported; only column references are allowed. |
value_list | The enumerated values that map rows to this partition. Values must be unique across all partitions in the table. |
partition_name | The name of the partition. Must be unique within the table. |
Constraints
LIST:
The expression must return an INT value. String expressions are not supported.
Supports only a single-column partition key.
LIST COLUMNS:
Does not support expressions; accepts column references only.
Supports multi-column partition keys.
Supported column types: INT, string types, DATE, and DATETIME.
Examples
Partition by an INT column (LIST)
The following example partitions a sales table by amount. Rows with amount values 1 or 2 go to partition p0, values 3 or 4 to p1, and values 5 or 6 to p2.
CREATE TABLE sales_list
(
dept_no INT,
part_no INT,
country VARCHAR(20),
date DATE,
amount INT
)
PARTITION BY LIST (amount)
(
PARTITION p0 VALUES IN (1, 2),
PARTITION p1 VALUES IN (3, 4),
PARTITION p2 VALUES IN (5, 6)
);Partition by a string column (LIST COLUMNS)
LIST COLUMNS supports string columns, so you can partition directly on a text value without a numeric mapping. The following example groups countries into three regional partitions.
CREATE TABLE sales_list_columns
(
dept_no INT,
part_no INT,
country VARCHAR(20),
date DATE,
amount INT
)
PARTITION BY LIST COLUMNS(country)
(
PARTITION europe VALUES IN ('FRANCE', 'ITALY'),
PARTITION asia VALUES IN ('INDIA', 'PAKISTAN'),
PARTITION americas VALUES IN ('US', 'CANADA')
);