This topic describes how to use the CREATE TABLE statement to create a TTL table.

You can create a TTL table by specifying the local_partition_definition syntax in the CREATE TABLE statement. A TTL table partitions each physical tables based on time.

For example, you want to partition a table named t_order by time that is specified in the gmt_modified column at an interval of one month. Each partition expires 12 months after the partition is created and three partitions are created in advance. The following syntax provides an example on how to create the table:

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;
Table 1. The following table describes the parameters in the preceding statement.
Parameter Required Examples Description
LOCAL PARTITION BY RANGE (column_name) Yes LOCAL PARTITION BY RANGE (gmt_modified) Specifies the physical time partition key of the TTL table. Data in physical tables are partitioned by time based on the value of the partition key.
STARTWITH 'yyyy-MM-dd' No STARTWITH '2022-01-01' Specifies the initial time partition. The TTL table uses the partition specified by this parameter as the first partition.
INTERVAL interval_count MONTH Yes INTERVAL 2 MONTH Specifies the interval between partitions. Valid units: YEAR, MONTH, and DAY.
EXPIRE AFTER expire_after_count No EXPIRE AFTER 6 Specifies the validity period of a partition. Example:

INTERVAL 2 MONTHEXPIRE AFTER 6

This value indicates that a local partition is created every two months and each partition expires after six times the interval at which new local partitions are created, which is 12 months.

PRE ALLOCATE pre_allocate_count No PRE ALLOCATE 2 Specifies the number of partitions that you want to create in advance.

The example value indicates that two local partitions are created in advance.

PIVOTDATE pivotdate_func No PIVOTDATE NOW() Specifies the base time. In most cases, you can keep the default value of this parameter.
DISABLE SCHEDULE No DISABLE SCHEDULE Specifies whether to disable the automatic creation and deletion of partitions.

If you enable the automatic creation and deletion of partitions, expired partitions are automatically deleted, and new partitions are automatically created in advance.

Limits on syntax

  • The partition key of a TTL table supports the following data format: date and datetime.
  • All unique keys including primary keys must include the partition key of the TTL table. This partition key is specified by the LOCAL PARTITION BY RANGE parameter.
  • A TTL table supports up to 32 local partitions.

View the metadata of partitions of a TTL table

After you create a TTL table, you can view the information about the partitions of the physical tables in the information_schema.local_partitions view. Data stored in each local partition is later than the date specified by PARTITION_DESCRIPTION. In this example, data generated on November 15, 2021 is stored in the p20211201 partition.

The expiration data of a local partition is specified by PARTITION_COMMENT.

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

*************************** 1. row ***************************
         TABLE_SCHEMA: local_partition
           TABLE_NAME: t_create_lxok
       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_create_lxok
       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_create_lxok
       PARTITION_NAME: pmax
     PARTITION_METHOD: RANGE COLUMNS
 PARTITION_EXPRESSION: `gmt_modified`
PARTITION_DESCRIPTION: MAXVALUE
    PARTITION_COMMENT:
7 rows in set (13.19 sec)

Verify the integrity of each local partition

Execute the following statement to verify the integrity of the local partitions of all physical tables including global secondary indexes (GSIs).
check table t_order with local partition;
456789