When to use list partitioning - PolarDB - Alibaba Cloud Documentation Center

List partitioning is based on the enumerated values of the data.

In the following example, all customers in Beijing, Tianjin, Inner Mongolia, and Hebei are stored in one partition, and customers in other provinces and municipalities are stored in other partitions. An administrator that analyzes accounts by region can use partition pruning.

Region	Code
North China (p_cn_north)	1:BJ,2:TJ,3:HB,4:NM
South China (p_cn_south)	5:GD, 6:GX, 7:HN
East China (p_cn_east)	8:SH, 9:ZJ, 10:JS

Create a list partitioned table:

CREATE TABLE `accounts` (
  `id` int(11) DEFAULT NULL,
  `account_number` int(11) DEFAULT NULL,
  `customer_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `region_id` int(11) DEFAULT NULL,
  `region` varchar(5) DEFAULT NULL,
  `status` varchar(1) DEFAULT NULL
) ENGINE=InnoDB
PARTITION BY LIST (`region_id`)
(PARTITION p_cn_north VALUES IN (1,2,3,4),
 PARTITION p_cn_south VALUES IN (5,6,7),
 PARTITION p_cn_east VALUES IN (8,9,10)
 );

List columns partitioning is an extension of list partitioning. It allows multiple columns to be used as partition keys and non-integer data columns to be used as partitioned columns. You can use STRING, DATE, and DATETIME columns. Like in range columns, you do not need to use expressions in the columns() clause to convert column values to integers. In fact, only expressions of column names are allowed in columns().

Create a list columns partitioned table:

DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts
( id             INT,
account_number INT,
customer_id    INT,
branch_id      INT,
region_id      INT,
region         VARCHAR(5),
status         VARCHAR(1)
)
PARTITION BY LIST COLUMNS(region)
(PARTITION p_cn_north VALUES IN ('BJ','TJ','HB','NMG'),
 PARTITION p_cn_south VALUES IN ('GD','GX','HN'),
 PARTITION p_cn_east VALUES IN ('SH','ZJ','JS')
 );