Use ALTER TABLE ... SET INTERVAL to change the interval type or value of an existing interval range partition. This is useful when your partitioning granularity needs to change after the table is created — for example, switching from monthly to yearly partitions as a table matures.
Prerequisites
Before you begin, ensure that you have:
A PolarDB for MySQL cluster running version 8.0.2, revision version 8.0.2.2.1 or later
To check your cluster version, see Query the engine version.
Syntax
ALTER TABLE table_name SET { INTERVAL(type, expr) | INTERVAL(expr) };Parameters
| Parameter | Description |
|---|---|
table_name | The name of the table. |
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 the type is SECOND, the value must be at least 60. |
Example
The following example creates an orders table partitioned by month, then changes the interval to yearly.
-- Create a table with monthly interval partitioning
CREATE TABLE orders(
orderkey BIGINT NOT NULL,
custkey BIGINT NOT NULL,
orderdate DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(orderdate) INTERVAL(MONTH, 1) (
PARTITION p0 VALUES LESS THAN('2021-10-01')
);
-- Change the interval to yearly
ALTER TABLE orders SET INTERVAL(YEAR, 1);