Create a TTL table

Row-based archiving

• For engine version MySQL 5.7, the instance version must be polardb-2.4.0_5.4.19-20240927_xcluster5.4.19-20240920 or later.

• For engine version MySQL 8.0, the instance version must be polardb-2.4.0_5.4.19-20240927_xcluster8.4.19-20240924 or later.

Partition-based archiving

• For engine version MySQL 5.7, the instance version must be polardb-2.5.0_5.4.20-20250328_xcluster5.4.20-20250221 or later.

• For engine version MySQL 8.0, the instance version must be polardb-2.5.0_5.4.20-20250328_xcluster8.4.20-20250304 or later.

Syntax

ALTER TABLE table_name 
MODIFY TTL
SET
TTL_EXPR = xxx
[,TTL_JOB = xxx]
[,TTL_PART_INTERVAL = xxx]
[,TTL_FILTER = xxx]
[,TTL_ENABLE = xxx]
[,TTL_CLEANUP = xxx]
[,ARCHIVE_TYPE = xxx]
[,ARCHIVE_TABLE_PRE_ALLOCATE = xxx]
[,ARCHIVE_TABLE_POST_ALLOCATE = xxx]
;

Example

Create an example partitioned table named my_ttl_tbl:

CREATE TABLE `my_ttl_tbl` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`date_field` datetime DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`id`)
PARTITIONS 8;

Add a TTL definition:

ALTER TABLE my_ttl_tbl 
MODIFY TTL
SET 
TTL_EXPR = `date_field` EXPIRE AFTER 2 MONTH TIMEZONE '+08:00';

TTL_EXPR

Defines the TTL time column, data retention period, and data expiration policy.

• Syntax

  TTL_EXPR = data_expiration_expr_definition
  
  data_expiration_expr_definition:
      data_expire_by_time_defition
    | data_expire_by_part_count_defition
    
  data_expire_by_time_defition:
    ttl_time_column_expr EXPIRE AFTER data_expire_expr_definition timezone_definition
  
  ttl_time_column_expr:
      ttl_time_col
    | FROM_UNIXTIME(ttl_ts_int_col)
    | FROM_UNIXTIME(ttl_milli_ts_int_col/1000)
    
  data_expire_expr_definition:
    EXPIRE AFTER ttl_expire_interval_definition
      
  ttl_expire_interval_definition:
    expire_after_interval_count interval_timeunit_expr
  
  interval_timeunit_expr:
       DAY 
     | MONTH
     | YEAR
  
  timezone_definition:
    TIMEZONE time_zone_value
  
  time_zone_value:
    '[+-]HH:MM'
  
  expire_after_interval_count:
    int_number
  
  data_expire_by_part_count_defition:
    ttl_int_col data_expire_over_definition
  
  data_expire_over_definition:
    EXPIRE OVER expire_over_partition_count PARTITIONS
  
  expire_over_partition_count:
    int_number

  Note

  The data_expiration_expr_definition parameter supports the following two data expiration policies:

  • data_expire_by_time_defition: Data expires automatically after a fixed time interval. Data stored in a TTL table for longer than the specified interval is considered expired. This policy is typically used for time-based archiving and is referred to as the EXPIRE AFTER policy in this topic.

    • ttl_time_col: The TTL column. The following data types are supported:

      • Time types: DATE, DATETIME, and TIMESTAMP.

      • Integer types: INT and BIGINT.

    • ttl_ts_int_col: A column of an integer type that represents a timestamp in seconds, such as a UNIX timestamp. The expression FROM_UNIXTIME(ttl_ts_int_col) converts the integer timestamp to a time value for expiration checks.

    • ttl_milli_ts_int_col: A column of an integer type that represents a timestamp in milliseconds, such as a Java timestamp. The expression FROM_UNIXTIME(ttl_ts_int_col/1000) converts the millisecond timestamp to a time value for expiration checks.

    • ttl_expire_interval_definition: The time-to-live (TTL) for data, in the format IntervalNumber IntervalTimeUnit. When (now() - ttl_time_column_expr) > ttl_expire_interval_definition, data that meets this condition is automatically deleted. The supported values for IntervalTimeUnit are DAY/MONTH/YEAR. Example:

      TTL_EXPR = `ttl_col` EXPIRE AFTER 3/*intervalNumber*/ MONTH /*intervalTimeUnit*/ 
      TIMEZONE '+08:00'

      • The expiration interval number IntervalNumber is 3, and the expiration interval unit IntervalTimeUnit is MONTH.

      • The expiration interval unit IntervalTimeUnit directly affects the truncation and normalization of the upper boundary of the cleanup time range. For more information, see TTL table cold data cleanup algorithm.

      • IntervalTimeUnit is the default time unit for the TTL_PART_INTERVAL expression. If TTL_PART_INTERVAL is not explicitly set, the system uses IntervalTimeUnit as the partition interval unit.

    • timezone_definition: Defines the time zone of the TTL column. The scheduled TTL task uses the time zone to calculate expiration. You must specify a time zone if the TTL column is of the TIMESTAMP type. Only the '[+-]HH:MM' format is supported.

  • data_expire_by_part_count_defition: Data expires automatically based on a fixed number of RANGE partitions. The TTL table retains a maximum of expire_over_partition_count partitions and deletes the oldest RANGE partition. This policy is typically used for partition-based archiving and is referred to as the EXPIRE OVER policy in this topic. This policy is often used with the TTL_PART_INTERVAL attribute.

    • ttl_int_col: A TTL column of an integer type.

    • expire_over_partition_count: The maximum number of partitions to retain.

