如何创建INTERVAL RANGE分区_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

本文介绍了创建INTERVAL RANGE分区的语法。

前提条件

集群版本需为PolarDB MySQL版8.0.2版本且Revision version为8.0.2.2.0及以上。您可以通过查询版本号确认集群版本。

语法

CREATE TABLE [IF NOT EXISTS] [schema.]table_name
    table_definition
    partition_options;

其中，partition_options为：

PARTITION BY
    { RANGE{(expr) | COLUMNS(column_list)} }
    { INTERVAL(type, expr) | INTERVAL(expr) }
    [(partition_definition [, partition_definition] ...)]

partition_definition为：

    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]

其中，INTERVAL子句仅支持设置间隔数值（expr）和间隔类型（type）。

参数

参数	参数说明
table_name	要创建的表名称。
RANGE(expr)	RANGE分区字段表达式，目前只支持INT类型，不支持字符类型。
column_list	RANGE COLUMNS的情况下使用，分区字段列表，不支持表达式。
INTERVAL(type)	目前支持8种时间类型（YEAR、QUARTER、MONTH、WEEK、DAY、HOUR、MINUTE、SECOND），不显式指定默认是数字类型的间隔。
INTERVAL(expr)	指定间隔的数值大小。当type为SECOND类型时，间隔不能小于60。
MAXVALUE	最大值。
engine_name	存储引擎名称。

间隔数值（expr）
相近的1000个数字进入同一个分区，示例如下：
```
INTERVAL(1000)
```
时间类型
- 年（YEAR）
  以年为单位设置自动分区的间隔，每一年的数据进入同一个分区，示例如下：
```
INTERVAL(YEAR, 1)
```
- 季度（QUARTER）
  以季度为单位设置自动分区的间隔，每一季度的数据进入同一个分区，示例如下：
```
INTERVAL(QUARTER, 1)
```
- 月（MONTH）
  以月为单位设置自动分区的间隔，每一月的数据进入同一个分区，示例如下：
```
INTERVAL(MONTH, 1)
```
- 周（WEEK）
  以周为单位设置自动分区的间隔，每一周的数据进入同一个分区，示例如下：
```
INTERVAL(WEEK, 1)
```
- 日（DAY）
  以日为单位设置自动分区的间隔，每一日的数据进入同一个分区，示例如下：
```
INTERVAL(DAY, 1)
```
- 时（HOUR）
  以小时为单位设置自动分区的间隔，每一小时的数据进入同一个分区，示例如下：
```
INTERVAL(HOUR, 1)
```
- 分（MINUTE）
  以分钟为单位设置自动分区的间隔，每一分钟的数据进入同一个分区，示例如下：
```
INTERVAL(MINUTE, 1)
```
- 秒（SECOND）
  以秒为单位设置自动分区的间隔，每60秒的数据进入同一个分区，示例如下：
```
INTERVAL(SECOND, 60)
```

示例

以下示例将order_time作为分区键，按间隔划分sales表。创建INTERVAL RANGE分区表需要有一个初始的转换点，然后才能在转换点之外自动创建新的分区。

在数据库中创建一个新的INTERVAL RANGE分区表，并向表中插入数据，示例如下：

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

向INTERVAL RANGE分区表中插入数据。示例如下：

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

插入数据后，SHOW CREATE TABLE查询sales表定义。新的表定义如下：

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

通过上述示例发现INTERVAL RANGE分区自动新增加了_p20211001000000、_p20211101000000、_p20211201000000三个分区，这里要注意‘_p’作为前缀的分区名将会保留为系统命名规则，手动管理分区（创建新分区或者重命名分区的操作）时，将不允许使用此类型的分区名。例如，ADD PARTITION和REORGANIZE PARTITION操作将不允许使用‘_p’作为前缀的分区名；但是DROP PARTITION操作可以使用‘_p’作为前缀的分区名。

在上述业务场景下，此前通常需要DBA在插入值触及转换点之前手动增加分区，稍有疏忽就有可能导致新的数据插入失败。通过创建INTERVAL RANGE分区表，可以由系统自动增加分区，避免数据不能及时插入。

由于分区数量最多只能达到8192，自动增加分区的数量也会受限制。因此建议您配合分区表的生命周期管理解决方案使用，定期增加分区，同时定期将冷数据所在的分区自动迁移到OSS上。

更多示例如下：

/* 设定间隔为数字类型，大小为1000 */
CREATE TABLE t(a int, b datetime)
PARTITION BY RANGE(a) INTERVAL(1000) (
    PARTITION p0 VALUES LESS THAN(1000)
);

/* 由于partition expression只能是INT_RESULT，所以不需要显式指定type，设定大小即可 */
CREATE TABLE t(a int, b datetime)
PARTITION BY RANGE(MONTH(b)) INTERVAL(1) (
    PARTITION p0 VALUES LESS THAN(2)
);

/* 由于分区键不是时间类型，设定间隔为数字类型，大小为1000 */
CREATE TABLE t(a int, b datetime)
PARTITION BY RANGE COLUMNS(a) INTERVAL(1000) (
    PARTITION p0 VALUES LESS THAN(1000)
);

/* 设定间隔为1年 */
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')
);