MySQL事件-定时任务

定时任务是项目中经常遇到的，除能在程序中编码实现（包括使用Windows上的定时任务，与Linux下的crontab定时任务）外，还可交由数据库实现。MySQL中的事件机制就可以完成定时任务：在指定的时间调用约定的操作。

 

1、查看是否启用了事件（返回ON/1表示开启）

SELECT @@event_scheduler; 

SHOW VARIABLES LIKE 'event_scheduler';

2、启用事件

SET GLOBAL event_scheduler=1; 

SET GLOBAL event_scheduler = ON;

为保证重启后自动启用事件：

 在配置文件中的[mysqld]部分添加 event_scheduler=ON

SET GLOBAL event_scheduler=OFF;  //关闭

4、创建事件

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>;

参数详细说明：

• DEFINER：定义事件执行的时候检查权限的用户。

• ON SCHEDULE schedule：定义执行的时间和时间间隔（见‘周期规则’部分）。

• ON COMPLETION [NOT] PRESERVE：定义事件是一次执行还是永久执行，默认为一次执行，即NOT PRESERVE。

• ENABLE | DISABLE | DISABLE ON SLAVE：定义事件创建以后是开启还是关闭，以及在从上关闭。如果是从服务器自动同步主上的创建事件的语句的话，会自动加上DISABLE ON SLAVE。

• COMMENT 'comment'：定义事件的注释。

周期规则

T timestamp [+ INTERVAL interval] ...

| EVERY interval

STARTS timestamp [+ INTERVAL interval] ...]

ENDS timestamp [+ INTERVAL interval] ...]

• 周期执行Every：单位有second、minute、hour、day、week、quarter、month、year；

    On Schedule Every 1 second  // 每秒执行1次

    On Schedule Every 5 day  // 没5天执行一次

• 某个时间执行At

    On Schedule At current_timestamp() + Interval 5 day // 5天后执行

    On Schedule At '2019-04-10 10:30:00'  // 在19年4月10号10点半执行

• 某个时段执行Starts...Ends

    On Schedule Every 1 hour Starts current_timestamp() + Interval 1 day Ends current_timestamp() + Interval 1 month  // 从5天后开始每1小时执行1次，直到一个月后停止

    On Schedule Every 1 minute Ends current_timestamp() + Interval 1 week  // 从现在起没分钟执行1次，直到1周后停止

5、修改已创建事件

Alter Event <event_name> on completion preserve enable; //开启定时任务

Alter Event <event_name> on completion preserve disable; //关闭定时任务

Drop Event [IF EXISTS] <event_name>; // 删除

6、查看已创建事件

Select event_name, event_definition, interval_value, interval_field, status FROM information_schema.EVENTS;

 

7、简单实例

创建事件调用的存储过程

delimiter $$;  // 定义结束符，避免遇到;即开始执行（要不多条语句看作整体）

create procedure testProc()

begin

  insert into testT(createTime) values(now());

end $$;

delimiter ;

创建定时任务（每分钟执行一次）：任务未启用，调用enable后启用。

create event testEvent

on schedule every 1 minute

on completion preserve disable

do call testProc();   // 通过call调用存储过程

 

创建定时删除日志的任务

CREATE DEFINER=`root`@`localhost` 

 EVENT `clearLog` 

 ON SCHEDULE EVERY 10 minute 

 ON COMPLETION PRESERVE DISABLE 

 DO delete T_userlog order by create_time limit 100