• Examples

  • The TTL column is of type DATETIME. The TTL table retains data from the last three months.

    TTL_EXPR = `<ttl_col>` EXPIRE AFTER 3 MONTH TIMEZONE '+08:00'

  • The TTL column is of type TIMESTAMP and uses the UTC+10 time zone. The TTL table retains data from the last 30 days.

    TTL_EXPR = `<ttl_col>` EXPIRE AFTER 30 DAY TIMEZONE '+10:00'

  • The TTL column is of type INT and represents a UNIX timestamp in seconds. The TTL column uses the UTC+0 time zone. The TTL table retains data from the last 7 days.

    TTL_EXPR = FROM_UNIXTIME(`<ttl_col>`) EXPIRE AFTER 7 DAY TIMEZONE '+00:00'

  • The TTL column is of type INT and represents a timestamp in milliseconds. The TTL column uses the UTC+8 time zone. The TTL table retains data from the last 60 days.

    TTL_EXPR = FROM_UNIXTIME(`<ttl_col>`/1000) EXPIRE AFTER 60 DAY TIMEZONE '+08:00'

  • The TTL column is of type BIGINT and represents an auto-incrementing business serial number. The TTL table retains a maximum of 16 RANGE partitions.

    TTL_EXPR = `<ttl_col>` EXPIRE OVER 16 PARTITIONS

TTL_JOB

Defines the submission time and frequency of the scheduled TTL task. This attribute is optional. The default value is '0 0 1 */1 * ? *', which runs the task once a day at 01:00.

• Syntax

  TTL_JOB = quartz_cron_expr_definition［timezone_definition］
  
  quartz_cron_expr_definition :
     CRON cron_expr_value
  
  timezone_definition :
    TIMEZONE time_zone_value
  
  time_zone_value:
    '[+-]HH:MM'

• Parameters

  Parameter	Description
  cron_expr_value	Specifies the time and frequency of the system cleanup task by using a Quartz CRON expression. The expression has seven fields: Second (0-59) Minute (0-59) Hour (0-23, use * for every hour) Day of month (1-31. The asterisk * specifies every day.) Month (1-12 or JAN, FEB, MAR, APR, ..., DEC, where * represents every month) Day of week (1-7 or SUN, MON, TUE, WED, THU, FRI, SAT, where 1 is Sunday. Use ? as a placeholder if this field is not set.) Year (use * to represent every year)
  timezone_definition (optional)	The time_zone_value in timezone_definition is fixed to '+08:00'. By default, TTL tasks are scheduled based on the UTC+8 time zone. Other time zones are not supported.

  Important

  Because scheduled cleanup tasks consume system resources, PolarDB-X 2.0 does not support setting the task frequency to the minute or second level. We also recommend avoiding an hourly frequency to prevent frequent task execution from affecting service stability.

