MySQL 事件排程器實作：定時更新表欄位狀態 (存儲過程 + EVENT)

場景

兩表 db_count (id, count) 與 db_date (id, t_date, count)。每秒檢查 db_date.t_date 是否早於當前時間，若是將對應 db_count.count 歸零。

完整實作代碼

DELIMITER //

-- 1. 存儲過程：核心邏輯
CREATE PROCEDURE update_count()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur_date DATETIME;
    DECLARE cur_id INT;
    DECLARE cur CURSOR FOR SELECT id, t_date FROM db_date;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO cur_id, cur_date;
        IF done THEN LEAVE read_loop; END IF;

        IF cur_date < NOW() THEN
            UPDATE db_count SET count = 0 WHERE id = cur_id;
        END IF;
    END LOOP;
    CLOSE cur;
END //

-- 2. 事件：每秒觸發
CREATE EVENT my_count
ON SCHEDULE EVERY 1 SECOND
DO
    CALL update_count(); //

DELIMITER ;

-- 3. 啟用事件排程器 (全域)
SET GLOBAL event_scheduler = ON;
-- 永久生效 my.cnf: event_scheduler = ON

關鍵優化建議

1. 避免游標：單條 UPDATE 即可完成，效能提升數量級：

   UPDATE db_count c
   JOIN db_date d ON c.id = d.id
   SET c.count = 0
   WHERE d.t_date < NOW();

2. 頻率評估：每秒執行對系統負載極大，建議改為 EVERY 1 MINUTE 或業務允許的最低頻率
3. 索引支援：db_date (t_date, id) 複合索引加速篩選
4. 事務包裝：若需原子性，存儲過程中加 START TRANSACTION; ... COMMIT;
5. 監控：SHOW EVENTS;、SELECT * FROM information_schema.EVENTS;、錯誤日誌

事件排程器狀態管理

SHOW VARIABLES LIKE 'event_scheduler';  -- ON/OFF/DISABLED
SET GLOBAL event_scheduler = ON;        -- 動態啟用
ALTER EVENT my_count DISABLE;           -- 暫停特定事件
ALTER EVENT my_count ENABLE;            -- 恢復
DROP EVENT my_count;                    -- 刪除

版本支援

• MySQL 5.1.6+ 正式支援 Event Scheduler
• 需 event_scheduler=ON (預設 OFF)
• 權限：EVENT 權限建立/修改/刪除事件