如何使用CREATETABLE语法创建TTL表_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

本文介绍了TTL表相关的CREATE TABLE语法。

若在建表时指定local_partition_definition语法，则将创建一张TTL表，TTL表会将每个物理表按照时间进行分区。

如果需要将t_order的物理表按照gmt_modified列进行时间分区，每个分区间隔一个月，每个分区12个月后过期，提前创建3个分区，创建语法如下：

CREATE TABLE t_order (
    id bigint NOT NULL AUTO_INCREMENT,
    gmt_modified DATETIME NOT NULL,
    PRIMARY KEY (id, gmt_modified)
)
PARTITION BY HASH(id)
PARTITIONS 16
LOCAL PARTITION BY RANGE (gmt_modified)
INTERVAL 1 MONTH
EXPIRE AFTER 12
PRE ALLOCATE 3;

表 1. 参数说明
参数	是否必填	示例	说明
LOCAL PARTITION BY RANGE (column_name)	是	LOCAL PARTITION BY RANGE (gmt_modified)	指定TTL表的物理时间分区列。在物理表上数据会以此列做时间分区。
STARTWITH 'yyyy-MM-dd'	否	STARTWITH '2022-01-01'	初始时间分区。TTL表会把“初始时间分区”作为第一个分区。
INTERVAL interval_count MONTH	是	INTERVAL 2 MONTH	时间分区的间隔，支持的时间粒度为YEAR、MONTH、DAY。
EXPIRE AFTER expire_after_count	否	EXPIRE AFTER 6	指定分区失效的时间。 INTERVAL 2 MONTHEXPIRE AFTER 6 表示每隔2个月划分一个物理时间分区每经过6倍物理时间分区（12个月），失效1个分区。
PRE ALLOCATE pre_allocate_count	否	PRE ALLOCATE 2	指定提前创建的分区数量。示例表示提前创建2个物理时间分区。
PIVOTDATE pivotdate_func	否	PIVOTDATE NOW()	指定基准时间，一般用默认值。
DISABLE SCHEDULE	否	DISABLE SCHEDULE	禁用自动分区滚动。如果开启的话，过期分区会自动被删除，新分区会自动提前创建。

语法限制

TTL表支持的时间分区列类型为：date、datetime；
所有的唯一键（包括主键）必须包含TTL表的local partition by range时间分区列；
TTL表最多支持32个最大物理时间分区。

查看TTL表的分区元数据

TTL表创建后，可以通过information_schema.local_partitions视图查看物理表上的分区信息。每个物理时间分区（Local Partition）会存储小于PARTITION_DESCRIPTION的数据。比如下面的例子中，'2021-11-15'的数据会存储到分区：p20211201。

每个物理时间分区（Local Partition）的失效时间会通过PARTITION_COMMENT显示。

说明

local_partition是示例中的库名称，请根据实际情况进行替换。

select * from information_schema.local_partitions where table_name = 't_order' and table_schema='local_partition'\G;

*************************** 1. row ***************************
         TABLE_SCHEMA: local_partition
           TABLE_NAME: t_order
       PARTITION_NAME: p20211101
     PARTITION_METHOD: RANGE COLUMNS
 PARTITION_EXPRESSION: `gmt_modified`
PARTITION_DESCRIPTION: '2021-11-01'
    PARTITION_COMMENT: expire:2022-11-01
*************************** 2. row ***************************
         TABLE_SCHEMA: local_partition
           TABLE_NAME: t_order
       PARTITION_NAME: p20211201
     PARTITION_METHOD: RANGE COLUMNS
 PARTITION_EXPRESSION: `gmt_modified`
PARTITION_DESCRIPTION: '2021-12-01'
    PARTITION_COMMENT: expire:2022-12-01
*************************** 3. row ***************************
         TABLE_SCHEMA: local_partition
           TABLE_NAME: t_order
       PARTITION_NAME: pmax
     PARTITION_METHOD: RANGE COLUMNS
 PARTITION_EXPRESSION: `gmt_modified`
PARTITION_DESCRIPTION: MAXVALUE
    PARTITION_COMMENT:
7 rows in set (13.19 sec)

校验物理分区的完整性

校验所有物理表（包括GSI）的物理时间分区（Local Partition）的完整性。

check table t_order with local partition;