This topic describes how to use the ALTER TABLE statement to modify a TTL table.

Modify the scheduled task of a TTL table

ALTER TABLE tbl_name
    local_partition_alter_options

local_partition_alter_options:
        ALLOCATE LOCAL PARTITION
      EXPIRE LOCAL PARTITION [local_partition_name]
   REMOVE LOCAL PARTITIONING
   local_partition_definition 

local_partition_definition:
        LOCAL PARTITION BY RANGE (column_name)
    [STARTWITH 'yyyy-MM-dd']
    INTERVAL interval_count MONTH
    [EXPIRE AFTER expire_after_count]
    [PRE ALLOCATE pre_allocate_count]
    [PIVOTDATE pivotdate_func]
    [DISABLE SCHEDULE]

pivotdate_func:
        NOW()
      DATE_ADD(...)
   DATE_SUB(...)

Create a new local partition

If you execute the following statements for multiple times, only new partitions within the specified time range are created.

ALTER TABLE t_order ALLOCATE LOCAL PARTITION;

Delete a expired local partition

If you execute the following statements for multiple times, only expired partitions are deleted.

  • You can specify the name of the local partition that you want to delete. You can obtain the name of a local partition by using information_schema.local_partitions.
    ALTER TABLE t_order EXPIRE LOCAL PARTITION p20210401;
  • If you do not specify the name of the local partition that you want to delete, all expired local partitions are deleted.
    ALTER TABLE t_order EXPIRE LOCAL PARTITION;
    Note A local partition cannot be deleted before it expires. If you delete a local partition that does not expire, an error is reported.

Convert a TTL table to a normal table

ALTER TABLE t_order REMOVE LOCAL PARTITIONING;
Note To convert a TTL table to a normal table, you must migrate the data in the TTL table. Perform data migration during off-peak hours.

Convert a normal table to a TTL table

ALTER TABLE t_order
  LOCAL PARTITION BY RANGE (gmt_modified)
  STARTWITH '2021-01-01'
  INTERVAL 1 MONTH
  EXPIRE AFTER 12
  PRE ALLOCATE 6;
Note To convert a normal table to a TTL table, you must migrate the data in the normal table. Perform data migration during off-peak hours.