RANGE COLUMNS partitioning extends standard RANGE partitioning with two key capabilities: support for multiple partition columns and non-integer data types including strings, DATE, and DATETIME. Use it when integer-only or single-column RANGE partitioning does not fit your data distribution needs.
For a full comparison, see the partition types overview.
Syntax
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, ...]
range_bound_value_list:
range_bound_value[, range_bound_value, ...]Partition boundaries must be strictly increasing. Use MAXVALUE as the upper bound of the last partition to capture all remaining rows.
Supported data types
| Type category | Supported types |
|---|---|
| Integer types | BIGINT, BIGINT UNSIGNED, INT, INT UNSIGNED, MEDIUMINT, MEDIUMINT UNSIGNED, SMALLINT, SMALLINT UNSIGNED, TINYINT, TINYINT UNSIGNED |
| Date and time types | DATETIME, DATE |
| String types | CHAR, VARCHAR |
| Fixed-point type | DECIMAL (fractional digits must be 0) |
Limitations
| Limit | Default value |
|---|---|
| Partitioning functions | Not supported — specify column names directly |
| Maximum partitions per table | 8,192 |
| Maximum columns per partition key | 5 |
| Maximum partition name length | 16 characters (must be unique) |
Examples
Partition by composite key (date and ID)
Partition records by a composite key of birthday and id to create year-based ranges with an ID-based sub-range within each year:
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)
);The final partition pm uses MAXVALUE for both columns, capturing all rows that do not fit the earlier boundaries.