PolarDB:TTL Usage Guide

Create a table with TTL

You can use one of the following methods to create a table with TTL:

Create a table named t1 and specify created_at as the TTL timestamp column to represent the data's creation time. Set the time-to-live (TTL) value for created_at to control data deletion.

• Set the maximum TTL for created_at to 100 seconds.

  CREATE TABLE `t1` (
    `a` INT PRIMARY KEY,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_created_at (`created_at`)
  )ENGINE=InnoDB  TTL='created_at@100';

  Note

  The TTL='created_at@100' setting specifies that rows in the table expire after 100 seconds. Expired data is deleted later during the cleanup process.

• Set the maximum TTL for created_at to 3 hours.

  CREATE TABLE `t1` (
    `a` INT PRIMARY KEY,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_created_at (`created_at`)
  )ENGINE=InnoDB  TTL='created_at' + INTERVAL 3 HOUR;

  Note

  • The TTL ='created_at'+ INTERVAL 3 HOUR setting configures a 3-hour TTL. Data older than 3 hours is automatically deleted.

  • This TTL (time-to-live) syntax supports multiple time units: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, and SECOND. Choose the unit that best fits your data management needs.

Modify a table's TTL

You can use one of the following methods to modify the TTL property of a table:

Modify the TTL for the t1 table by adjusting the time-to-live (TTL) value for the created_at column.

• Set the maximum TTL for created_at to 10,000 seconds.

  ALTER TABLE `t1` TTL='created_at@10000';

  Note

  Setting TTL to 10,000 seconds on the created_at column ensures data is deleted 10,000 seconds after its creation.

• Set the maximum TTL for created_at to 3 days.

  ALTER TABLE `t1` TTL='created_at' + INTERVAL 3 DAY;

  Note

  Adding 3 days to the created_at timestamp sets the expiration time to 3 days. Data older than 3 days is automatically deleted.

Remove TTL from a table

ALTER TABLE t1 TTL = '';

Query a table's TTL setting

SHOW CREATE TABLE `t1` FULL;

CREATE TABLE `t1` (  
`a` INT PRIMARY KEY,  
`created_at` TIMESTAMP DEFAULT 
CURRENT_TIMESTAMP,  
 KEY idx_created_at (`created_at`)
)ENGINE=InnoDB  TTL='created_at@259200';