• Example

  The TTL background cleanup task is run once a day at 01:00:

  TTL_JOB = CRON '0 0 1 */1 * ? *' TIMEZONE '+08:00'

• Notes

  • TTL_JOB defines the time and frequency at which the scheduled TTL task is automatically submitted. However, the actual execution of the TTL task is constrained by the maintenance window (the default window is from 02:00 to 05:00).

  • A TTL task that is not completed within the current maintenance window is automatically paused and resumes during the next maintenance window. For example, if you set TTL_JOB = CRON '0 0 1 */1 * ? *' TIMEZONE '+08:00' and the maintenance window is from 02:00 to 05:00, the TTL task is submitted daily at 01:00 but starts running at 02:00. If the task is not completed by 05:00, it is automatically paused and resumes at 02:00 the next day.

  • Ensure the TTL task can be completed within the maintenance window. Otherwise, residual data may accumulate.

TTL_PART_INTERVAL

Specifies the time or numeric interval between adjacent partitions of a TTL table and its archive table. This attribute applies in the following three main scenarios:

• When a TTL table uses partition-based archiving, the scheduled task automatically pre-creates new RANGE partitions for the TTL table. The interval between these new partitions is specified by TTL_PART_INTERVAL.

• When an archive table is created to store archived data, the database automatically defines RANGE partitions for the archive table. The time interval for these RANGE partitions is specified by TTL_PART_INTERVAL.

• After an archive table is created, the scheduled task automatically pre-creates new RANGE partitions for it. The interval between these new partitions is specified by TTL_PART_INTERVAL.

This attribute is typically used with TTL_EXPR.

• Syntax

  TTL_PART_INTERVAL = INTERVAL(int_value, interval_time_unit)
  
  interval_time_unit :
      DAY
    | MONTH
    | YEAR
    | NUMBER

  Note

  • If the interval unit is NUMBER, the TTL column must be of an integer type and use the EXPIRE OVER expiration policy. The value must be a positive integer.

  • In partition-based archiving, if you specify only the expiration interval in TTL_EXPR (for example, `ttl_col` EXPIRE AFTER 3 MONTH TIMEZONE '+08:00') and do not explicitly set TTL_PART_INTERVAL, the default value is determined as follows:

    • The time interval unit of TTL_PART_INTERVAL is the same as the expiration interval unit of TTL_EXPR.

    • The default interval number of TTL_PART_INTERVAL is 1.

• Examples

  • Partition by year (one partition per year).

    TTL_PART_INTERVAL = INTERVAL(1, YEAR)

  • Partition by month (one partition per month).

    TTL_PART_INTERVAL = INTERVAL(1, MONTH)

  • Partition by week (one partition every 7 days).

    TTL_PART_INTERVAL = INTERVAL(7, DAY)

  • Partition by day (one partition per day).

    TTL_PART_INTERVAL = INTERVAL(1, DAY)

  • Partition by integer range (one partition per interval of 100,000).

    TTL_PART_INTERVAL = INTERVAL(100000, NUMBER)

TTL_FILTER

For TTL tables that use row-based archiving, use this attribute to specify additional filtering conditions. This allows the TTL task to clean up only the expired data that meets specific business criteria.

