用MySQL的Event设置定时任务执行sql语句

2018-12-31 4,381 12/31

想在每天某个特定时间段定时执行一条sql语句命令,来进行数据库的备份或者更新删除等,可以直接使用MySQL自带的定时任务Event事件来执行操作。需要注意的是Event事件是在MySQL 5.1版本中新增的,还有一点就是确保你有MySQL数据库的root管理权限,因为Event默认是关闭状态需要用root权限开启 下面会给出开启方法。

查看Event事件是否开启

1、查看MySQL是否开启event,执行下面的命令。

  1. SHOW VARIABLES LIKE 'event_scheduler';  
用MySQL的Event设置定时任务执行sql语句

如果显示OFF,说明是关闭状态需要开启。

2、 开启event,使用下面的命令 。

  1. SET GLOBAL event_scheduler = 1;  

执行完后再次查看event状态,ON为开启。

用MySQL的Event设置定时任务执行sql语句

Event 事件语法说明

语法

  1. CREATE  
  2.     [DEFINER = { user | CURRENT_USER }]  
  3.     EVENT  
  4.     [IF NOT EXISTS]  
  5.     event_name  
  6.     ON SCHEDULE schedule  
  7.     [ON COMPLETION [NOT] PRESERVE]  
  8.     [ENABLE | DISABLE | DISABLE ON SLAVE]  
  9.     [COMMENT 'string']  
  10.     DO event_body;  
  11.   
  12. schedule:  
  13.     AT timestamp [+ INTERVAL interval] ...  
  14.   | EVERY interval  
  15.     [STARTS timestamp [+ INTERVAL interval] ...]  
  16.     [ENDS timestamp [+ INTERVAL interval] ...]  
  17.   
  18. interval:  
  19.     quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}  

语法说明

  • DEFINER:指定可执行该定时器的MySQL账号,user的格式是’user_name’@’host_name’,CURRENT_USER或CURRENT_USER(),注意,单引号是需要在语句中输入的。如果不指定,默认是DEFINER = CURRENT_USER。
  • event_name:事件名称,最大64个字符,不区分大小写,MyEvent和myevent是一样的,命名规则和其他MySQL对象是一样的。
  • ON SCHEDULE schedule: 指定何时执行,下文详细说明。

    [ON COMPLETION [NOT] PRESERVE]:可选,preserve是保持的意思,这里是说这个定时器第一次执行完成以后是否还需要保持,如果是NOT PRESERVE,该定时器只执行一次,完成后自动删除事件;没有NOT,该定时器会多次执行,可以理解为这个定时器是持久性的。默认是NOT PRESERVE。

    [ENABLE | DISABLE | DISABLE ON SLAVE]:可选,是否启用该事件,ENABLE-启用,DISABLE-禁用,可使用alter event语句修改该状态。DISABLE ON SLAVE是指在主备复制的数据库服务器中,在备机上也创建该定时器,但是不执行。
  • COMMENT: 注释,必须用单引号括住。
  • DO event_body:事件要执行的SQL语句,可以是一个SQL,也可以是使用BEGIN和END的复合语句,和存储过程相同。

ON SCHEDULE 事件的执行时间

ON SCHEDULE指定事件何时执行,执行的频率和执行的时间段,有AT和EVERY两种形式。

AT(在指定的时间只执行一次)

AT语法AT timestamp,用于只执行一次事件。执行的时间由timestamp指定,timestamp必须包含完整的日期和时间,即年月日时分秒都要有。可以使用DATETIME或TIMESTAMP类型,或者可以转换成时间的值,例如“2018-01-21 00:00:00”。如果指定是时间是过去的时间,该事件不会执行,并会生成警告。

可以使用CURRENT_TIMESTAMP(当前时间的意思)指定执行时间,这样的话,事件创建成功会立即执行。

如果事件执行的时间是未来的某个时间点,可以使用+ INTERVAL interval指定具体时间。interval有数字(quantity) 和 时间单位(Unit of time)两部分组成,例如:2分10秒后执行,应写为 + INTERVAL ‘2:10’,可用的时间单位有很多,列表如下图:

用MySQL的Event设置定时任务执行sql语句

AT小例子

1小时后执行该事件。创建名字为myevent的事件,ON SCHEDULE指定时间,DO要执行的sql语句

  1. CREATE    
  2.     EVENT myevent    
  3.     ON SCHEDULE    
  4.       SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR    
  5.     DO    
  6.       UPDATE myschema.mytable SET mycol = mycol + 1;    

Every(每间隔多久执行一次)

如果需要让事件定时执行,使用Every这种方式。注意Every后边没有+ INTERVAL,时间格式和单位和上面的相同。

Every小例子

EVERY 20 second每20秒执行一次

  1. CREATE    
  2.     EVENT myevent    
  3.     ON SCHEDULE    
  4.       EVERY 20 second  
  5.     DO    
  6.       UPDATE myschema.mytable SET mycol = mycol + 1;    

EVERY后面可以跟可选的STARTSENDS,指定事件开始和结束时间,在这个时间段内,时间定时执行。STARTSENDS可同时指定,或者只指定STARTS,或两者都不指定。

更多案例

  • EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + INTERVAL 1 WEEK 一周以后开始,每隔三个月
  • EVERY 2 WEEK STARTS CURRENT_TIMESTAMP + INTERVAL ‘6:15’ HOUR_MINUTE 六个小时15分钟以后,每隔两周
  • EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK 30分钟以后开始,4周后结束,每隔12个小时

例子:每天凌晨执行

use 指定名为WordPress的数据库,在每天凌晨将wp_vod数据表中的,vod_day字段值设为0

  1. use WordPress;  
  2.   
  3. CREATE    
  4.     EVENT myevent    
  5.     ON SCHEDULE    
  6.       EVERY 1 DAY STARTS '2019-01-01 00:00:00'    
  7.     DO    
  8.       update wp_vod set vod_day = 0;    

删除Event事件

语句很简单 ,后面myevent为事件名

  1. drop event myevent;  

MySQL服务器重启 断电会导致事件关闭

整个服务器重启、断电会导致event事件恢复成默认OFF关闭状态,想解决这个问题,则需要在mysql.ini文件中修改加入event_scheduler = ON;

- THE END -
最后修改:2019年1月2日
0

非特殊说明,本博所有文章均为博主原创。

共有 0 条评论