实现mysql定时任务的方法（二）

前言：在项目中碰到需要实行mysql定时任务，在查询资料和自己实验后暂时发现有两种可行的方法，本文先介绍第二种方法——利用mysql的event实现定时任务功能。

Event Scheduler简介

事件（event）是MySQL在相应的时刻调用的过程式数据库对象，它由一个特定的线程来管理的，也就是所谓的事件调度器（event scheduler）。而事件调度器是在MySQLv5.1.6中新增的一个功能，它相当于一个定时器，可以在指定的时间点执行一条SQL语句或一个语句块，也可以用于在固定间隔重复执行。

具体实现步骤

1.确认mysql事件调度器是否开启：

mysql> show global variables like '%event%';
+---------------------------------------------------+-------+
| Variable_name                                     | Value |
+---------------------------------------------------+-------+
| event_scheduler                                   | OFF   |
| performance_schema_events_waits_history_long_size | 10000 |
| performance_schema_events_waits_history_size      | 10    |
+---------------------------------------------------+-------+
3 rows in set (0.00 sec)

2.如果显示为OFF则可以通过以下方式开启：

set global event_scheduler=on;

需要注意的是这种开启方式在mysql服务重启后，又会重置为OFF，所以我们可以在mysql配置文件（Linux系统路径为/etc/my.cnf，windows系统一般是安装目录下面的my-default.ini文件）添加如下一行来保证默认开启事件调度器：

event_scheduler=1

3.创建event要调用的存储过程message_clear，该存储过程主要完成的清除history.message表的数据：

delimiter //
drop procedure if exists history.message_clear//
create procedure history.message_clear()
begin
truncate table history.message;
end//
delimiter ;

4.创建事件message_clear_event，该event每隔30天就会去调用存储过程message_clear：

delimiter //
drop event if exists history.message_clear_event;
create event history.message_clear_event
on schedule every 30 day starts NOW()
on completion preserve disable
do call history.message_clear();//
delimiter ;

5.开启事件message_clear_event：

alter event message_clear_eventon completion preserve enable;

6.查看创建事件的信息：

mysql> select * from mysql.event\G
*************************** 1. row ***************************
                  db: mnf
                name: message_clear_event
                body: call history.message_clear()
             definer: root@localhost
          execute_at: NULL
      interval_value: 30
      interval_field: DAY
             created: 2017-09-21 16:03:29
            modified: 2017-09-21 16:03:29
       last_executed: NULL
              starts: 2017-09-21 08:03:29
                ends: NULL
              status: ENABLED
       on_completion: PRESERVE
            sql_mode:
             comment:
          originator: 1
           time_zone: SYSTEM
character_set_client: utf8
collation_connection: utf8_general_ci
        db_collation: latin1_swedish_ci
           body_utf8: call history.message_clear()

事件调度记录

从上面查询event信息来看，我们只能知道这个event什么时候执行过，但是并不知道是否执行成功。为了记录事件调度的历史，可以额外创建一个event history表来作记录：

create table `history`.`event_history` (  
  `dbname` varchar(128) not null default '',  
  `eventname` varchar(128) not null default '',  
  `starttime` datetime not null default '0000-00-00 00:00:00',  
  `endtime` datetime default null,  
  `issuccess` int(11) default null,  
  `duration` int(11) default null,  
  `errormessage` varchar(512) default null,  
  `randno` int(11) default null,  
  primary key (`dbname`,`eventname`,`starttime`),  
  key `ix_endtime` (`endtime`),  
  key `ix_starttime_randno` (`starttime`,`randno`)  
) engine=innodb default charset=utf8; 

接着将event修改成如下：

delimiter //  
create definer=`root`@`localhost` event `message_clear_event` on schedule   
#修改以下调度信息  
every 30 day starts now() on completion preserve disable do   
begin  
    declare r_code char(5) default '00000';  
    declare r_msg text;  
    declare v_error integer;  
    declare v_starttime datetime default now();  
    declare v_randno integer default floor(rand()*100001);  

    insert into history.event_history (dbname,eventname,starttime,randno)   
    #修改下面的作业名（该作业的名称）  
    values(database(),'message_clear_event', v_starttime,v_randno);    

    begin  
        #异常处理段  
        declare continue handler for sqlexception    
        begin  
            set  v_error = 1;  
            get diagnostics condition 1 r_code = returned_sqlstate , r_msg = message_text;  
        end;  

        #此处为实际调用的用户程序过程  
        call history.message_clear()  
    end;  

    update history.event_history set endtime=now(),issuccess=isnull(v_error),duration=timestampdiff(second,starttime,now()), errormessage=concat('error=',r_code,', message=',r_msg),randno=null where starttime=v_starttime and randno=v_randno;  

end//  
delimiter ;  

通过往event_history表插入数据，我们能知道每次event什么时候执行，执行了多久以及是否有错误。但是需要注意的是get diagnostics这个功能只有5.6以后的版本才能使用，之前的版本我们可以通过其他方式来设置sql异常处理，具体可以参考： MySQL存储过程中的错误处理。

主从库开启定时器注意事项

在另外一篇文章MySQL定时器Events中看到有如下注意事项：“对于我们线上环境来说，使用event时，注意在主库上开启定时器，从库上关闭定时器，event触发所有操作均会记录binlog进行主从同步，从库上开启定时器很可能造成卡库。切换主库后之后记得将新主库上的定时器打开。”
虽然目前在实际开发中还未遇到这种情况，但是先记录在这里以防以后遇到。

两种实现mysql定时任务方法对比

在前一篇文章实现mysql定时任务的方法（一）介绍了实现mysql定时任务的另一种方式，在使用过两种方法之后有如下对比结果：

1. crontab方式任务调度周期只能精准到分钟，而event方式可以精准到秒；
2. crontab方式可以通过直接运行crontab文件的方式调用任务，而event方式不能手动调用，只能定时触发；
3. crontab方式不能执行太过复杂的存储过程，只能执行比较简单的数据库定时任务，而event方式不再依赖外部命令，能直接使用数据库本身提供的功能；
4. crontab方式能提供的日志记录比较简单，而event可以较详细的记录每次任务调度时的错误，缺点是需要额外建一个表。