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)