1.先设定时区
https://blog.csdn.net/m0_46629123/article/details/133382375
- 输入命令show variables like “%time_zone%”;(注意分号结尾)
- 设置时区,输入 set global time_zone = “+8:00”; 回车,然后退出重启(一定记得重启,不然查找不到跟新的时间值)
- 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;//关闭定时任务