:Definitions of TTL tables

TTL_EXPR

Specifies the time column and the data retention period of a TTL table.

• Syntax:

  TTL_EXPR = ttl_column EXPIRE AFTER ttl_interval_definition timezone_definition
  
  ttl_interval_definition:
    ttl_interval_num { DAY | MONTH | YEAR }
  
  timezone_definition:
    TIMEZONE time_zone_value

  Note

  • ttl_column: the time column of the TTL table. Only columns of the Date, Datetime, and Timestamp types are supported.

  • ttl_interval_definition: the retention period of data in the TTL table. Unit: DAY, MONTH, or YEAR. If the difference between the now() value and the ttl_column value is greater than the ttl_interval_num value specified in ttl_interval_definition, the system automatically cleans up the corresponding data with the ttl_column value.

  • timezone_definition: the time zone of the TTL table. For time zone-sensitive types such as Timestamp, you can specify a time zone in the TTL definition to implement time zone-based cleanup.

• Example:

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

  Note

  • The date_field column is the time column of the TTL table.

  • 2 MONTH specifies that the data retention period is two months. Data in the table expires after the 2-month retention period ends based on the values in the specified time column.

  • +08:00 specifies that the time zone is UTC+8.

TTL_JOB

Specifies the schedule on which a cleanup job runs. Default value: '0 0 1 */1 * ? *', which specifies that the cleanup job runs at 01:00 every day.

• Syntax:

  TTL_JOB = quartz_cron_expr_definition timezone_definition
  
  quartz_cron_expr_definition:
     CRON cron_expr_value
  
  timezone_definition:
    TIMEZONE time_zone_value

  Note

  quartz_cron_expr_definition: the schedule on which the cleanup job runs. The value is a CRON expression of the Quartz framework that consists of the following parts:

  • Second. Valid values: 0 to 59.

  • Minute. Valid values: 0 to 59.

  • Hour. Valid values: 0 to 23 and an asterisk (*). The asterisk (*) specifies that the cleanup job runs every hour.

  • Date, which specifies the day of the month. Valid values: 1 to 31 and an asterisk (*). The asterisk (*) specifies that the cleanup job runs every day.

  • Month. Valid values: 1 to 12 or JAN, FEB, MAR, APR, ..., to DEC, and an asterisk (*). The asterisk (*) specifies that the cleanup job runs every month.

  • Day of the week. Valid values: 1 to 7 or SUN, MON, TUE, WED, THU, FRI, to SAT, and a question mark (?). 1 specifies Sunday. The question mark (?) serves as the placeholder and specifies that no value is set.

  • Year. Valid values: year values and an asterisk (*). The asterisk (*) specifies that the cleanup job runs every year.

  Important

  Scheduled cleanup jobs consume a specific amount of resources. To prevent frequent cleanup jobs from affecting business stability, you cannot configure the execution schedule of a cleanup job to an expression accurate to the minute or second for PolarDB-X 2.0.

• Example: Configure a scheduled cleanup job to run at 02:00 every day.

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

TTL_ENABLE

Specifies whether to enable the scheduled cleanup job of a TTL table.

TTL_ENABLE = {'ON' | 'OFF'}

TTL_FILTER

When a cleanup job is triggered for a TTL table, the rows whose time column value is NULL are deleted by default. To retain these rows, explicitly set TTL_FILTER=COND_EXPR(<ttl_column> IS NOT NULL) for the TTL table.

Sample code:

ALTER TABLE `my_ttl_tbl` 
MODIFY TTL
SET
TTL_FILTER = COND_EXPR(`date_field` IS NOT NULL); 

Specify a TTL definition for an existing table

In this example, a table named my_ttl_tbl is created by executing the following statement:

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;

Execute the following ALTER statement to specify a TTL definition for the my_ttl_tbl table:

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

Specify a TTL definition when you create a table

Execute the following statement to create a table named my_ttl_tbl with a TTL definition specified:

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 TTL = TTL_DEFINITION ( TTL_EXPR = `date_field` EXPIRE AFTER 2 MONTH TIMEZONE '+08:00' ) 
PARTITION BY KEY(`id`)
PARTITIONS 8;

Modify the time column

Execute the following ALTER statement to change the time column from date_field to create_date for the TTL definition:

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

Modify the data retention period

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

Modify the execution schedule of a cleanup job

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

Modify the enabling status of a scheduled cleanup job

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