Use the CREATE TABLE statement to create an interval range partition - PolarDB

Interval range partitioning automatically creates new partitions when incoming data exceeds all existing range partitions. You define at least one initial partition to establish the transition point — the upper boundary of your manually defined partitions — and the database creates partitions automatically for any values that exceed it.

This is useful for time-series tables (such as order logs or event streams) and large numeric-keyed tables where manually managing partition boundaries is error-prone. You can also combine interval range partitioning with partitioned table lifecycle management to migrate cold data to Object Storage Service (OSS) automatically.

Prerequisites

Before you begin, ensure that:

Your cluster runs PolarDB for MySQL 8.0.2
The revision version is 8.0.2.2.0 or later

To check your cluster version, see Query the engine version.

Syntax

CREATE TABLE [IF NOT EXISTS] [schema.]table_name
    table_definition
    partition_options;

partition_options:

PARTITION BY
    { RANGE{(expr) | COLUMNS(column_list)} }
    { INTERVAL(type, expr) | INTERVAL(expr) }
    [(partition_definition [, partition_definition] ...)]

partition_definition:

    PARTITION partition_name
        [VALUES LESS THAN {expr | MAXVALUE}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string']
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]

Parameters

Parameter	Description
`table_name`	The name of the table.
`RANGE(expr)`	The partition expression. Must be of the INT type. String types are not supported.
`column_list`	The column list used in `RANGE COLUMNS()`. Expressions are not supported.
`INTERVAL(type)`	The interval type. Supported time types: `YEAR`, `QUARTER`, `MONTH`, `WEEK`, `DAY`, `HOUR`, `MINUTE`, and `SECOND`. If omitted, the numeric type is used.
`INTERVAL(expr)`	The interval value. If `type` is `SECOND`, the value must be 60 or greater.
`MAXVALUE`	The maximum value of the partition.
`engine_name`	The storage engine name.

INTERVAL examples

Numeric interval — group every 1,000 adjacent integers into one partition:

INTERVAL(1000)

Time-based intervals:

Type	Example	Effect
`YEAR`	`INTERVAL(YEAR, 1)`	One year per partition
`QUARTER`	`INTERVAL(QUARTER, 1)`	One quarter per partition
`MONTH`	`INTERVAL(MONTH, 1)`	One month per partition
`WEEK`	`INTERVAL(WEEK, 1)`	One week per partition
`DAY`	`INTERVAL(DAY, 1)`	One day per partition
`HOUR`	`INTERVAL(HOUR, 1)`	One hour per partition
`MINUTE`	`INTERVAL(MINUTE, 1)`	One minute per partition
`SECOND`	`INTERVAL(SECOND, 60)`	60 seconds per partition (minimum for `SECOND`)

Limitations

The partition expression in RANGE(expr) must be of the INT type. String types are not supported.
Expressions are not supported in RANGE COLUMNS(). Use column names only.
If type is SECOND, expr must be 60 or greater.
A table can have a maximum of 8,192 partitions.
Partition names prefixed with _p are reserved for system-generated partitions:
- Cannot be used with ADD PARTITION or REORGANIZE PARTITION
- Can be used with DROP PARTITION

Create an interval range partitioned table

The following example creates a sales table partitioned by month, using order_time as the partition key. The initial partition covers data before September 2021. Any data inserted beyond that boundary triggers automatic partition creation.

Step 1: Create the table with an initial partition.

CREATE TABLE sales
(
  id BIGINT,
  uid BIGINT,
  order_time DATETIME
)
PARTITION BY RANGE COLUMNS(order_time) INTERVAL(MONTH, 1)
(
  PARTITION p0 VALUES LESS THAN('2021-9-1')
);

Step 2: Insert a row beyond the transition point.

INSERT INTO sales VALUES(1, 1010101010, '2021-11-11');

The inserted value (2021-11-11) falls outside the p0 boundary (2021-9-1), so the database automatically creates the necessary partitions.

Step 3: Verify that new partitions were created.

Run SHOW CREATE TABLE sales. The table now includes three system-generated partitions covering October, November, and December 2021:

CREATE TABLE `sales` (
  `id` bigint(20) DEFAULT NULL,
  `uid` bigint(20) DEFAULT NULL,
  `order_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE  COLUMNS(order_time) */ /*!99990 800020200 INTERVAL(MONTH, 1) */
/*!50500 (PARTITION p0 VALUES LESS THAN ('2021-9-1') ENGINE = InnoDB,
 PARTITION _p20211001000000 VALUES LESS THAN ('2021-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20211101000000 VALUES LESS THAN ('2021-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20211201000000 VALUES LESS THAN ('2021-12-01 00:00:00') ENGINE = InnoDB) */

The database created three partitions (_p20211001000000, _p20211101000000, _p20211201000000) to fill the gap between the transition point and the inserted value. Partition names prefixed with _p are system-reserved.

More examples

/* Numeric interval: one partition per 1,000 integers */
CREATE TABLE t(a INT, b DATETIME)
PARTITION BY RANGE(a) INTERVAL(1000) (
    PARTITION p0 VALUES LESS THAN(1000)
);

/* Numeric interval using a function expression: MONTH(b) returns INT, so type is omitted */
CREATE TABLE t(a INT, b DATETIME)
PARTITION BY RANGE(MONTH(b)) INTERVAL(1) (
    PARTITION p0 VALUES LESS THAN(2)
);

/* Numeric interval via RANGE COLUMNS: partition key is INT, not a time type */
CREATE TABLE t(a INT, b DATETIME)
PARTITION BY RANGE COLUMNS(a) INTERVAL(1000) (
    PARTITION p0 VALUES LESS THAN(1000)
);

/* Time interval: one partition per year */
CREATE TABLE t(a INT, b DATETIME)
PARTITION BY RANGE COLUMNS(b) INTERVAL(YEAR, 1) (
    PARTITION p0 VALUES LESS THAN('2021-11-01 00:00:00')
);

What's next

To manage partition growth, combine interval range partitioning with partitioned table lifecycle management to drop or migrate cold-data partitions to OSS automatically.
To remove a system-generated partition, use DROP PARTITION with the _p-prefixed partition name.