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

MySQL中的事件调度基础学习教程

程序员文章站 2024-02-24 10:50:22
经常需要有一些定时任务在mysql表上执行,例如统计、迁移、删除无用数据等。之前的作法是利用linux cron定时运行脚本,但是发现这样的额外依赖有时并不方便,例如单机多...

经常需要有一些定时任务在mysql表上执行,例如统计、迁移、删除无用数据等。之前的作法是利用linux cron定时运行脚本,但是发现这样的额外依赖有时并不方便,例如单机多实例部署时,就需要分别手动分别配置不同的cron任务,需要额外配置相应的用户和权限;新环境部署时容易遗漏cron任务等。

mysql提供了event scheduler,与linux下的crontab类似,可以根据时间调度来运行任务,运行一次或多次。

完整的event schduler创建语句如下:

create
  [definer = { user | current_user }]
  event
  [if not exists]
  event_name
  on schedule schedule
  [on completion [not] preserve]
  [enable | disable | disable on slave]
  [comment 'comment']
  do event_body;

schedule:
  at timestamp [+ interval interval] …
  | every interval
  [starts timestamp [+ interval interval] …]
  [ends timestamp [+ interval interval] …]

interval:
  quantity {year | quarter | month | day | hour | minute |
       week | second | year_month | day_hour | day_minute |
       day_second | hour_minute | hour_second | minute_second}

一、调度scheduler
mysql中的调度可以是只运行一次,也可以指定时间间隔重复运行。其定义是在event定义的on schedule子句中。该子句格式如下:

on schedule
at timestamp [+ interval interval] …
| every interval
  [starts timestamp [+ interval interval] …]
  [ends timestamp [+ interval interval] …]

其中,timestamp必须包括”年月日时分秒“,它参与表达式计算后,结果是datetime或者timestamp类型。

而时间间隔interval可以如下:

<数字> {year | quarter | month | day | hour | minute |
      week | second | year_month | day_hour | day_minute |
      day_second | hour_minute | hour_second | minute_second}

其含义很清晰,如year 年;quarter 季度;year_month 年+月;minute_second 分钟+秒。

补充:

year | quarter | month | year_month 后台都转换成month,其他时间间隔都转换成second
on schedule中的时间使用创建时本会话中的时区信息time_zone,这个时区默认是服务端的全局time_zone,也可能后续手动更新掉。这些时间会转化成utc时间,存储到mysql.event表中。
1.一次运行
at直接指定时间,或者使用时间表达式计算得出确定的时间点。

示例:

at '2006-02-10 23:59:00′   指定确切运行时间,本地时区。
at current_timestamp + interval '1:15′ minute_second  指定1分15秒后运行。
2.多次运行
every设置运行的时间间隔,这里不能再指定[+ interval interval]。

指定starts、ends是可选的。

starts是指定重复运行的第一次是什么时候。不指定的情况下,会在事件创建时运行第一次,即等价于starts current_timestamp!
ends告知mysql结束重复运行的时间点。不指定的情况下,mysql会永远重复运行下去。
示例:

every 5 week  每5周运行一次,创建时运行第一次。
every 3 day starts '2013-12-4 09:10:00′  从'2013-12-4 09:10:00′开始运行第一次,每隔3天运行一次。
every 2 month starts current_timestamp + interval 10 minute ends '2014-12-31 23:59:59′ 10分钟后开始到2014年底,每两个月运行一次。
二、事件event
1.启用event scheduler功能
event是由一个特定的event scheduler线程执行的,运行过程中可以通过show full processlist查看其当前状态信息,如:

7384313     event_scheduler     localhost     [null]     daemon     3     waiting on empty queue     [null]

默认事件调度event scheduler功能是未启用的,需要配置全局参数event_scheduler,本参数可以动态设置,即时生效。

event_scheduler有如下三种取值:

off/0 关闭,默认值。不运行event scheduler线程,也就无法进行事件调度。设置为on可以立即启用。
on/1 启用。
disabled 禁用。同样不运行event scheduler线程。只有在mysql服务启动时设置才有用。当event_scheduler是on或者off时,不能在运行时设置event_scheduler为disabled。如果启动时配置了event-scheduler=disabled,则运行时就不能设置为on/off。换句话中,可以在mysql服务启动时设置为disabled,然后完全禁用了event_scheduler,不能动态调整。
所以,要启用event_scheduler,运行时执行:

set global event_scheduler=on

要随mysql服务一起启用,则在/etc/my.cnf中添加

[mysqld]
event-scheduler=on

2.创建事件的语法

create
  [definer = { user | current_user }]
  event
  [if not exists]
  event_name
  on schedule schedule
  [on completion [not] preserve]
  [enable | disable | disable on slave]
  [comment 'comment']
  do event_body;
 
schedule:
  at timestamp [+ interval interval] ...
   | every interval
  [starts timestamp [+ interval interval] ...]
  [ends timestamp [+ interval interval] ...]
