Create partitioned tables with RANGE partitioning - PolarDB

RANGE partitioning divides table rows into partitions based on whether a column value falls within a defined range. Each partition stores rows whose values are less than the specified boundary, making it well-suited for time-series data and ordered numeric sequences.

When to use RANGE partitioning

RANGE partitioning is particularly useful when:

Data has a time dimension: You store logs, events, or transactions by date and want to drop old data efficiently with ALTER TABLE ... DROP PARTITION instead of running large DELETE queries.
Queries filter by the partition key: MySQL-compatible engines can skip entire partitions when the WHERE clause matches the partition key, improving query performance on large tables.
Data grows monotonically: Order IDs, event sequences, or timestamps naturally map to incremental ranges, keeping partition boundaries stable.

For a comparison of RANGE partitioning and RANGE COLUMNS partitioning, see the Comparison between RANGE partitioning and RANGE COLUMNS partitioning table in the partition types overview.

Syntax

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)

-- Supported partitioning functions
partition_func:
    TO_DAYS
  | TO_MONTHS
  | TO_WEEKS
  | TO_SECOND
  | UNIX_TIMESTAMP
  | MONTH
  | DAYOFWEEK
  | DAYOFMONTH
  | DAYOFYEAR
  | SUBSTR
  | SUBSTRING
  | RIGHT
  | LEFT

Key parameters:

Parameter	Description
`partition_expr`	The expression that determines which partition a row belongs to. Use a bare column name or wrap it in a supported partitioning function.
`partition_func`	A built-in function that transforms the column value before comparison. Only supported for single-column partition keys.
`part_name`	A unique name for the partition, up to 16 characters.
`range_bound_value`	The exclusive upper bound for the partition. Boundaries must be strictly ascending. Use `MAXVALUE` as a catch-all upper bound for the last partition.

Limitations

Limitation	Details
Partitioning functions	Supported for single-column partition keys only. Not supported for multi-column (vector) partition keys.
Nested functions	Nested partitioning functions are not supported.
Partition name length	Must be unique and can be up to 16 characters.
TIMESTAMP columns	Must use the `UNIX_TIMESTAMP` partitioning function when the partition key column uses a time zone-sensitive type such as `TIMESTAMP`.
Maximum partitions	Up to 8,192 partitions per table.
Maximum partition key columns	Up to five columns.

Examples

Partition by date using a partitioning function

Use TO_DAYS to convert a DATETIME column to a day count, then partition by those day values. This is the standard approach for time-based RANGE partitioning with DATETIME or DATE columns.

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 TIMESTAMP columns, use UNIX_TIMESTAMP instead of TO_DAYS. For other supported functions, see Partitioning functions.

Partition by integer range without a partitioning function

When the partition key is an integer column, reference it directly without a partitioning function.

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)
);

Partition by multiple columns (auto-conversion to RANGE COLUMNS)

When you specify a multi-column (vector) partition key in a PARTITION BY RANGE(...) clause, PolarDB for Xscale automatically converts it to PARTITION BY RANGE COLUMNS(...).

Original RANGE definition with a vector partition key:

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)
);

Equivalent RANGE COLUMNS definition after auto-conversion:

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)
);

Supported data types

The following column data types are supported as RANGE partition keys:

Category	Supported types
Integer	BIGINT, BIGINT UNSIGNED, INT, INT UNSIGNED, MEDIUMINT, MEDIUMINT UNSIGNED, SMALLINT, SMALLINT UNSIGNED, TINYINT, TINYINT UNSIGNED
Date and time	DATETIME, DATE, TIMESTAMP
String	CHAR, VARCHAR

What's next

Partitioning functions — Full reference for all supported partitioning functions and their applicable column types
Partition types overview — Compare RANGE, RANGE COLUMNS, LIST, and HASH partitioning strategies