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.