Mysql事件调度器学习

在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','','','mannual','6JL351QA38Z9ONOL9PWJ64A4U','test','david');    #该条测试数据的expired_timestamp是上述的 2020-12-31 23:59:59

insert into access_token value('AG8FLP3JQKGNCRZY0OPVTYV2W','','','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.events\G

*************************** 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','','','mannual','EN525M7AW448RBR3386ODM9T3','test','tim');

insert into access_token value('19XKLPXCAYGDJKH9VB7KA9V3Y','','','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.events\G

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

巴特西

Mysql事件调度器学习

最新文章

热门文章