Create an interval range partition - PolarDB - Alibaba Cloud Documentation Center

This topic describes how to create 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.0 or later. For information about how to view the version of your cluster, see Query the engine version.

Syntax

CREATE TABLE [IF NOT EXISTS] [schema.]table_name
    table_definition
    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]

The INTERVAL clause supports only interval values (expr) and the INTERVAL type (type ).

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 RANGE 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.

Interval values (expr)
The following example adds every 1000 adjacent numbers to one partition:
```
INTERVAL(1000)
```
Time types
- YEAR
  The following example sets the interval type to YEAR and adds the data of one year to a partition:
```
INTERVAL(YEAR, 1)
```
- QUARTER
  The following example sets the interval type to QUARTER and adds the data of one quarter to a partition:
```
INTERVAL(QUARTER, 1)
```
- MONTH
  The following example sets the interval type to MONTH and adds the data of one month to a partition:
```
INTERVAL(MONTH, 1)
```
- WEEK
  The following example sets the interval type to WEEK and adds the data of one week to a partition:
```
INTERVAL(WEEK, 1)
```
- DAY
  The following example sets the interval type to DAY and adds the data of one day to a partition:
```
INTERVAL(DAY, 1)
```
- HOUR
  The following example sets the interval type to HOUR and adds the data of one hour to a partition:
```
INTERVAL(HOUR, 1)
```
- MINUTE
  The following example sets the interval type to MINUTE and adds the data of one minute to a partition:
```
INTERVAL(MINUTE, 1)
```
- SECOND
  The following example sets the interval type to SECOND and adds the data of every 60 seconds to a partition:
```
INTERVAL(SECOND, 60)
```

Examples

Use the order_time as the partition key to partition the sales table by interval. When you create an interval range partitioned table, you must specify an initial transition point. Then, new partitions can be automatically created beyond the transition point.

Create an interval range partitioned table in the database and populate the table with data:

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

Populate the interval range partitioned table with data:

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

Execute the SHOW CREATE TABLE statement to query the sales table definition after data is inserted. The following new table definition is displayed:

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) */

Three partitions _p20211001000000, _p20211101000000, and _p20211201000000 are added after interval range partitioning is used. Partition names prefixed with _p are reserved by the system. Such partition names cannot be used when you create or rename partitions. For example, partition names prefixed with '_p' cannot be used when you execute the ADD PARTITION and REORGANIZE PARTITION statements. However, such partition names can be used when you execute the DROP PARTITION statement.

In this scenario, you can manually add partitions before inserted values reach the transition point. However, data insertion may fail due to accidental operations. If you create an interval range partitioned table, partitions can be automatically created to ensure prompt data insertion.

The number of automatically created partitions is limited because a maximum of 8,192 partitions can be created. You can use interval range partitioning with the partitioned table lifecycle management solution to create partitions and migrate the partitions where cold data is located to OSS on a regular basis.

Other examples:

/* Set the interval type to numeric and the interval value to 1000  */
CREATE TABLE t(a int, b datetime)
PARTITION BY RANGE(a) INTERVAL(1000) (
    PARTITION p0 VALUES LESS THAN(1000)
);

/* Set only the interval value and skip the interval type because the partition expression can only be INT_RESULT. */
CREATE TABLE t(a int, b datetime)
PARTITION BY RANGE(MONTH(b)) INTERVAL(1) (
    PARTITION p0 VALUES LESS THAN(2)
);

/* Set the interval type to numeric and the interval value to 1000 because the partition key is not of the time type. */
CREATE TABLE t(a int, b datetime)
PARTITION BY RANGE COLUMNS(a) INTERVAL(1000) (
    PARTITION p0 VALUES LESS THAN(1000)
);

/* Set the interval to one 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')
);