You can execute the ALTER TABLE statement to modify partition attributes and therefore switch between an interval range partitioned table and a common table or a range partitioned table.

Prerequisites

The version of the cluster must be PolarDB for MySQL 8.0 and the revision version of the cluster must be 8.0.2.2.0 or later. For information about how to view the version of your cluster, see Query the engine version.

Switching rules

Take note of the following switching rules:
  • A common table can be switched to an interval range partitioned table. However, an interval range partitioned table cannot be switched to a common table. You can execute the ALTER TABLE table_name partition_options statement to switch a common table to an interval range partitioned table.
  • You can switch between an interval range partitioned table and a range partitioned table in the following ways: In PolarDB for MySQL 8.0.2.2.0 and later, execute the ALTER TABLE table_name partition_options statement. In PolarDB for MySQL 8.0.2.2.1 and later, execute the ALTER TABLE table_name SET { INTERVAL(type, expr) | INTERVAL(expr) } statement.

Syntax

  • Syntax 1
    ALTER TABLE table_name partition_options;
    partition_options is:
     PARTITION BY
    { RANGE{(expr) | COLUMNS(column_list)} }
    { INTERVAL(type, expr) | INTERVAL(expr) }
    [(partition_definition [, partition_definition] ...)]
    partition_definition is:
    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]
  • Syntax 2
    ALTER TABLE table_name SET { INTERVAL(type, expr) | INTERVAL(expr) };

Parameters

Parameter Description
table_name The name of the table.
RANGE(expr) The expression of the partition. It must be of the INT type. The string type is not supported.
column_list The list of partitions. It is used in LIST COLUMNS(). Expressions are not supported.
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.
MAXVALUE The maximum value of the partition.
engine_name The name of the storage engine.

Switch a common table to an interval range partitioned table

You can switch an existing table to an interval range partitioned table. Select an appropriate partition key and define a partition for the existing data in the table. After the switching is complete, newly inserted data can trigger automatic creation of the partition.

The following example switches the orders table to an interval range partitioned table. 
CREATE TABLE orders(
  orderkey BIGINT NOT NULL,
  custkey BIGINT NOT NULL,
  orderdate DATE NOT NULL
);

ALTER TABLE orders
PARTITION BY RANGE COLUMNS(orderdate) INTERVAL(MONTH, 1) (
  PARTITION p0 VALUES LESS THAN('2021-10-01')
);

Switch a range partitioned table to an interval range partitioned table

You can switch an existing range partitioned table to an interval range partitioned table. Define an interval range partition for the existing data in the table. After the switching is complete, newly inserted data can trigger automatic creation of the partition. You can use one of the following methods:
  • Method 1: Execute the ALTER TABLE table_name partition_options statement
    The following example switches the orders partitioned table to an interval range partitioned table. 
    CREATE TABLE orders(
  orderkey BIGINT NOT NULL,
  custkey BIGINT NOT NULL,
  orderdate DATE NOT NULL
)
PARTITION BY RANGE(orderkey) (
  PARTITION p0 VALUES LESS THAN(10000000)
);

ALTER TABLE orders
PARTITION BY RANGE COLUMNS(orderdate) INTERVAL(MONTH, 1) (
  PARTITION p0 VALUES LESS THAN('2021-10-01')
);
  • Method 2: Execute the ALTER TABLE table_name SET { INTERVAL(type, expr) | INTERVAL(expr) } statement
    The following example switches the orders partitioned table to an interval range partitioned table. 
    CREATE TABLE orders(
  orderkey BIGINT NOT NULL,
  custkey BIGINT NOT NULL,
  orderdate DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(orderdate) (
  PARTITION p0 VALUES LESS THAN('2021-10-01')
);

ALTER TABLE orders SET INTERVAL(MONTH, 1);

Switch an interval range partitioned table to a range partitioned table

You can switch an existing interval range partitioned table to a range partitioned table. You can use one of the following methods:
  • Method 1: Execute the ALTER TABLE table_name partition_options statement
    The following example switches the orders partitioned table to a range partitioned table. 
    CREATE TABLE orders(
  orderkey BIGINT NOT NULL,
  custkey BIGINT NOT NULL,
  orderdate DATE NOT NULL
)
PARTITION BY RANGE(orderkey) INTERVAL(100000) (
  PARTITION p0 VALUES LESS THAN(10000000)
);

ALTER TABLE orders
PARTITION BY RANGE COLUMNS(orderdate) (
  PARTITION p0 VALUES LESS THAN('2021-10-01')
);
  • Method 2: Execute the ALTER TABLE table_name SET { INTERVAL(type, expr) | INTERVAL(expr) } statement
    The following example switches the orders partitioned table to a range partitioned table. 
    CREATE TABLE orders(
  orderkey BIGINT NOT NULL,
  custkey BIGINT NOT NULL,
  orderdate DATE NOT NULL
)
PARTITION BY RANGE(orderkey) INTERVAL(100000) (
  PARTITION p0 VALUES LESS THAN(10000000)
);

ALTER TABLE orders SET INTERVAL(); /* Switch an interval range partitioned table to a range partitioned table without specifying the interval type and value. */