mysql 的事件调度器_Mysql事件调度器学习-CSDN博客

本文链接：https://blog.csdn.net/weixin_39878646/article/details/113441245

在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 * fromaccess_token;+---------------------------+-------------------+-------------------+------------+---------------------------+-------+-----------+

+---------------------------+-------------------+-------------------+------------+---------------------------+-------+-----------+

+---------------------------+-------------------+-------------------+------------+---------------------------+-------+-----------+

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 fromaccess_token;+---------------------------+--------------------------+--------------------------+------------+---------------------------+-------+-----------+

+---------------------------+--------------------------+--------------------------+------------+---------------------------+-------+-----------+

+---------------------------+--------------------------+--------------------------+------------+---------------------------+-------+-----------+

2 rows in set (0.01 sec)

createevent testdb.delete_access_token_expired_recordon schedule every 2minute

dodelete from testdb.access_token where expired_timestamp < unix_timestamp(current_timestamp)*1000 ;

查看该event信息

select * frominformation_schema.events\G*************************** 1. row ***************************EVENT_CATALOG: def

EVENT_SCHEMA: testdb

EVENT_NAME: delete_access_token_expired_record

DEFINER: root@localhostTIME_ZONE: SYSTEM

EVENT_BODY: SQL

EVENT_DEFINITION:delete from testdb.access_token where expired_timestamp < unix_timestamp(current_timestamp)*1000EVENT_TYPE: RECURRING

EXECUTE_AT:NULLINTERVAL_VALUE:2INTERVAL_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:59ENDS:NULLSTATUS: ENABLED

ON_COMPLETION:NOTPRESERVE

CREATED:2019-12-14 18:38:59LAST_ALTERED:2019-12-14 18:38:59LAST_EXECUTED:2019-12-14 10:38:59EVENT_COMMENT:

ORIGINATOR:543306CHARACTER_SET_CLIENT: utf8mb4

COLLATION_CONNECTION: utf8mb4_0900_ai_ci

DATABASE_COLLATION: utf8mb4_0900_ai_ci1 row in set (0.01 sec)

该event每两分钟运行一次，如果access_token表中字段expired_timestamp的时间戳小于当前时间戳，说明该条记录已经过期，可以删除。

再次查看access_token表时，发现过期数据已经被删除

+---------------------------+--------------------------+--------------------------+------------+---------------------------+-------+-----------+

+---------------------------+--------------------------+--------------------------+------------+---------------------------+-------+-----------+

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

查看当前数据

+---------------------------+--------------------------+--------------------------+------------+---------------------------+-------+-----------+

| 4MXCXCXQRZRY1FVYAO9DG99F9 | 2019-12-14 18:27:56.0000 | 2019-12-14 19:00:02.0000 | mannual | EN525M7AW448RBR3386ODM9T3 | test | tim |

+---------------------------+--------------------------+--------------------------+------------+---------------------------+-------+-----------+

3 rows in set (0.00 sec)

过两分钟后，再次查看数据时，发现刚才插入access_token为当前时间戳的数据已经被删除

+---------------------------+--------------------------+--------------------------+------------+---------------------------+-------+-----------+

+---------------------------+--------------------------+--------------------------+------------+---------------------------+-------+-----------+

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 fromaccess_token;+---------------------------+--------------------------+--------------------------+------------+---------------------------+-------+-----------+

+---------------------------+--------------------------+--------------------------+------------+---------------------------+-------+-----------+

+---------------------------+--------------------------+--------------------------+------------+---------------------------+-------+-----------+

1 row in set (0.00 sec)

禁用该event

alter event testdb.delete_access_token_expired_record disable;

再次查看该event

select * frominformation_schema.events\G*************************** 1. row ***************************EVENT_CATALOG: def

EVENT_SCHEMA: testdb

EVENT_NAME: delete_access_token_expired_record

DEFINER: root@localhostTIME_ZONE: SYSTEM

EVENT_BODY: SQL

EVENT_DEFINITION:delete from testdb.access_token where expired_timestamp < unix_timestamp(current_timestamp)*1000EVENT_TYPE: RECURRING

EXECUTE_AT:NULLINTERVAL_VALUE:2INTERVAL_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:59ENDS:NULLSTATUS: DISABLED

ON_COMPLETION:NOTPRESERVE

CREATED:2019-12-14 18:38:59LAST_ALTERED:2019-12-14 18:38:59LAST_EXECUTED:2019-12-14 11:14:59EVENT_COMMENT:

ORIGINATOR:543306CHARACTER_SET_CLIENT: utf8mb4

COLLATION_CONNECTION: utf8mb4_0900_ai_ci

DATABASE_COLLATION: utf8mb4_0900_ai_ci1 row in set (0.01 sec)

创建一个event，每天夜里一点定期你删除过期数据

CREATEEVENT testdb.delete_access_token_expired_recordON SCHEDULE EVERY 24 HOUR STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1HOUR)ON COMPLETION PRESERVE DISABLE ONSLAVE

DODELETE FROM testdb.access_token WHERE expired_timestamp < unix_timestamp(CURRENT_TIMESTAMP)*1000 ;