interval:
 quantity {year | quarter | month | day | hour | minute |
       week | second | year_month | day_hour |
day_minute |day_second | hour_minute |
hour_second | minute_second}

参数详细说明:
definer: 定义事件执行的时候检查权限的用户。
on schedule schedule: 定义执行的时间和时间间隔。
on completion [not] preserve: 定义事件是一次执行还是永久执行,默认为一次执行,即not preserve。
enable | disable | disable on slave: 定义事件创建以后是开启还是关闭,以及在从上关闭。如果是从服务器自动同步主上的创建事件的语句的话,会自动加上disable on slave。
comment 'comment': 定义事件的注释。
 
3.更改事件的语法

alter
  [definer = { user | current_user }]
  event event_name
  [on schedule schedule]
  [on completion [not] preserve]
  [rename to new_event_name]
  [enable | disable | disable on slave]
  [comment 'comment']
  [do event_body]

4.删除事件的语法

drop event [if exists] event_name

5.do子句
在do子句中实现事件的具体逻辑,几乎所有可以在存储程序中运行的mysql语句都可以在event中使用。

1)简单sql示例:

create event e_hourly
  on schedule
   every 1 hour
  comment ‘clears out sessions table each hour.'
  do
   delete from site_activity.sessions;

2)复杂sql示例:

delimiter |
create event e
  on schedule
   every 5 second
  do
   begin
    declare v integer;
    declare continue handler for sqlexception begin end;
    set v = 0;
    while v < 5 do
     insert into t1 values (0);
     update t2 set s1 = s1 + 1;
     set v = v + 1;
    end while;
  end |
delimiter ;

3)do子句中sql的限制

基本上do中可以使用任何在存储程序(stored routine)中允许的sql语句,而存储程序中有些限制,event还有些额外的限制。

stored routine中如下语句不允许:

  • lock tables/unlock tables
  • load data与load table

支持动态sql(prepare, execute, deaalocate prepare)!但是prepare本身有些语句不允许执行。

insert delayed不会生效
event的限制:

如果do子句中包含alter event子句,虽然能够创建,但是运行时会出错。
不要在do子句中使用select或show这样仅仅是查询的语句,因为其输出无法从外部获取到。可以使用select … into 这样的形式将查询结果保存起来。


5.查看event
有如下方式可以查看event的信息:

mysql.event
information_schema.events
show events
show create event


三、event schedule其他注意点
mysql保存了事件创建时的sql_mode作为其运行时的sql_mode;
如果在一个调度区间内任务没有处理完成,新的调度依然会生成,这样就会出现同时又多个任务在运行的情况。如果要避免多个任务同时存在,可以使用get_lock()函数或者行锁、表锁。

四、    mysql事件实战
测试环境
创建一个用于测试的test表:

create table `test` (
 `id` int(11) not null auto_increment,
 `t1` datetime default null,
 `id2` int(11) not null default '0',
 primary key (`id`)
) engine=innodb auto_increment=106 default charset=utf8

实战1
ø  创建一个每隔3秒往test表中插入一条数据的事件,代码如下:

create event if not exists test on schedule every 3 second
on completion preserve
do insert into test(id,t1) values('',now());

ø  创建一个10分钟后清空test表数据的事件

create event if not exists test
on schedule
at current_timestamp + interval 1 minute
do truncate table test.aaa;

ø  创建一个在2012-08-23 00:00:00时刻清空test表数据的事件,代码如下:

create event if not exists test
on schedule
at timestamp '2012-08-23 00:00:00'
do truncate table test;

ø  创建一个从2012年8月22日21点45分开始到10分钟后结束,运行每隔3秒往test表中插入一条数据的事件,代码如下:

create event if not exists test on schedule every 3 second
starts '2012-08-22 21:49:00' 
ends '2012-08-22 21:49:00'+ interval 10 minute
on completion preserve
do insert into test(id,t1) values('',now());

 
 实战2
通常的应用场景是通过事件来定期的调用存储过程,下面是一个简单的示例:
创建一个让test表的id2字段每行加基数2的存储过程,存储过程代码如下:

drop procedure if exists test_add;
delimiter //
create procedure test_add()
begin
declare 1_id int default 1;
declare 1_id2 int default 0;
declare error_status int default 0;
declare datas cursor for select id from test;
declare continue handler for not found set error_status=1;
open datas;
fetch datas into 1_id;
repeat
set 1_id2=1_id2+2;
update test set id2=1_id2 where id=1_id;
fetch datas into 1_id;
until error_status
end repeat;
close datas;
end
//

事件设置2012-08-22 00:00:00时刻开始运行,每隔1调用一次存储过程,40天后结束,代码如下:

create event test on schedule every 1 day
starts '2012-08-22 00:00:00'
ends '2012-08-22 00:00:00'+interval 40 day
on completion preserve do
call test_add();