• Syntax

  TTL_FILTER = COND_EXPR (query_cond_expr)

  query_cond_expr refers to the conditional expression in the WHERE clause of a standard DELETE statement. The default value is ''. This expression is combined with the time condition for TTL row-level cleanup by using the AND operator. For example, if the current cleanup time range is ttl_col < '2025-05-01' and query_cond_expr is status=1, the actual cleanup filter condition is (( ttl_col < '2025-05-01' ) AND ( status = 1)). Because the conditional expression for query_cond_expr cannot use the index of the TTL column, you should keep the expression as simple as possible. A complex expression may affect the row-level cleanup speed.

  Important

  By default, the cleanup task treats data rows with a NULL value in the TTL column as having the minimum value and cleans them up. You can set TTL_FILTER = COND_EXPR( <ttl_col_name> IS NOT NULL )) to prevent the cleanup task from automatically cleaning up data rows with a NULL value in the TTL column. This setting is supported only on instance versions polardb-2.5.0_5.4.20-20250328_xcluster5.4.20-20250221 (MySQL 5.7) and later or polardb-2.5.0_5.4.20-20250328_xcluster8.4.20-20250304 (MySQL 8.0) and later.

• Example

  The data cleanup rule applies only to expired data that is marked as completed (status=1):

  TTL_FILTER = COND_EXPR(`status` = 1)

Other parameters

Row-based archiving

The TTL table first archives expired data to OSS by uploading or synchronizing it through a columnar index. Then, it cleans up the expired data by using DELETE DML statements.

In row-based archiving, DELETE statements are used for cleanup. The cleanup logic varies based on the data type of the TTL column, primarily time types and integer types. The following sections provide examples for both.

CREATE TABLE ttl_t1
(
  id BIGINT,
  ttl_ts_col TIMESTAMP
)
PARTITION BY KEY(`id`)
PARTITIONS 8;

-- Specify TTL
ALTER TABLE `ttl_t1`
MODIFY TTL
SET
TTL_ENABLE='ON',
TTL_CLEANUP='OFF',
TTL_EXPR=`ttl_ts_col` EXPIRE AFTER 1 MONTH TIMEZONE '+08:00',
TTL_JOB=CRON '0 0 2 */1 * ? *' TIMEZONE '+08:00',
TTL_PART_INTERVAL=INTERVAL(1,MONTH),
ARCHIVE_TYPE='ROW',
ARCHIVE_TABLE_PRE_ALLOCATE=3,
ARCHIVE_TABLE_POST_ALLOCATE=24;

CREATE TABLE ttl_t2
(
  id BIGINT,
  ttl_int_col BIGINT,
  flag INT
)
PARTITION BY KEY(`id`)
PARTITIONS 8;

-- Specify TTL
ALTER TABLE `ttl_t2`
MODIFY TTL
SET
TTL_ENABLE = 'ON',
TTL_CLEANUP = 'OFF',
TTL_EXPR = FROM_UNIXTIME(`ttl_int_col`/1000) EXPIRE AFTER 1 MONTH TIMEZONE '+00:00',
TTL_JOB = CRON '0 0 2 */1 * ? *' TIMEZONE '+08:00',
TTL_PART_INTERVAL = INTERVAL(1,MONTH),
TTL_FILTER = COND_EXPR( `flag` = 1 ),
ARCHIVE_TYPE = 'ROW',
ARCHIVE_TABLE_PRE_ALLOCATE = 3,
ARCHIVE_TABLE_POST_ALLOCATE = 48;

Partition-based archiving

The TTL table first archives expired data to OSS by uploading or synchronizing it through a columnar index. This step is skipped if no archive table is configured. Then, it cleans up the expired data by using DROP (SUB)PARTITION DDL statements.

Partition-based archiving uses DROP (SUB)PARTITION DDL statements to clean up expired RANGE partitions. The following sections provide examples for archiving by primary partition and by secondary partition.

CREATE TABLE ttl_t_2_1_0
(
  id BIGINT,
  ttl_dt_col DATETIME
)
PARTITION BY RANGE COLUMNS(`ttl_dt_col`)
(
  PARTITION p1 VALUES LESS THAN ('2025-04-01'),
  PARTITION p2 VALUES LESS THAN ('2025-05-01'),
  PARTITION p3 VALUES LESS THAN ('2025-06-01')
);

