实战mysql存储程序与定时器

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

 

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

 

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

 

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

 

Sql代码  

1. create procedure inproc()  
2. begin  
3.     declare done int default 0;  
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 ver,dt;  
6.     declare continue handler for sqlstate '02000' set done = 1;  
7.     open curl;  
8.     repeat  
9.         fetch curl into a,b,c;  
10.         if not done then  
11.             insert into ty.daycount values (null,b,a,c);  
12.         end if;  
13.     until done end repeat;  
14.     close curl;  
15. end  

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

 

 

执行这个语句之前要先

Sql代码  

1. delimiter $$  

执行完成后再

 

 

Sql代码  

1. delimiter ;  

 

用show查看是否已经成功

 

Sql代码  

1. show procedure status like '%%';  

 

 

第二步：开启mysql定时器

如果不是on，就执行

 

Sql代码  

1. set global event_scheduler=1;  

不需要重启mysql

 

会发现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表示系统不执行该事件。

 

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

Sql代码  

1. ALTER EVENT event_name  ENABLE/DISABLE  

 

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

Sql代码  

1. 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  

 

另外的一些例子：

Sql代码  

1. 每天凌晨三点执行  
2. create event event_call_defer     
3. on schedule every 1 day starts date_add(date(curdate() + 1),interval 3 hour)  
4. on completion preserve enable  
5. do  
6. begin  
7.     call test.warn();  
8. end  
9.   
10. 每个月的一号凌晨1 点执行  
11. CREATE EVENT EVENT2    
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 COMPLETION PRESERVE ENABLE  
14. DO  
15.  BEGIN  
16.      CALL STAT();  
17.  END  
18.    
19. 每个季度一号的凌晨2点执行  
20. CREATE EVENT TOTAL_SEASON_EVENT  
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 COMPLETION PRESERVE ENABLE  
24. DO  
25.  BEGIN  
26.      CALL SEASON_STAT();  
27.  END  
28.    
29.   
30. 每年1月1号凌晨四点执行  
31. CREATE EVENT TOTAL_YEAR_EVENT  
32. ON SCHEDULE EVERY 1 YEAR STARTS DATE_ADD(DATE(CONCAT(YEAR(CURDATE()) + 1,'-',1,'-',1)),INTERVAL 4 HOUR)  
33. ON COMPLETION PRESERVE ENABLE  
34. DO  
35.  BEGIN  
36.      CALL YEAR_STAT();  
37.  END