MySql定时执行SQL

好久没写博客了，因为没啥可写，感觉自己对技术的追求已经没有那么强烈了。今天总结一下mysql自带的定时计划。

首先

show variables like '%event_sche%';

查询定时策略是否开启，如果是关闭的话，需要先开启哦。
执行语句即可：

set global event_scheduler=1;

1. 创建存储过程 下述案例创建了 名为test_proce的存储过程函数，方便创建事件，事件直接调用该存储过程。

use miaosuyun;delimiter//createprocedure test_proce()beginUPDATE order_course a
join(SELECT
                B.*FROM(SELECT
                    ROW_NUMBER()over(PARTITIONBY a.id )AS row_num,A.*FROM
                    order_course A 
                WHERE
                    A.course_username IN(SELECT
                        course_username 
                    FROM
                        order_course 
                    WHERE
                        course_status IN('2','4','12')GROUPBY
                        course_username 
                    HAVINGSUM(CASEWHEN course_status ='4'THEN1ELSE0END)=0ANDSUM(CASEWHEN course_status ='12'THEN1ELSE0END)=0ANDSUM(CASEWHEN course_status ='2'THEN1ELSE0END)>0)AND A.course_status ='2') B
            WHERE
            B.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(),INTERVAL10MINUTE)) b on a.id = b.id
set a.course_status ='1',a.error_msg=null,a.cookie=null,a.watch_finish_rate=0;end//delimiter;

1. 创建事件

-- 删除事件drop event second_event
-- 创建事件 并 每隔300秒调用一次 create event second_event
on schedule every 300seconddocall test_proce();

常用定时执行计划

-- 表示创建后并不开始生效on completion preserve disableon schedule every 1second//每秒执行1次on schedule every 2minute//每两分钟执行1次on schedule every 3day//每3天执行1次on schedule at current_timestamp()+interval5day//5天后执行on schedule at current_timestamp()+interval10minute//10分钟后执行on schedule at '2016-10-01 21:50:00'//在2016年10月1日，晚上9点50执行on schedule every 1day starts current_timestamp()+interval5day ends current_timestamp()+interval1month//5天后开始每天都执行执行到下个月底on schedule every 1day ends current_timestamp()+interval5day//从现在起每天执行，执行5天

1. 开启/关闭 任务

event second_event
alter event second_event on completion preserve enable;
event second_event
alter event second_event on completion preserve disable;

1. 查询事件

SELECT event_name,event_definition,interval_value,interval_field,status,last_executed FROM information_schema.EVENTS;