-- Specify TTL
ALTER TABLE `ttl_t_2_1_0`
MODIFY TTL
SET
TTL_ENABLE = 'ON',
TTL_CLEANUP = 'OFF',
TTL_EXPR = `ttl_dt_col` EXPIRE AFTER 1 MONTH TIMEZONE '+08:00',
TTL_JOB = CRON '0 0 2 */1 * ? *' TIMEZONE '+08:00',
TTL_PART_INTERVAL = INTERVAL(1,MONTH),
ARCHIVE_TYPE = 'PARTITION',
ARCHIVE_TABLE_PRE_ALLOCATE = 3,
ARCHIVE_TABLE_POST_ALLOCATE = 48;

CREATE TABLE ttl_t_2_1_1
(
  id BIGINT,
  ttl_ts_col TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
)
PARTITION BY RANGE(UNIX_TIMESTAMP(`ttl_ts_col`))
(
  PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2025-04-01 00:00:00')),
  PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP('2025-05-01 00:00:00')),
  PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP('2025-06-01 00:00:00'))
);

-- Specify TTL
ALTER TABLE `ttl_t_2_1_1`
MODIFY TTL
SET
TTL_ENABLE = 'ON',
TTL_CLEANUP = 'OFF',
TTL_EXPR = `ttl_ts_col` EXPIRE AFTER 1 MONTH TIMEZONE '+08:00',
TTL_JOB = CRON '0 0 2 */1 * ? *' TIMEZONE '+08:00',
TTL_PART_INTERVAL = INTERVAL(1,MONTH),
ARCHIVE_TYPE = 'PARTITION',
ARCHIVE_TABLE_PRE_ALLOCATE = 3,
ARCHIVE_TABLE_POST_ALLOCATE = 48;

CREATE TABLE ttl_t_2_1_2
(
  id BIGINT,
  ttl_ts_int_col BIGINT
)
PARTITION BY RANGE COLUMNS(`ttl_ts_int_col`)
(
  PARTITION p20250401 VALUES LESS THAN (1743436800),
  PARTITION p20250501 VALUES LESS THAN (1746028800),
  PARTITION p20250601 VALUES LESS THAN (1748707200)
);

-- Specify TTL
ALTER TABLE `ttl_t_2_1_2`
MODIFY TTL
SET
TTL_ENABLE = 'ON',
TTL_CLEANUP = 'OFF',
TTL_EXPR = FROM_UNIXTIME(`ttl_ts_int_col`) EXPIRE AFTER 1 MONTH TIMEZONE '+08:00',
TTL_JOB = CRON '0 0 2 */1 * ? *' TIMEZONE '+08:00',
TTL_PART_INTERVAL = INTERVAL(1,MONTH),
ARCHIVE_TYPE = 'PARTITION',
ARCHIVE_TABLE_PRE_ALLOCATE = 3,
ARCHIVE_TABLE_POST_ALLOCATE = 48;

CREATE TABLE ttl_t_2_2
(
  id BIGINT,
  ttl_int_col BIGINT
)
PARTITION BY RANGE COLUMNS(`ttl_int_col`)
(
  PARTITION p1 VALUES LESS THAN (400000),
  PARTITION p2 VALUES LESS THAN (500000),
  PARTITION p3 VALUES LESS THAN (600000),
  PARTITION p4 VALUES LESS THAN (700000)
);

-- Specify TTL
ALTER TABLE `ttl_t_2_2`
MODIFY TTL
SET
TTL_ENABLE = 'ON',
TTL_CLEANUP = 'OFF',
TTL_EXPR = ttl_int_col EXPIRE OVER 3 PARTITIONS,
TTL_JOB = CRON '0 0 2 */1 * ? *' TIMEZONE '+08:00',
TTL_PART_INTERVAL = INTERVAL(100000,NUMBER),
ARCHIVE_TYPE = 'PARTITION',
ARCHIVE_TABLE_PRE_ALLOCATE = 3,
ARCHIVE_TABLE_POST_ALLOCATE = 48;

