mysql定时器设置每天晚上执行 mysql定时器执行sql

转载

hackernew 2023-12-16 21:59:48

文章标签 mysql定时器设置每天晚上执行人工智能数据库操作系统计划任务 文章分类 MySQL 数据库

home198979

实战mysql存储程序与定时器

博客分类：

mysql

存储过程定时器 event procedure 实战

需求：一个庞大的日志表，现每天做定时统计一天的总数，放另一个表中，方便查看，运营。

旧方案：用脚本写好程序,用linux的crontab定时执行。

本文重点，用mysql定时器定时执行存储程序。

第一步：编写存储程序（需了解基本的存储程序的语法）

1. create procedure
2. begin
3. declare done int default
4. declare a,b,c int;  
5. declare curl cursor for select ver,date_format(time,'%Y%m%d') as dt,count(*) as count from ty.count where time>date_sub(curdate(),interval 1 day) group by
6. declare continue handler for sqlstate '02000' set
7. open
8.     repeat  
9. fetch curl into
10. not done then
11. insert into ty.daycount values (null,b,a,c);  
12. end
13. end
14. close
15. end

create procedure inproc()
begin
    declare done int default 0;
    declare a,b,c int;
    declare curl cursor for select ver,date_format(time,'%Y%m%d') as dt,count(*) as count from ty.count where time>date_sub(curdate(),interval 1 day) group by ver,dt;
    declare continue handler for sqlstate '02000' set done = 1;
    open curl;
    repeat
        fetch curl into a,b,c;
        if not done then
            insert into ty.daycount values (null,b,a,c);
        end if;
    until done end repeat;
    close curl;
end

这个存储程序主要用过了declare定义局部变量，声明curl光标，利用光标直到遍历结果集的作用。

执行这个语句之前要先

delimiter $$

delimiter $$

执行完成后再

delimiter ;

delimiter ;

用show查看是否已经成功

show procedure status like '%%';

show procedure status like '%%';

第二步：开启mysql定时器

mysql定时器设置每天晚上执行 mysql定时器执行sql_操作系统

如果不是on，就执行

set global

set global event_scheduler=1;

不需要重启mysql

mysql定时器设置每天晚上执行 mysql定时器执行sql_计划任务_02

会发现mysql多起了一个daemon进程

（注：对于我们线上环境来说，使用event时，注意在主库上开启定时器，从库上关闭定时器，event触发所有操作均会记录binlog进行主从同步，从库上开启定时器很可能造成卡库。切换主库后之后记得将新主库上的定时器打开。）

第三步：创建定时任务

语法：

CREATE EVENT 的语法如下：

CREATE EVENT

[IF NOT EXISTS] ---------------------------------------------*标注1

event_name -----------------------------------------------------*标注2

ON SCHEDULE schedule ------------------------------------*标注3

[ON COMPLETION [NOT] PRESERVE] -----------------*标注4

[ENABLE | DISABLE] ----------------------------------------*标注5

[COMMENT 'comment'] --------------------------------------*标注6

DO sql_statement -----------------------------------------------*标注7

;

标注3：ON SCHEDULE

ON SCHEDULE 计划任务，有两种设定计划任务的方式：

1. AT 时间戳，用来完成单次的计划任务。

2. EVERY 时间（单位）的数量时间单位[STARTS 时间戳] [ENDS时间戳]，用来完成重复的计划任务。

在两种计划任务中，时间戳可以是任意的TIMESTAMP 和DATETIME 数据类型，时间戳需要大于当前时间。

在重复的计划任务中，时间（单位）的数量可以是任意非空（Not Null）的整数式，时间单位是关键词：YEAR，MONTH，DAY，HOUR，MINUTE 或者SECOND。

提示: 其他的时间单位也是合法的如：QUARTER, WEEK, YEAR_MONTH,DAY_HOUR,DAY_MINUTE,DAY_SECOND,HOUR_MINUTE,HOUR_SECOND, MINUTE_SECOND，不建议使用这些不标准的时间单位。

