【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()
BEGIN
   
   DECLARE 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_status
	FROM 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 ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66

3.创建定时任务事件

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

定义事件触发储存过程。

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

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点执行，每天执行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

手动调整周期

4.开启事件权限

show variables like '%sche%' ;
1

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

set global event_scheduler=1 ;

1
2

5.开启/暂停事件

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

6.等待时间执行事件

7.（经验）如果事件不执行，则删掉旧事件，重新添加同样的新事件。