在cassandra数据库中，有一个叫做TTL的功能，即插入一条记录时，可以指定某一字段对应的TTL值，比如30s，那么当TTL到达30s后该条记录就会被自动删除。目前MySQL并未直接提供TTL的功能，但是我们可以通过event schedule功能实现。
创建一张测试表
CREATE TABLE `access_token` ( `token_id` varchar(100) COLLATE utf8_bin NOT NULL DEFAULT '', `created_timestamp` bigint(20) DEFAULT NULL, `expired_timestamp` bigint(20) DEFAULT NULL, `grant_type` varchar(100) COLLATE utf8_bin DEFAULT NULL, `refresh_token` varchar(100) COLLATE utf8_bin DEFAULT NULL, `scope` varchar(200) COLLATE utf8_bin DEFAULT NULL, `user_info` varchar(200) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`token_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
首先假设一些记录的expired_timestamp过期时间是“2020-12-31 23:59:59”，将该时间转换成unix时间戳，并乘以1000转换成毫秒存储，其他部分记录的expired_timestamp过期时间设置为当前时间
select unix_timestamp('2020-12-31 23:59:59')*1000; +--------------------------------------------+ | unix_timestamp('2020-12-31 23:59:59')*1000 | +--------------------------------------------+ | 1609430399000 | +--------------------------------------------+ 1 row in set (0.00 sec)
select unix_timestamp(current_timestamp)*1000; +----------------------------------------+ | unix_timestamp(current_timestamp)*1000 | +----------------------------------------+ | 1576319276000 | +----------------------------------------+ 1 row in set (0.00 sec)
插入测试数据
insert into access_token value('9T8F9EX42G9OXHPIDM98YBK74','1576319276000','1609430399000','mannual','6JL351QA38Z9ONOL9PWJ64A4U','test','david'); #该条测试数据的expired_timestamp是上述的 2020-12-31 23:59:59
insert into access_token value('AG8FLP3JQKGNCRZY0OPVTYV2W','1576319276000','1576319276000','auto','D7QOBDZPBKEP7TVWUG10XJA0S','ceshi','brain'); #该条测试数据的expired_timestamp的当前时间current_timestamp
查看测试数据
select * from access_token; +---------------------------+-------------------+-------------------+------------+---------------------------+-------+-----------+ | token_id | created_timestamp | expired_timestamp | grant_type | refresh_token | scope | user_info | +---------------------------+-------------------+-------------------+------------+---------------------------+-------+-----------+ | 9T8F9EX42G9OXHPIDM98YBK74 | 1576319276000 | 1609430399000 | mannual | 6JL351QA38Z9ONOL9PWJ64A4U | test | david | | AG8FLP3JQKGNCRZY0OPVTYV2W | 1576319276000 | 1576319276000 | auto | D7QOBDZPBKEP7TVWUG10XJA0S | ceshi | brain | +---------------------------+-------------------+-------------------+------------+---------------------------+-------+-----------+ 2 rows in set (0.00 sec)
将数据转换成timestamp显示
select token_id, from_unixtime(created_timestamp/1000) as created_timestamp, from_unixtime(expired_timestamp/1000) as expired_timestamp, grant_type, refresh_token , scope, user_info from access_token; +---------------------------+--------------------------+--------------------------+------------+---------------------------+-------+-----------+ | token_id | created_timestamp | expired_timestamp | grant_type | refresh_token | scope | user_info | +---------------------------+--------------------------+--------------------------+------------+---------------------------+-------+-----------+ | 9T8F9EX42G9OXHPIDM98YBK74 | 2019-12-14 18:27:56.0000 | 2020-12-31 23:59:59.0000 | mannual | 6JL351QA38Z9ONOL9PWJ64A4U | test | david | | AG8FLP3JQKGNCRZY0OPVTYV2W | 2019-12-14 18:27:56.0000 | 2019-12-14 18:27:56.0000 | auto | D7QOBDZPBKEP7TVWUG10XJA0S | ceshi | brain | +---------------------------+--------------------------+--------------------------+------------+---------------------------+-------+-----------+ 2 rows in set (0.01 sec)
创建event schedule，具体含义可参考https://dev.mysql.com/doc/refman/5.7/en/create-event.html
create event testdb.delete_access_token_expired_record on schedule every 2 minute do delete from testdb.access_token where expired_timestamp < unix_timestamp(current_timestamp)*1000 ;
查看该event信息
select * from information_schema.eventsG *************************** 1. row *************************** EVENT_CATALOG: def EVENT_SCHEMA: testdb EVENT_NAME: delete_access_token_expired_record DEFINER: root@localhost TIME_ZONE: SYSTEM EVENT_BODY: SQL EVENT_DEFINITION: delete from testdb.access_token where expired_timestamp < unix_timestamp(current_timestamp)*1000 EVENT_TYPE: RECURRING EXECUTE_AT: NULL INTERVAL_VALUE: 2 INTERVAL_FIELD: MINUTE SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION STARTS: 2019-12-14 18:38:59 ENDS: NULL STATUS: ENABLED ON_COMPLETION: NOT PRESERVE CREATED: 2019-12-14 18:38:59 LAST_ALTERED: 2019-12-14 18:38:59 LAST_EXECUTED: 2019-12-14 10:38:59 EVENT_COMMENT: ORIGINATOR: 543306 CHARACTER_SET_CLIENT: utf8mb4 COLLATION_CONNECTION: utf8mb4_0900_ai_ci DATABASE_COLLATION: utf8mb4_0900_ai_ci 1 row in set (0.01 sec)
该event每两分钟运行一次，如果access_token表中字段expired_timestamp的时间戳小于当前时间戳，说明该条记录已经过期，可以删除。
再次查看access_token表时，发现过期数据已经被删除
select token_id, from_unixtime(created_timestamp/1000) as created_timestamp, from_unixtime(expired_timestamp/1000) as expired_timestamp, grant_type, refresh_token , scope, user_info from access_token; +---------------------------+--------------------------+--------------------------+------------+---------------------------+-------+-----------+ | token_id | created_timestamp | expired_timestamp | grant_type | refresh_token | scope | user_info | +---------------------------+--------------------------+--------------------------+------------+---------------------------+-------+-----------+ | 9T8F9EX42G9OXHPIDM98YBK74 | 2019-12-14 18:27:56.0000 | 2020-12-31 23:59:59.0000 | mannual | 6JL351QA38Z9ONOL9PWJ64A4U | test | david | +---------------------------+--------------------------+--------------------------+------------+---------------------------+-------+-----------+ 1 row in set (0.00 sec)
再次插入两条数据，一条数据的expired_timestamp为当前时间戳，一条数据的expired_timestamp为5分钟后
insert into access_token value('4MXCXCXQRZRY1FVYAO9DG99F9','1576319276000','1576321202000','mannual','EN525M7AW448RBR3386ODM9T3','test','tim'); insert into access_token value('19XKLPXCAYGDJKH9VB7KA9V3Y','1576321336000','1576321504000','auto','3YBVRXTBW7578SVXMEJ9UK67E','ceshi','jhon');
查看当前数据
select token_id, from_unixtime(created_timestamp/1000) as created_timestamp, from_unixtime(expired_timestamp/1000) as expired_timestamp, grant_type, refresh_token , scope, user_info from access_token; +---------------------------+--------------------------+--------------------------+------------+---------------------------+-------+-----------+ | token_id | created_timestamp | expired_timestamp | grant_type | refresh_token | scope | user_info | +---------------------------+--------------------------+--------------------------+------------+---------------------------+-------+-----------+ | 19XKLPXCAYGDJKH9VB7KA9V3Y | 2019-12-14 19:02:16.0000 | 2019-12-14 19:05:04.0000 | auto | 3YBVRXTBW7578SVXMEJ9UK67E | ceshi | jhon | | 4MXCXCXQRZRY1FVYAO9DG99F9 | 2019-12-14 18:27:56.0000 | 2019-12-14 19:00:02.0000 | mannual | EN525M7AW448RBR3386ODM9T3 | test | tim | | 9T8F9EX42G9OXHPIDM98YBK74 | 2019-12-14 18:27:56.0000 | 2020-12-31 23:59:59.0000 | mannual | 6JL351QA38Z9ONOL9PWJ64A4U | test | david | +---------------------------+--------------------------+--------------------------+------------+---------------------------+-------+-----------+ 3 rows in set (0.00 sec)
过两分钟后，再次查看数据时，发现刚才插入access_token为当前时间戳的数据已经被删除
select token_id, from_unixtime(created_timestamp/1000) as created_timestamp, from_unixtime(expired_timestamp/1000) as expired_timestamp, grant_type, refresh_token , scope, user_info from access_token; +---------------------------+--------------------------+--------------------------+------------+---------------------------+-------+-----------+ | token_id | created_timestamp | expired_timestamp | grant_type | refresh_token | scope | user_info | +---------------------------+--------------------------+--------------------------+------------+---------------------------+-------+-----------+ | 19XKLPXCAYGDJKH9VB7KA9V3Y | 2019-12-14 19:02:16.0000 | 2019-12-14 19:05:04.0000 | auto | 3YBVRXTBW7578SVXMEJ9UK67E | ceshi | jhon | | 9T8F9EX42G9OXHPIDM98YBK74 | 2019-12-14 18:27:56.0000 | 2020-12-31 23:59:59.0000 | mannual | 6JL351QA38Z9ONOL9PWJ64A4U | test | david | +---------------------------+--------------------------+--------------------------+------------+---------------------------+-------+-----------+ 2 rows in set (0.00 sec)
五分钟以后再次查看，刚才插入access_token为当前时间五分钟后的时间戳的数据也已经被删除
mysql> select token_id, from_unixtime(created_timestamp/1000) as created_timestamp, from_unixtime(expired_timestamp/1000) as expired_timestamp, grant_type, refresh_token , scope, user_info from access_token; +---------------------------+--------------------------+--------------------------+------------+---------------------------+-------+-----------+ | token_id | created_timestamp | expired_timestamp | grant_type | refresh_token | scope | user_info | +---------------------------+--------------------------+--------------------------+------------+---------------------------+-------+-----------+ | 9T8F9EX42G9OXHPIDM98YBK74 | 2019-12-14 18:27:56.0000 | 2020-12-31 23:59:59.0000 | mannual | 6JL351QA38Z9ONOL9PWJ64A4U | test | david | +---------------------------+--------------------------+--------------------------+------------+---------------------------+-------+-----------+ 1 row in set (0.00 sec)
禁用该event
alter event testdb.delete_access_token_expired_record disable;
再次查看该event
select * from information_schema.eventsG *************************** 1. row *************************** EVENT_CATALOG: def EVENT_SCHEMA: testdb EVENT_NAME: delete_access_token_expired_record DEFINER: root@localhost TIME_ZONE: SYSTEM EVENT_BODY: SQL EVENT_DEFINITION: delete from testdb.access_token where expired_timestamp < unix_timestamp(current_timestamp)*1000 EVENT_TYPE: RECURRING EXECUTE_AT: NULL INTERVAL_VALUE: 2 INTERVAL_FIELD: MINUTE SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION STARTS: 2019-12-14 18:38:59 ENDS: NULL STATUS: DISABLED ON_COMPLETION: NOT PRESERVE CREATED: 2019-12-14 18:38:59 LAST_ALTERED: 2019-12-14 18:38:59 LAST_EXECUTED: 2019-12-14 11:14:59 EVENT_COMMENT: ORIGINATOR: 543306 CHARACTER_SET_CLIENT: utf8mb4 COLLATION_CONNECTION: utf8mb4_0900_ai_ci DATABASE_COLLATION: utf8mb4_0900_ai_ci 1 row in set (0.01 sec)
创建一个event，每天夜里一点定期你删除过期数据
CREATE EVENT testdb.delete_access_token_expired_record ON SCHEDULE EVERY 24 HOUR STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR) ON COMPLETION PRESERVE DISABLE ON SLAVE DO DELETE FROM testdb.access_token WHERE expired_timestamp < unix_timestamp(CURRENT_TIMESTAMP)*1000 ;