标注4： [ON COMPLETION [NOT] PRESERVE]

ON COMPLETION参数表示"当这个事件不会再发生的时候"，即当单次计划任务执行完毕后或当重复性的计划任务执行到了ENDS阶段。而PRESERVE的作用是使事件在执行完毕后不会被Drop掉，建议使用该参数，以便于查看EVENT具体信息。

标注5：[ENABLE | DISABLE]

参数Enable和Disable表示设定事件的状态。Enable表示系统将执行这个事件。Disable表示系统不执行该事件。

可以用如下命令关闭或开启事件：

ALTER

ALTER EVENT event_name  ENABLE/DISABLE

下面是我的实例，每天凌晨一点执行

CREATE EVENT `event_call_inproc` ON SCHEDULE EVERY 1 DAY STARTS '2013-09-12 01:00:00' ON COMPLETION PRESERVE ENABLE DO begin call ty.inproc();end

CREATE EVENT `event_call_inproc` ON SCHEDULE EVERY 1 DAY STARTS '2013-09-12 01:00:00' ON COMPLETION PRESERVE ENABLE DO begin call ty.inproc();end

另外的一些例子：

1. 每天凌晨三点执行  
2. create
3. on schedule every 1 day starts date_add(date(curdate() + 1),interval 3 hour)  
4. on
5. do  
6. begin
7.     call test.warn();  
8. end
9.   
10. 每个月的一号凌晨1 点执行  
11. CREATE
12. ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH),INTERVAL 1 HOUR)  
13. ON
14. DO  
15. BEGIN
16.      CALL STAT();  
17. END
18.    
19. 每个季度一号的凌晨2点执行  
20. CREATE
21. ON SCHEDULE EVERY 1 QUARTER STARTS DATE_ADD(DATE_ADD(DATE( CONCAT(YEAR(CURDATE()),'-',ELT(QUARTER(CURDATE()),1,4,7,10),'-',1)),INTERVAL 1 QUARTER),INTERVAL 2  
22. HOUR)  
23. ON
24. DO  
25. BEGIN
26.      CALL SEASON_STAT();  
27. END
28.    
29.   
30. 每年1月1号凌晨四点执行  
31. CREATE
32. ON SCHEDULE EVERY 1 YEAR STARTS DATE_ADD(DATE(CONCAT(YEAR(CURDATE()) + 1,'-',1,'-',1)),INTERVAL 4 HOUR)  
33. ON
34. DO  
35. BEGIN
36.      CALL YEAR_STAT();  
37. END

每天凌晨三点执行
create event event_call_defer   
on schedule every 1 day starts date_add(date(curdate() + 1),interval 3 hour)
on completion preserve enable
do
begin
    call test.warn();
end

每个月的一号凌晨1 点执行
CREATE EVENT EVENT2  
ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH),INTERVAL 1 HOUR)
ON COMPLETION PRESERVE ENABLE
DO
 BEGIN
     CALL STAT();
 END
 
每个季度一号的凌晨2点执行
CREATE EVENT TOTAL_SEASON_EVENT
ON SCHEDULE EVERY 1 QUARTER STARTS DATE_ADD(DATE_ADD(DATE( CONCAT(YEAR(CURDATE()),'-',ELT(QUARTER(CURDATE()),1,4,7,10),'-',1)),INTERVAL 1 QUARTER),INTERVAL 2
HOUR)
ON COMPLETION PRESERVE ENABLE
DO
 BEGIN
     CALL SEASON_STAT();
 END
 

每年1月1号凌晨四点执行
CREATE EVENT TOTAL_YEAR_EVENT
ON SCHEDULE EVERY 1 YEAR STARTS DATE_ADD(DATE(CONCAT(YEAR(CURDATE()) + 1,'-',1,'-',1)),INTERVAL 4 HOUR)
ON COMPLETION PRESERVE ENABLE
DO
 BEGIN
     CALL YEAR_STAT();
 END

（本文注重实战，语法等知识请查询相关手册）