Convert Non-Partitioned Tables to Range-Partitioned via ALTER TABLE - PolarDB

Partitioning an existing non-partitioned table in standard MySQL requires a full table rewrite and per-row validation — a process that can take minutes for large tables and blocks concurrent writes. PolarDB for MySQL eliminates this overhead with ALTER TABLE ... WITHOUT VALIDATION, which converts a non-partitioned table into a range-partitioned table by updating only the table's metadata. The conversion completes in under 0.1 seconds regardless of table size.

All existing rows are placed into the first partition without per-row validation. Other partitions are created empty. The typical use case is archiving: attach an existing non-partitioned table as the initial (oldest) partition of a new range-partitioned table, then use subsequent partitions for incoming data.

Important

All existing rows must fall within the value range of the first partition. Rows placed outside that declared range are physically stored in the first partition but may not be returned by partition-pruned queries, making them effectively unretrievable through normal access.

Note

For more information about how to quickly convert a non-partitioned table into a range-partitioned table, join DingTalk group 24490017825 to obtain technical support.

Prerequisites

Before you begin, ensure that you have:

A PolarDB for MySQL 8.0.2 cluster with revision version 8.0.2.2.10 or later

To check your version, see the "Query the engine version" section of Engine versions.

Limitations

Tables with columns added using INSTANT ADD COLUMN cannot be converted into a range-partitioned table.

Convert a non-partitioned table

Note

Requires PolarDB for MySQL 8.0.2, revision version 8.0.2.2.10 or later.

When you run a standard ALTER TABLE PARTITION BY, MySQL validates every row against the partition definition before moving data — which is slow on large tables. WITHOUT VALIDATION skips this check and moves all data directly into the first partition, making the conversion instant.

Syntax

ALTER TABLE table_name
PARTITION BY RANGE {(expr) | COLUMNS(column_list)}
(partition_definition [, partition_definition] ...)
WITHOUT VALIDATION;

partition_definition:

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

Parameters

Parameter	Description
`table_name`	The name of the table to convert
`column_list`	The list of partition key columns. Expressions are not supported.
`RANGE(expr)`	The expression used for range partitioning
`partition_name`	The name of the partition
`value_list`	The values of the partition
`MAXVALUE`	The maximum value in the partition

Example

The following example converts the non-partitioned table t1 into a range-partitioned table with two partitions.

CREATE TABLE t1 (
    `a` INT,
    `b` INT,
    PRIMARY KEY (a, b)
);

INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1),(111,111),(3333,333);

ALTER TABLE t1
PARTITION BY RANGE (a) (
    PARTITION p0 VALUES LESS THAN (100),
    PARTITION p1 VALUES LESS THAN (200)
) WITHOUT VALIDATION;

After the statement runs, all rows from t1 — including those with a = 111 and a = 3333 — are placed into p0 without validation. Rows whose values exceed p0's declared upper bound (a < 100) are physically stored in p0 but may not be retrieved by partition-pruned queries.

Important

Before running WITHOUT VALIDATION, define the first partition's upper bound to cover the maximum value in your data. If any row exceeds the boundary, it becomes unretrievable through normal queries.

Use with INTERVAL-partitioned tables

WITHOUT VALIDATION also works with INTERVAL-partitioned tables. This lets you load an existing non-partitioned table as the initial partition, while PolarDB for MySQL automatically creates subsequent RANGE partitions at the specified interval.

Example

CREATE TABLE t1 (
    ID   INT,
    DATE DATE,
    PRIMARY KEY (ID, DATE)
);

ALTER TABLE t1
PARTITION BY RANGE COLUMNS (DATE) INTERVAL (DAY, 1) (
    PARTITION p0 VALUES LESS THAN ('2023-01-31')
) WITHOUT VALIDATION;

All existing rows in t1 are placed into p0. New rows inserted after the conversion are automatically routed to new daily partitions created by the INTERVAL definition.

Performance

Because WITHOUT VALIDATION only updates table metadata, the conversion time is constant regardless of table size.

Table size	Standard MySQL `ALTER TABLE PARTITION BY`	PolarDB `WITHOUT VALIDATION`
1 GB (6,001,215 rows)	52.24 seconds	0.10 seconds
10 GB (59,986,052 rows)	8 minutes 45.82 seconds	0.07 seconds