【SQL】mysql创建定时任务执行存储过程--20230928

1.先设定时区

https://blog.csdn.net/m0_46629123/article/details/133382375

1. 输入命令show variables like “%time_zone%”;（注意分号结尾）
2. 设置时区，输入 set global time_zone = “+8:00”; 回车,然后退出重启（一定记得重启，不然查找不到跟新的时间值）
3. select now()检查时间

2.定义储存过程

定义储存过程，以便于事件触发储存过程（原子性）

DELIMITER //
CREATE PROCEDURE season.vlookup_peopledata_csodtraining()
BEGINDECLARE startdate varchar(50);START TRANSACTION;SET startdate = date_format(date_add(NOW(),INTERVAL -1 DAY),'%Y%m%d');select startdate;DELETE FROM season.csod_data2;INSERT into season.csod_data2(site,plant,deptid,emplid,name,name_a,hire_dt,location,jobtitle_descr,officer_level_a,supervisor_id,labor_type,grade,sex,lo_title,object_type,user_lo_assigned_dt,user_lo_comp_dt,user_lo_status)SELECT a.site,a.plant,a.deptid,a.emplid,a.name,a.name_a,a.hire_dt,a.location,a.jobtitle_descr,a.officer_level_a,a.supervisor_id,a.labor_type,a.grade,a.sex,b.lo_title,b.object_type,b.user_lo_assigned_dt,b.user_lo_comp_dt,b.user_lo_statusFROM season.people_data AS a LEFT JOIN season.csod_employee_training_detail_g AS b ON (a.emplid = b.emplid)WHERE left(a.batchid,8)=startdate and left(b.batchid,8)=startdate#WHERE left(a.batchid,8)='20230926' and left(b.batchid,8)='20230926'#WHERE a.batchid = '20230926041509422' and b.batchid = '20230926092000010'#limit 30;COMMIT;rollback;
END
//
DELIMITER ;

3.创建定时任务事件

https://blog.csdn.net/YXWik/article/details/127263626

定义事件触发储存过程。

create event create_csod_data2
on schedule every 10 second
do CALL vlookup_peopledata_csodtraining();

3.1 周期

on schedule every 1 second //每秒执行1次
on schedule every 1 minute //每一分钟执行1次
on schedule every 1 day //每一天执行1次on schedule at current_timestamp()+interval 1 day //1天后执行
on schedule at current_timestamp()+interval 10 minute //10分钟后执行
on schedule at '2022-10-01 20:00:00' //在2022年10月1日，晚上20点执行on schedule every 1 day starts current_timestamp()+interval 1 day ends current_timestamp()+interval 1 month 
//1天后开始每天都执行执行到下个月底on schedule every 1 day ends current_timestamp()+interval 5 day 
//从现在起每天执行，执行5天ON SCHEDULE EVERY 1 DAY STARTS '2022-10-12 00:00:00'
// 从2022年10月12号0点执行，每天执行

手动调整周期

4.开启事件权限

show variables like '%sche%' ;

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

set global event_scheduler=1 ;

5.开启/暂停事件

alter event create_csod_data2 on completion preserve enable; //开启定时任务
alter event create_csod_data2 on completion preserve disable;//关闭定时任务

6.等待时间执行事件