PolarDB:RANGE partitioning

Syntax

Data is partitioned by range boundary. Time boundaries are commonly used. Partition boundaries must be incremental. You can specify MAXVALUE as the maximum range boundary.

CREATE TABLE ... 
PARTITION BY RANGE(partition_expr) [PARTITIONS number]
(
  PARTITION part_name VALUES LESS THAN (range_bound_value),
  PARTITION part_name VALUES LESS THAN (range_bound_value),
  ...
)

partition_expr:
    partition_column	
  | partition_func(partition_column)


# 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

• RANGE 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.

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

• You cannot use nested partitioning functions for 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.

• 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.

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 RANGE partitioning.

CREATE TABLE tb_r_fn(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE(TO_DAYS(birthday))
(
   PARTITION p1 VALUES LESS THAN(TO_DAYS('2020-01-01')),
   PARTITION p2 VALUES LESS THAN(TO_DAYS('2021-01-01')),
   PARTITION p3 VALUES LESS THAN(TO_DAYS('2022-01-01')),
   PARTITION pm VALUES LESS THAN(MAXVALUE)
)

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 RANGE partitioning and set the number of partitions to 8.

CREATE TABLE tb_r(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE(id)
(
   PARTITION p1 VALUES LESS THAN(1000),
   PARTITION p2 VALUES LESS THAN(2000),
   PARTITION p3 VALUES LESS THAN(3000),
   PARTITION pm VALUES LESS THAN(MAXVALUE)
)

Use a vector partition key

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

CREATE TABLE tb_r(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE(bid,id)
(
   PARTITION p1 VALUES LESS THAN(1,1000),
   PARTITION p2 VALUES LESS THAN(2,2000),
   PARTITION pm VALUES LESS THAN(MAXVALUE, MAXVALUE)
)

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

CREATE TABLE tb_r(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE COLUMNS(bid,id)
(
   PARTITION p1 VALUES LESS THAN(1,1000),
   PARTITION p2 VALUES LESS THAN(2,2000),
   PARTITION pm VALUES LESS THAN(MAXVALUE, MAXVALUE)
)

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