Mysql 定时任务删除过期数据

1、查看数据库是否开启定时调度

SHOW VARIABLES LIKE 'event_scheduler';//查看OFF为关
SET GLOBAL event_scheduler = ON;//设置开启，重启后失效

防止重启后失效

设置my.cnf，Linux一般在/etc/my.cnf ，

在[mysqld]下增加
event_scheduler = ON //重启后生效

2、执行命令

DROP event IF EXISTS e_delete_channel;
CREATE EVENT e_delete_channel
ON SCHEDULE
EVERY 1 DAY STARTS '2020-10-30 00:00:00'
DO
DELETE FROM t_instant_channel WHERE occurtime < DATE_SUB(CURRENT_TIMESTAMP,INTERVAL 7 DAY);
————————————————

实例配置：

1.  Mysql 创建表需添加创建数据时间的字段，如 created_at 
 
mysql> show create table website_record\G;
*************************** 1. row ***************************
       Table: website_record
Create Table: CREATE TABLE `website_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` datetime(6) NOT NULL,
  `instance_id` varchar(200) NOT NULL,
  `status` int(11) NOT NULL,
  `env_region` varchar(100) NOT NULL,
  `app_name` varchar(200) DEFAULT NULL,
  `resource_name` varchar(200) NOT NULL,
  `system_name` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
 
 
2.  测试插入部分数据
3.  mysql> insert into website_record values(10,'2023-09-19 08:26:31','111',1,'test001','test001','001','002');
4.  Query OK, 1 row affected (0.00 sec)
5.  
6.  mysql> insert into website_record values(10,'2023-09-19 08:26:31','111',1,'test001','test001','001','0022');
7.  ERROR 1062 (23000): Duplicate entry '10' for key 'PRIMARY'
8.  mysql> insert into website_record values(11,'2023-09-19 08:26:31','111',1,'test001','test001','001','0022');
9.  Query OK, 1 row affected (0.01 sec)
10. 
11. mysql> insert into website_record values(12,'2023-09-19 08:26:31','111',1,'test001','test001','001','0022');
12. Query OK, 1 row affected (0.00 sec)
13. 
14. mysql> insert into website_record values(13,'2023-09-19 08:26:31','111',1,'test001','test001','001','0022');
15. Query OK, 1 row affected (0.00 sec)
16. 
17. mysql> 
18. mysql> select * from website_record;
19. +----+----------------------------+-------------+--------+------------+----------+---------------+-------------+
20. | id | created_at                 | instance_id | status | env_region | app_name | resource_name | system_name |
21. +----+----------------------------+-------------+--------+------------+----------+---------------+-------------+
22. | 10 | 2023-09-19 08:26:31.000000 | 111         |      1 | test001    | test001  | 001           | 002         |
23. | 11 | 2023-09-19 08:26:31.000000 | 111         |      1 | test001    | test001  | 001           | 0022        |
24. | 12 | 2023-09-19 08:26:31.000000 | 111         |      1 | test001    | test001  | 001           | 0022        |
25. | 13 | 2023-09-19 08:26:31.000000 | 111         |      1 | test001    | test001  | 001           | 0022        |
 
 
4.  创建定时任务event 
###创建事件event 开始时间2023-9-19 16:25:00 每一分钟执行一次，删除website_record表中created_at字段时间早于现在1分钟的数据。
 
DROP event IF EXISTS e_delete_channel;
CREATE EVENT e_delete_channel
ON SCHEDULE
EVERY 1 Minute STARTS '2023-9-19 16:25:00'
DO
DELETE FROM website_record WHERE created_at < DATE_SUB(CURRENT_TIMESTAMP,INTERVAL 1 Minute);
 
显示 events scheduler 调度事件
 
mysql> show events\G; 
*************************** 1. row ***************************
                  Db: datasource
                Name: e_delete_channel
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 1
      Interval field: MINUTE
              Starts: 2023-09-19 16:25:00
                Ends: NULL
              Status: ENABLED
          Originator: 0
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
 
5.  验证数据是否已经被定时任务删除，定时任务是否已经生效
 
 
mysql> select * from website_record;
Empty set (0.00 sec)