You can execute the ALTER TABLE…SET INTERVAL statement to modify the type and value of the interval for an interval range partition.

Prerequisites

The version of the cluster must be PolarDB for MySQL 8.0.2 and the revision version of the cluster must be 8.0.2.2.1 or later. For information about how to view the version of your cluster, 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 type of the interval. Eight time types are supported: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, and SECOND. If you do not specify the type here, the numeric type is selected by default.
INTERVAL(expr) The value of the interval. If type is set to SECOND, the value cannot be less than 60.

Examples

Modify the interval type for the interval range partition in the orders table to YEAR. 

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

ALTER TABLE orders SET INTERVAL(YEAR, 1);