MySQL-event时间触发器实现到达时间自动更新状态值

简介

mysql5.1 版本开始引进 event 概念。event 既 “时间触发器”，与 TRIGGERS 的事件触发不同，event 类似与 linux crontab 计划任务，用于时间触发。通过单独或调用存储过程使用，在某一特定的时间点，触发相关的 SQL 语句或存储过程。

对于每隔一段时间就有固定需求的操作，如创建表，删除数据，更新数据等操作，可以使用 event 来处理。

基本设置

开启定时器：要使 event 起作用，MySQL 的常量 GLOBAL event_scheduler 必须为 ON 或者是 1。

# 查看是否开启定时器
SHOW VARIABLES LIKE 'event_scheduler';

# 开启定时器 0：off 1：on
SET GLOBAL event_scheduler = ON;  
SET @@global.event_scheduler = ON;  
SET GLOBAL event_scheduler = 1;  
SET @@global.event_scheduler = 1;  

使用

实现：到达时间自动更新表sp_buy_order_item状态值从3到7

定义存储过程（方法）

DELIMITER |
DROP PROCEDURE IF EXISTS update_order_item_status |
CREATE PROCEDURE update_order_item_status()
BEGIN
    IF exists(SELECT id
              FROM `folkspace-uat`.sp_buy_order_item
              WHERE status = 3 AND SYSDATE() >= confirm_receipt_ddl) THEN
        UPDATE `folkspace-uat`.sp_buy_order_item
        SET status = 7
        WHERE id IN (SELECT id
                     FROM (SELECT id
                           FROM `folkspace-uat`.sp_buy_order_item
                           WHERE status = 3 and SYSDATE() >= confirm_receipt_ddl) AS id_table)
          AND status = 3;
    END IF;
END
|

## 中间表使用原因
# MySQL环境下，先select再update会出现 ERROR 1093 (HY000): You can’t specify target table ‘message’ for update in FROM clause 这个错误。
# 而 select 的结果再通过一个中间表 select 多一次，就可以避免这个错误

存储过程多SQL

DELIMITER |
DROP PROCEDURE IF EXISTS update_return_order_status |
CREATE PROCEDURE update_return_order_status()
BEGIN
    IF exists(SELECT id
              FROM `folkspace-uat`.sp_return_order
              WHERE status = 5
                AND SYSDATE() >= confirm_receipt_ddl) THEN
        UPDATE `folkspace-uat`.sp_return_order
        SET status = 6
        WHERE status = 5
          AND SYSDATE() >= confirm_receipt_ddl;
    END IF;
    IF exists(SELECT id
              FROM `folkspace-uat`.sp_return_order
              WHERE status = 6
                AND SYSDATE() >= confirm_receipt_ddl) THEN
        UPDATE `folkspace-uat`.sp_buy_order_item
        SET status = 5
        WHERE status = 4
          AND order_item_id IN (SELECT buy_order_item_id
                                FROM `folkspace-uat`.sp_return_order
                                WHERE status = 6
                                  AND SYSDATE() >= confirm_receipt_ddl);
    END IF;
END
|

创建定时器

# 每间隔5分钟调用一次存储过程
CREATE EVENT IF NOT EXISTS event_update_order_item_status
ON SCHEDULE EVERY 300 SECOND
ON COMPLETION PRESERVE
DO CALL update_order_item_status();

启动定时器

ALTER EVENT event_update_order_item_status ON
COMPLETION PRESERVE ENABLE;

查看

查看定时器

SELECT * FROM information_schema.EVENTS;

查看存储过程

# 查看存储过程状态
SHOW PROCEDURE STATUS LIKE 'update_order_item_status';

# 查看存储过程定义
SHOW CREATE PROCEDURE update_order_item_status;

# 查看存储信息（数据表）
SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME = 'update_order_item_status';

删除

关闭事件任务

# 关闭事件任务
ALTER EVENT event_update_order_item_status ON 
COMPLETION PRESERVE DISABLE;

删除定时器

DROP EVENT IF EXISTS event_update_order_item_status;

删除存储过程

DROP PROCEDURE IF EXISTS update_order_item_status;