Mysql 数据库 EVENT 功能——定期删除数据

目录

前言

本篇文章介绍下如何在 mysql 数据库中设置，定期（类似 rotate 功能）删除 Mysql 数据库一个指定数据表中一段时间之前的数据。

Mysql 可以通过 event 功能实现此需求，Mysql5.1 版本开始引进 event 概念。

开启事件功能

## 首先查看 是否已经开启事件功能
mysql>
mysql> select @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| ON                |
+-------------------+
mysql> show variables like 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set, 1 warning (0.00 sec)



## 如果是未开启状态 通过以下命令开启
mysql> SET GLOBAL event_scheduler = ON; SET GLOBAL event_scheduler = 1; — 0代表关闭

## 也可以通过配置文件开启，编写 my.cnf 文件后重启 mysqld 服务生效
[mysqld]
event_scheduler=ON

创建事件

  1. 可以直接创建事件，指定定期执行 sql 语句
mysql> create event e_delete_wififlows on schedule every 5 second 
       do delete from wififlows where 
       timestamp < (CURRENT_TIMESTAMP() + INTERVAL -2 MINUTE);
  1. 也可以先创建一个 PROCEDURE 存储（类似函数），然后创建事件调用这个PROCEDURE。
  • 创建 PROCEDURE
## 修改结束符的方法
mysql> delimiter $$　　#将语句的结束符号从分号;临时改为两个$$(可以是自定义)
mysql> CREATE PROCEDURE `del_record_month`(IN `date_inter` INT) 
-> BEGIN
-> 　　DELETE FROM request_log
->    WHERE 
->    (TO_DAYS(NOW()) - TO_DAYS(FROM_UNIXTIME(unix_timestamp(create_time),'%Y%m%d'))) >=date_inter
-> END$$
mysql> delimiter ;　　#将语句的结束符号恢复为分号

## 直接一句话创建的方法
mysql> CREATE PROCEDURE `del_record_month`(IN `date_inter` INT) BEGIN DELETE FROM request_log WHERE (TO_DAYS(NOW()) - TO_DAYS(FROM_UNIXTIME(unix_timestamp(create_time),'%Y%m%d'))) >=date_inter END ;
  • 创建 event 调用上面创建的 PROCEDURE
mysql> CREATE EVENT `del_record_month_event` ON SCHEDULE EVERY 1 DAY STARTS '2020-05-27 12:55:00' ON COMPLETION NOT PRESERVE ENABLE DO CALL del_record_month(2);

查看已有定时事件任务

## 查看 procedure
mysql> show procedure status where Db='management';
+------------+------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db         | Name             | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------------+------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| management | del_record_month | PROCEDURE | root@localhost | 2020-05-27 12:48:45 | 2020-05-27 12:48:45 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
+------------+------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

mysql> show create PROCEDURE   del_record_month ;
+------------------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure        | sql_mode                                                                                                              | Create Procedure                                                                                                                                                                                               | character_set_client | collation_connection | Database Collation |
+------------------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| del_record_month | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `del_record_month`(IN `date_inter` INT)
DELETE FROM request_log WHERE (TO_DAYS(NOW()) - TO_DAYS(FROM_UNIXTIME(unix_timestamp(create_time),'%Y%m%d'))) >=date_inter | utf8                 | utf8_general_ci      | utf8_general_ci    |
+------------------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

mysql>

## 查看 EVENTS
mysql> SHOW EVENTS;
+------------+------------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| Db         | Name                   | Definer        | Time zone | Type      | Execute at | Interval value | Interval field | Starts              | Ends | Status  | Originator | character_set_client | collation_connection | Database Collation |
+------------+------------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| management | del_record_month_event | root@localhost | SYSTEM    | RECURRING | NULL       | 1              | DAY            | 2020-05-27 12:55:00 | NULL | ENABLED |          1 | utf8                 | utf8_general_ci      | utf8_general_ci    |
+------------+------------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'del_record_month_event' ;
+---------------+--------------+------------------------+----------------+-----------+------------+--------------------------+------------+------------+----------------+----------------+-----------------------------------------------------------------------------------------------------------------------+---------------------+------+---------+---------------+---------------------+---------------------+---------------------+---------------+------------+----------------------+----------------------+--------------------+
| EVENT_CATALOG | EVENT_SCHEMA | EVENT_NAME             | DEFINER        | TIME_ZONE | EVENT_BODY | EVENT_DEFINITION         | EVENT_TYPE | EXECUTE_AT | INTERVAL_VALUE | INTERVAL_FIELD | SQL_MODE                                                                                                              | STARTS              | ENDS | STATUS  | ON_COMPLETION | CREATED             | LAST_ALTERED        | LAST_EXECUTED       | EVENT_COMMENT | ORIGINATOR | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION |
+---------------+--------------+------------------------+----------------+-----------+------------+--------------------------+------------+------------+----------------+----------------+-----------------------------------------------------------------------------------------------------------------------+---------------------+------+---------+---------------+---------------------+---------------------+---------------------+---------------+------------+----------------------+----------------------+--------------------+
| def           | management   | del_record_month_event | root@localhost | SYSTEM    | SQL        | CALL del_record_month(2) | RECURRING  | NULL       | 1              | DAY            | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | 2020-05-27 12:55:00 | NULL | ENABLED | NOT PRESERVE  | 2020-05-27 12:46:28 | 2020-05-27 12:46:28 | 2020-05-27 12:55:00 |               |          1 | utf8                 | utf8_general_ci      | utf8_general_ci    |
+---------------+--------------+------------------------+----------------+-----------+------------+--------------------------+------------+------------+----------------+----------------+-----------------------------------------------------------------------------------------------------------------------+---------------------+------+---------+---------------+---------------------+---------------------+---------------------+---------------+------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

mysql>

事件的开启与关闭

开启某事件：
mysql> ALTER EVENT del_record_month_event ON COMPLETION PRESERVE ENABLE;
关闭某事件：
mysql> ALTER EVENT del_record_month_event ON COMPLETION PRESERVE DISABLE;
posted @ 2020-05-27 13:12  tomoku-dm  阅读(679)  评论(0)    收藏  举报