好久没写博客了,因为没啥可写,感觉自己对技术的追求已经没有那么强烈了。今天总结一下Mysql自带的定时计划。 首先show variables like '%event_sche%';查询定时策略是否开启,如果是关闭的话,需要先开启哦
首先show variables like '%event_sche%';
查询定时策略是否开启,如果是关闭的话,需要先开启哦。
执行语句即可:set global event_scheduler=1;
test_proce
的存储过程函数,方便创建事件,事件直接调用该存储过程。use miaosuyun;delimiter //create procedure test_proce()beginUPDATE order_course ajoin (SELECTB.* FROM(SELECTROW_NUMBER() over ( PARTITioN BY a.id ) AS row_num,A.* FROMorder_course A WHEREA.course_username IN (SELECTcourse_username FROMorder_course WHEREcourse_status IN ( '2', '4' ,'12') GROUP BYcourse_username HAVINGSUM( CASE WHEN course_status = '4' THEN 1 ELSE 0 END ) = 0 AND SUM( CASE WHEN course_status = '12' THEN 1 ELSE 0 END ) = 0 AND SUM( CASE WHEN course_status = '2' THEN 1 ELSE 0 END ) > 0 ) AND A.course_status = '2' ) BWHEREB.row_num = 1 ) b on a.id = b.id set a.course_status = '1';update order_course a join (select *from order_course a where a.course_status in ('3','4') and a.recent_update_time <= DATE_SUB(NOW(), INTERVAL 10 MINUTE)) b on a.id = b.idset a.course_status = '1',a.error_msg= null,a.cookie=null,a.watch_finish_rate=0;end//delimiter ;
-- 删除事件drop event second_event-- 创建事件 并 每隔300秒调用一次 create event second_eventon schedule every 300 seconddo call test_proce();
常用定时执行计划
-- 表示创建后并不开始生效on completion preserve disableon schedule every 1 second //每秒执行1次on schedule every 2 minute //每两分钟执行1次on schedule every 3 day //每3天执行1次on schedule at current_timestamp()+interval 5 day //5天后执行on schedule at current_timestamp()+interval 10 minute //10分钟后执行on schedule at '2016-10-01 21:50:00' //在2016年10月1日,晚上9点50执行on schedule every 1 day starts current_timestamp()+interval 5 day ends current_timestamp()+interval 1 month //5天后开始每天都执行执行到下个月底on schedule every 1 day ends current_timestamp()+interval 5 day //从现在起每天执行,执行5天
event second_eventalter event second_event on completion preserve enable;event second_eventalter event second_event on completion preserve disable;
SELECT event_name,event_definition,interval_value,interval_field,status,last_executed FROM infORMation_schema.EVENTS;
来源地址:https://blog.csdn.net/huijiaaa1/article/details/129631181
--结束END--
本文标题: MySql定时执行SQL
本文链接: https://www.lsjlt.com/news/374776.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0