mysql编写存储过程，并创建定时任务执行

参考：https://blog.csdn.net/wq1039822486/article/details/79637168

mysql实现定时执行SQL语句需要用到Event

1.检查event事件是否开启

show variables like '%sche%';

1)如果Value值为OFF，则需要开启。需要超级权限

set global event_scheduler=1;

 2.创建存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `deleteBookNullData`()
BEGIN
-- 删除影响客户体验的小说
delete from book where id in (select a.book_id from (select book_id,count(*) counta from book_index group by book_id) a right join (select book_id,count(*) countb from book_index where index_name is null group by book_id) b on a.book_id = b.book_id
where b.countb/a.counta > 0.05);

-- 删除影响客户体验的小说目录
delete from book_index where book_id in (select a.book_id from (select book_id,count(*) counta from book_index group by book_id) a right join (select book_id,count(*) countb from book_index where index_name is null group by book_id) b on a.book_id = b.book_id
where b.countb/a.counta > 0.05);
END

3.创建定时任务

从设置时间开始，每天执行一次

CREATE EVENT course_deleteBookNullData
ON SCHEDULE EVERY 1 DAY STARTS '2021-03-04 23:59:59'
ON COMPLETION PRESERVE
ENABLE
DO
call deleteBookNullData();

4.关闭事件任务

alter event course_deleteBookNullData ON
COMPLETION PRESERVE DISABLE;

5.开启事件任务

alter event course_deleteBookNullData ON
COMPLETION PRESERVE ENABLE;