1 创建存储过程

1.1 创建一个存储过程

1
2
3
4
5
6
7
8
9

CREATE PROCEDURE `ClearExpiredData`() BEGIN
-- 清理已过期10天的数据
delete from
  vid_blacklist
where
  expire_time > CAST('2010-10-10' AS DATETIME)
  and expire_time < SUBDATE(CURDATE(), 10);
-- end
END

1.2 执行存储过程

1

call ClearExpiredData()

2 数据库定时任务

2.1 创建一次性事件

1
2
3

CREATE EVENT clearEvent
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
DO call ClearExpiredData()

2.2 创建周期性事件

每天早上8点执行一次：

1
2
3
4
5

CREATE EVENT clearEvent
ON SCHEDULE EVERY 1 DAY
 STARTS '2022-01-24 08:00:00'
COMMENT '定时清理过期数据'
DO call ClearExpiredData();

2.3 查询数据库 Event

1

select * from information_schema.events where event_name = 'clearEvent'

2.4 删除 Event

1

drop EVENT clearEvent

总结

TODO