MySQL 创建定时器

项目中一般会用 Linux Crontab 来执行定时任务,若是要执行的事件只是数据库操作的话,不妨尝试使用 MySQL 的 Event Scheduler(事件调度器)配合 Stored Procedure(存储过程)来实现。

下面是一个简单的实例。需要注意的是 MySQL 5.1.0 开始才支持 Event Scheduler。

一、开启 Event Scheduler

下面三条语句都可以查看 Event Scheduler 是否开启,默认是关闭的。

1
2
3
SHOW VARIABLES LIKE 'event_scheduler';
SELECT @@event_scheduler;
SHOW PROCESSLIST;

下面四条语句都可以临时开启 Event Scheduler,但是不建议临时开启。

1
2
3
4
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;
SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;

建议通过修改配置文件永久开启 Event Scheduler。
my.cnf 中找到 [mysqld],添加配置:

1
event_scheduler = ON

然后重启 MySQL service mysqld restart
接下来就可以开始写事件了。

二、创建 Event Scheduler

1
2
3
4
5
6
7
8
DELIMITER $$
CREATE EVENT IF NOT EXISTS update_order_status
ON SCHEDULE EVERY 5 minute
ON COMPLETION PRESERVE
DO BEGIN
CALL UpdateOrderStatus();
END$$
DELIMITER ;

上面创建了一个名为 update_order_status 的事件,每五分钟执行一次 CALL UpdateOrderStatus();
也就是每五分钟调用一次 UpdateOrderStatus() 这个存储过程。

创建成功后,下面语句可以查看已创建的事件:

1
SELECT * FROM mysql.event;

当然也可以删除不需要的事件:

1
DROP EVENT update_order_status;

三、创建存储过程

这里我们需要做的就是把上面提到的 UpdateOrderStatus() 这个存储过程实现。

注意,这里的操作是对应具体某个数据库的,所以需要进入到目标数据库中创建存储过程

现在假设要操作数据库里的 tbl_order 表,我想让数据库查询到 status 为 2 而且 finish_time 小于当前时间 的订单,将它的 status 改为 3。
这是一个典型的 已付款订单超期未点已完成,自动完成订单 的需求。

编写如下 SQL 语句,并执行:

1
2
3
4
5
6
7
DELIMITER $$
DROP PROCEDURE if exists UpdateOrderStatus$$
CREATE PROCEDURE UpdateOrderStatus()
BEGIN
UPDATE `tbl_order` SET status = 3 WHERE status=2 AND finish_time <= unix_timestamp();
END$$
DELIMITER ;

同样的,创建成功后,下面语句可以查看到创建的储存过程:

1
SELECT * FROM mysql.proc;

但是这里面看不到前面写的 SQL 语句,可以这样查看:

1
SHOW CREATE PROCEDURE UpdateOrderStatus

同样如果要删除这个存储过程,可以执行:

1
DROP PROCEDURE UpdateOrderStatus;
好了,现在数据库会每五分钟为我们执行一次 UpdateOrderStatus() 存储过程。