TTL usage

Create a table with TTL properties

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

A table named t1 is created, where created_at is the time to live (TTL) column that stores the creation time of the data. You can set a TTL value based on the created_at column to determine when the data is deleted.

• Set the time to live of 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

  TTL='created_at@100' sets the time to live for rows in the table to 100 seconds. Rows older than this duration are marked as expired and will be deleted later.

• Set the time to live of 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 expression TTL ='created_at'+ INTERVAL 3 HOUR sets the time to live for rows in the table to 3 hours. Expired data is automatically deleted.

  • The TTL setting supports multiple time units, such as YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, and SECOND. This flexibility lets you select an appropriate time unit for efficient data management based on your requirements.

Modify the TTL properties of a table

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

The following examples modify the time to live of the created_at column in the t1 table.

• Modify the time to live of created_at to 10,000 seconds.

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

  Note

  This setting ensures that data is automatically deleted 10,000 seconds after its creation time, which is based on the value in the created_at field.

• Modify the time to live of created_at to 3 days.

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

  Note

  This sets the data expiration time to 3 days from the value in the created_at column. Data older than 3 days is automatically deleted.

Clear the TTL properties of a table

ALTER TABLE t1 TTL = '';

Query the TTL properties of a table

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';