CREATE TABLE ttl_t_2_3_0
(
  id BIGINT,
  ttl_dt_col DATETIME
)
PARTITION BY KEY(`id`)
PARTITIONS 8
SUBPARTITION BY RANGE COLUMNS(`ttl_dt_col`)
(
  SUBPARTITION sp20250401 VALUES LESS THAN ('2025-04-01'),
  SUBPARTITION sp20250501 VALUES LESS THAN ('2025-05-01'),
  SUBPARTITION sp20250601 VALUES LESS THAN ('2025-06-01')
);

-- Specify TTL
ALTER TABLE `ttl_t_2_3_0`
MODIFY TTL
SET
TTL_ENABLE = 'ON',
TTL_CLEANUP = 'OFF',
TTL_EXPR = `ttl_dt_col` EXPIRE AFTER 1 MONTH TIMEZONE '+08:00',
TTL_JOB = CRON '0 0 2 */1 * ? *' TIMEZONE '+08:00',
TTL_PART_INTERVAL = INTERVAL(1,MONTH),
ARCHIVE_TYPE = 'SUBPARTITION',
ARCHIVE_TABLE_PRE_ALLOCATE = 3,
ARCHIVE_TABLE_POST_ALLOCATE = 48;

CREATE TABLE ttl_t_2_3_1
(
  id BIGINT,
  ttl_ts_col TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
)
PARTITION BY KEY(`id`)
PARTITIONS 8
SUBPARTITION BY RANGE(UNIX_TIMESTAMP(`ttl_ts_col`))
(
  SUBPARTITION sp20250401 VALUES LESS THAN (UNIX_TIMESTAMP('2025-04-01 00:00:00')),
  SUBPARTITION sp20250501 VALUES LESS THAN (UNIX_TIMESTAMP('2025-05-01 00:00:00')),
  SUBPARTITION sp20250601 VALUES LESS THAN (UNIX_TIMESTAMP('2025-06-01 00:00:00'))
);

-- Specify TTL
ALTER TABLE `ttl_t_2_3_1`
MODIFY TTL
SET
TTL_ENABLE = 'ON',
TTL_CLEANUP = 'OFF',
TTL_EXPR = `ttl_ts_col` EXPIRE AFTER 1 MONTH TIMEZONE '+08:00',
TTL_JOB = CRON '0 0 2 */1 * ? *' TIMEZONE '+08:00',
TTL_PART_INTERVAL = INTERVAL(1,MONTH),
ARCHIVE_TYPE = 'SUBPARTITION',
ARCHIVE_TABLE_PRE_ALLOCATE = 3,
ARCHIVE_TABLE_POST_ALLOCATE = 48;

CREATE TABLE ttl_t_2_3_2
(
  id BIGINT,
  ttl_ts_int_col BIGINT
)
PARTITION BY KEY(`id`)
PARTITIONS 8
SUBPARTITION BY RANGE COLUMNS(`ttl_ts_int_col`)
(
  SUBPARTITION sp20250401 VALUES LESS THAN (1743436800),
  SUBPARTITION sp20250501 VALUES LESS THAN (1746028800),
  SUBPARTITION sp20250601 VALUES LESS THAN (1748707200)
);

-- Specify TTL
ALTER TABLE `ttl_t_2_3_2`
MODIFY TTL
SET
TTL_ENABLE = 'ON',
TTL_CLEANUP = 'OFF',
TTL_EXPR = FROM_UNIXTIME(ttl_ts_int_col) EXPIRE AFTER 1 MONTH TIMEZONE '+08:00',
TTL_JOB = CRON '0 0 2 */1 * ? *' TIMEZONE '+08:00',
TTL_PART_INTERVAL = INTERVAL(1,MONTH),
ARCHIVE_TYPE = 'SUBPARTITION',
ARCHIVE_TABLE_PRE_ALLOCATE = 3,
ARCHIVE_TABLE_POST_ALLOCATE = 48;

CREATE TABLE ttl_t_2_4
(
  id BIGINT,
  ttl_int_col BIGINT
)
PARTITION BY KEY(`id`)
PARTITIONS 8
SUBPARTITION BY RANGE COLUMNS(`ttl_int_col`)
(
  SUBPARTITION sp1 VALUES LESS THAN (400000),
  SUBPARTITION sp2 VALUES LESS THAN (500000),
  SUBPARTITION sp3 VALUES LESS THAN (600000),
  SUBPARTITION sp4 VALUES LESS THAN (700000)
);

