欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

实战mysql存储程序与定时器_MySQL

程序员文章站 2022-06-09 21:34:09
...
bitsCN.com

实战mysql存储程序与定时器

存储过程定时器eventprocedure实战

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

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

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

第一步:编写存储程序(需了解基本的存储程序的语法)

Sql代码

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光标,利用光标直到遍历结果集的作用。


执行这个语句之前要先

Sql代码

delimiter $$

执行完成后再

Sql代码

delimiter ;


用show查看是否已经成功

Sql代码

show procedure status like '%%';



第二步:开启mysql定时器
实战mysql存储程序与定时器_MySQL
如果不是on,就执行

Sql代码

set global event_scheduler=1;

不需要重启mysql

实战mysql存储程序与定时器_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代码

ALTER EVENT event_name ENABLE/DISABLE


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

Sql代码

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 点执行

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

bitsCN.com