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 COLUMNS (partition_column_list) [PARTITIONS number]
(
PARTITION part_name VALUES LESS THAN (range_bound_value_list),
PARTITION part_name VALUES LESS THAN (range_bound_value_list),
...
)
partition_column_list:
partition_column[, partition_column, partition_column, ...]
range_bound_value_list:
range_bound_value[, range_bound_value, range_bound_value, ...]For more information about the differences between RANGE partitioning and RANGE COLUMNS partitioning, see the Comparison between RANGE partitioning and RANGE COLUMNS partitioning table of the "Overview" topic.
Usage notes
RANGE COLUMNS partitioning does not support partitioning functions.
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.
Examples
Create a table and partition the table based on the vector partition key that consists of the birthday and id columns by using the RANGE COLUMNS partitioning policy.
CREATE TABLE tb_rc(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY RANGE COLUMNS(birthday, id)
(
PARTITION p1 VALUES LESS THAN('2020-01-01', 1000),
PARTITION p2 VALUES LESS THAN('2021-01-01', 2000),
PARTITION p3 VALUES LESS THAN('2022-01-01', 3000),
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 and DATE
String types: CHAR and VARCHR
Fixed-point type: DECIMAL, for which the number of digits in the fractional part must be 0