-- Specify TTL
ALTER TABLE `ttl_t_2_4`
MODIFY TTL
SET
TTL_ENABLE = 'ON',
TTL_CLEANUP = 'OFF',
TTL_EXPR = ttl_int_col EXPIRE OVER 3 PARTITIONS,
TTL_JOB = CRON '0 0 2 */1 * ? *' TIMEZONE '+08:00',
TTL_PART_INTERVAL = INTERVAL(100000,NUMBER),
ARCHIVE_TYPE = 'SUBPARTITION',
ARCHIVE_TABLE_PRE_ALLOCATE = 3,
ARCHIVE_TABLE_POST_ALLOCATE = 48;

Modify task switch (TTL_ENABLE)

By default, TTL_ENABLE is set to OFF. To enable automatic scheduling of the TTL task for the table, run the following SQL statement:

ALTER TABLE `my_ttl_tbl`
MODIFY TTL
SET
TTL_ENABLE = 'ON';

Modify data cleanup switch (TTL_CLEANUP)

By default, TTL_CLEANUP is set to OFF. When TTL_CLEANUP = 'OFF', the TTL task does not clean up any data or delete any expired partitions, but it automatically adds new partitions to the TTL table or archive table. Therefore, if you need to create an archive table to store historical data, enable this data cleanup switch only after the archive table is created. This practice prevents data from being cleaned up before it is archived.

ALTER TABLE `my_ttl_tbl`
MODIFY TTL
SET
TTL_CLEANUP = 'ON';

Modify data expiration definition (TTL_EXPR)

Assume the TTL_EXPR definition for the table my_ttl_tbl is:

TTL_EXPR = `date_field` EXPIRE AFTER 2 YEAR TIMEZONE '+08:00';

To change the expiration period to retain data from the last year, modify the TTL_EXPR definition:

ALTER TABLE `my_ttl_tbl`
MODIFY TTL
SET
TTL_EXPR = `date_field` EXPIRE AFTER 12 MONTH TIMEZONE '+08:00';

Modify task schedule (TTL_JOB)

To change the cleanup task schedule to 03:00 every day, run the following statement:

ALTER TABLE `my_ttl_tbl`
MODIFY TTL
SET
TTL_JOB = CRON '0 0 3 */1 * ? *' TIMEZONE '+08:00';

Modify partition interval (TTL_PART_INTERVAL)

To change the partition interval to one partition per month (INTERVAL(1,MONTH)), run the following statement:

ALTER TABLE `my_ttl_tbl`
MODIFY TTL
SET
TTL_PART_INTERVAL = INTERVAL(1,MONTH);

Modify filter condition (TTL_FILTER)

For row-based archiving, you can dynamically modify the filter condition used for data cleanup. For example, to clean up only data for completed orders (`order_status`=1), run the following statement:

ALTER TABLE `my_ttl_tbl`
MODIFY TTL
SET
TTL_FILTER=COND_EXPR(`order_status`=1);

To clear all additional filter conditions defined by TTL_FILTER, run the following statement:

ALTER TABLE `my_ttl_tbl`
MODIFY TTL
SET
TTL_FILTER=COND_EXPR(TRUE);

Modify archiving type (ARCHIVE_TYPE)

You can dynamically change the archiving policy of a TTL table by switching between partition-based and row-based archiving. Run the following SQL statement:

-- Change to row-based archiving
ALTER TABLE `my_ttl_tbl`
MODIFY TTL
SET
ARCHIVE_TYPE = 'ROW';

Modify pre-allocated partitions (ARCHIVE_TABLE_PRE_ALLOCATE)

To change the number of pre-allocated partitions for the TTL table to 3, run the following statement:

ALTER TABLE `my_ttl_tbl`
MODIFY TTL
SET
ARCHIVE_TABLE_PRE_ALLOCATE = 3;