Partition PolarDB MySQL Tables by Range with RANGE COLUMNS - PolarDB

Range partitioning divides table data into partitions based on a column value falling within a defined range. Each partition holds rows where the partition key is less than a specified boundary value, and boundaries must be strictly incremental.

Range partitioning works well when you need to:

Query or delete data by time period — drop an entire partition instead of running a DELETE with a date filter, which is significantly faster on large tables.
Partition by a continuous numeric range — for example, sales amounts, IDs, or scores.
Improve query performance through partition pruning — queries that filter on the partition key can skip irrelevant partitions entirely.

PolarDB for MySQL supports two forms: PARTITION BY RANGE (expression-based, single-column) and PARTITION BY RANGE COLUMNS (column-based, supports multiple columns and more data types).

Syntax

CREATE TABLE ... PARTITION BY RANGE {(expr) | COLUMNS(column_list)}
(partition_definition [, partition_definition] ...);

Where partition_definition is:

PARTITION partition_name
    VALUES LESS THAN {(value | value_list) | MAXVALUE}

Parameters

Parameter	Description
`expr`	A partition expression. Must evaluate to an INT value. String types are not supported.
`column_list`	A list of partition key columns for use with `RANGE COLUMNS`. Expressions are not supported; only column names are allowed.
`value`	The upper boundary value for a partition.
`value_list`	A list of upper boundary values, one per column. Used with `RANGE COLUMNS` when multiple partition keys are specified.
`MAXVALUE`	The maximum value of the partition. Always add a `MAXVALUE` partition as the last partition to ensure all rows have a matching partition.
`partition_name`	The name of the partition. Must be unique within the table.

RANGE vs RANGE COLUMNS

Feature	`PARTITION BY RANGE`	`PARTITION BY RANGE COLUMNS`
Partition key count	Single column only	One or more columns
Expressions	Supported	Not supported; column names only
Supported data types	INT (expression must resolve to INT)	INT, string types, DATE, DATETIME

Use RANGE COLUMNS when you need to partition by a DATE or DATETIME column directly, or when you need a composite partition key across multiple columns.

Examples

Partition by a numeric range

The following example partitions a sales table by amount. Each partition holds rows where amount is less than the boundary value. The final partition uses MAXVALUE to capture all remaining rows.

CREATE TABLE sales_range
(
  dept_no     INT,
  part_no     INT,
  country     VARCHAR(20),
  date        DATE,
  amount      INT
)
PARTITION BY RANGE(amount)
(
  PARTITION p0 VALUES LESS THAN (1000),
  PARTITION p1 VALUES LESS THAN (2000),
  PARTITION p2 VALUES LESS THAN (3000),
  PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

Partition by a date column (RANGE COLUMNS)

RANGE COLUMNS lets you partition directly on a DATE column without wrapping it in an expression. The following example creates monthly partitions on create_date.

CREATE TABLE sales_range_columns
(
  dept_no       INT,
  part_no       INT,
  country       VARCHAR(20),
  create_date   DATE,
  amount        INT
)
PARTITION BY RANGE COLUMNS(create_date)
(
  PARTITION p1 VALUES LESS THAN ('2023-01-01'),
  PARTITION p2 VALUES LESS THAN ('2023-02-01'),
  PARTITION p3 VALUES LESS THAN ('2023-03-01'),
  PARTITION p4 VALUES LESS THAN ('2023-04-01')
);

Partition by multiple columns (RANGE COLUMNS)

RANGE COLUMNS supports composite partition keys.

CREATE TABLE sales_range_columns
(
  dept_no   INT,
  part_no   INT,
  country   VARCHAR(20),
  date      DATE,
  amount    INT
)
PARTITION BY RANGE COLUMNS(dept_no, part_no)
(
  PARTITION p1 VALUES LESS THAN (1000, MAXVALUE),
  PARTITION p2 VALUES LESS THAN (2000, MAXVALUE),
  PARTITION p3 VALUES LESS THAN (3000, MAXVALUE),
  PARTITION p4 VALUES LESS THAN (4000, MAXVALUE)
);