PolarDB:LIST partitioning

Syntax

In LIST 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(partition_expr) [PARTITIONS number]
(
  PARTITION part_name VALUES IN (list_bound_value_set),
  PARTITION part_name VALUES IN (list_bound_value_set),
  ...
)

partition_expr:
    partition_column	
  | partition_func(partition_column)

list_bound_value_set:
  list_bound_value[, list_bound_value, list_bound_value, ...]

# Define partitioning functions.
partition_func:
     
  | TO_DAYS
  | TO_MONTHS
  | TO_WEEKS
  | TO_SECOND
  | UNIX_TIMESTAMP
  | MONTH
  | DAYOFWEEK
  | DAYOFMONTH
  | DAYOFYEAR
  | SUBSTR
  | SUBSTRING
  | RIGHT
  | LEFT

Usage notes

• LIST partitioning allows you to use partitioning functions for single-column partition keys. However, the supported data types of partition key columns vary based on the partitioning function.

• LIST partitioning does not allow you to use partitioning functions for vector partition keys.

• You cannot use nested partitioning functions for partition key columns.

• 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 a partitioning function

Specify the birthday column of the DATETIME type as the single-column partition key, use the TO_DAYS partitioning function to convert the values of the birthday column to the number of days, and then perform LIST partitioning.

CREATE TABLE tb_l_fn(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST(TO_DAYS(birthday))
(
   PARTITION p1 VALUES IN (TO_DAYS('2020-01-01'),TO_DAYS('2020-02-01')),
   PARTITION p2 VALUES IN (TO_DAYS('2021-01-01'),TO_DAYS('2021-02-01')),
   PARTITION p3 VALUES IN (TO_DAYS('2022-01-01')),
   PARTITION pm VALUES IN (DEFAULT)
)

For more information about how to use other partitioning functions, see Partitioning functions.

Perform partitioning without using a partitioning function

Specify the id column as the single-column partition key for LIST partitioning and set the number of partitions to 8.

CREATE TABLE tb_l(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST(id)
(
   PARTITION p1 VALUES IN (1000,1001,1002),
   PARTITION p2 VALUES IN (2000,2001,2002),
   PARTITION p3 VALUES IN (3000),
   PARTITION pm VALUES IN (DEFAULT)
)

Use a vector partition key

If a vector partition key is used for LIST partitioning, LIST partitioning is automatically converted to LIST COLUMNS partitioning. The following sample code provides an example of LIST partitioning based on the birthday and id columns:

CREATE TABLE tb_l(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST(birthday,id)
(
   PARTITION p1 VALUES IN(('1990-04-03',1000),('1991-04-03',2000)),
   PARTITION p2 VALUES IN(('2000-01-03',3000),('2001-04-03',3001)),
   PARTITION pm VALUES IN(DEFAULT)
)

The preceding LIST partitioning is automatically converted to LIST COLUMNS partitioning. The following sample code provides an example of LIST COLUMNS partitioning:

CREATE TABLE tb_l(
 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(('1990-04-03',1000),('1991-04-03',2000)),
   PARTITION p2 VALUES IN(('2000-01-03',3000),('2001-04-03',3001)),
   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