您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL Event 定時任務全攻略：從概念、語法到生產級主從同步避坑指南

編輯：MySQL 資料庫相關知識

MySQL 5.1+ 引入 Event（事件調度器），提供資料庫層面的定時任務能力，類似 Linux crontab 但整合在事務與複製鏈路中。本文系統講解核心概念、權限模型、完整語法、執行邏輯、主從同步關鍵避坑與實戰範例。

核心概念與適用場景

本質：時間觸發器，單獨執行 SQL 或調用存儲過程。
優勢：無需外部調度器、受事務控制、Binlog 記錄可主從同步。
適用：定時建表/分表、數據歸檔/清理、統計聚合刷新、緩存預熱。
不適用：高併發業務邏輯、需毫秒級精度、跨資料庫協調任務。

權限模型

建議：由具備完整權限的專用運維帳號（或 root）統一創建維護，業務帳號僅保留 EXECUTE。

完整語法詳解

CREATE EVENT [IF NOT EXISTS] event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT 'comment']
DO sql_statement;

關鍵子句深度解析

操作	所需權限	存儲位置
CREATE EVENT	EVENT 權限	mysql.user / mysql.db 的 Event_priv
ALTER/DROP EVENT	EVENT 權限	同上
調用存儲過程	EXECUTE 權限	proc 表
執行 SQL 語句	對應語句權限（INSERT/UPDATE/DROP 等）	同上

子句	說明	最佳實踐
`IF NOT EXISTS`	同名不存在才創建	建議不使用，失敗暴露衝突優於靜默忽略
`event_name`	庫級唯一，≤64 字節	命名規範：<動作>_<對象>_<周期>，如 `archive_orders_day`
`ON SCHEDULE`	核心調度定義	見下文兩種模式
`ON COMPLETION PRESERVE`	執行完不自動刪除	強烈建議加上，便於審計、調試、手動清理
`ENABLE/DISABLE`	創建即啟用/禁用	生產環境建議 `DISABLE` 創建，驗證後 `ALTER EVENT ... ENABLE`
`COMMENT`	備註，存入 information_schema.events	記錄業務擁有者、Jira 鏈接、預期效果
`DO sql_statement`	執行體，可為複合語句	用 `BEGIN ... END` 包裹多語句；禁用 LOCK/UNLOCK/CREATE EVENT 等

ON SCHEDULE 兩大模式

-- 1. 單次執行：AT timestamp
ON SCHEDULE AT '2023-12-31 23:59:59'

-- 2. 循環執行：EVERY interval [STARTS timestamp] [ENDS timestamp]
ON SCHEDULE EVERY 1 DAY STARTS '2023-01-01 01:00:00'
ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(DATE_SUB(CURDATE(), INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH) + INTERVAL 1 HOUR
-- 推薦時間單位：YEAR/MONTH/DAY/HOUR/MINUTE/SECOND

調度器開關：全局總閘

-- 查看狀態
SHOW VARIABLES LIKE 'event_scheduler';
-- OFF / ON / DISABLED

-- 開啟（動態生效，重啟失效）
SET GLOBAL event_scheduler = ON;  -- 或 1

-- 永久生效：my.cnf [mysqld]
event_scheduler=ON

主從關鍵避坑：

主庫：ON（產生 Binlog 事件）
從庫：OFF（防止雙重執行導致主鍵衝突、數據膨脹）
故障切換時：新主庫必須手動/腳本開啟 event_scheduler。

執行邏輯（簡化版）

FOR each event:
  IF event.status != 'DISABLED'
     AND NOW() < event.ends
     AND NOW() >= event.starts
     AND (NOW() - event.last_executed) >= event.interval
     AND event not currently running
  THEN
     CREATE new thread
     EXECUTE event.sql_statement
     -- 線程執行完自動銷毀
     UPDATE event.last_executed = NOW()
  END IF

管理命令速查

-- 查看事件
SHOW EVENTS;
SHOW EVENTS FROM db_name;
SELECT * FROM information_schema.events;

-- 修改事件
ALTER EVENT event_name
  ON SCHEDULE EVERY 1 HOUR
  ENABLE
  COMMENT 'updated';

-- 刪除事件
DROP EVENT [IF EXISTS] event_name;

-- 啟用/禁用
ALTER EVENT event_name ENABLE;
ALTER EVENT event_name DISABLE;

實戰範例

每秒調用存儲過程（高頻慎用）

CREATE EVENT e_test
ON SCHEDULE EVERY 1 SECOND
ON COMPLETION PRESERVE
DO CALL e_test();

每月 1 號凌晨 1 點自動建表（含動態表名）

CREATE EVENT create_monthly_tables
ON SCHEDULE EVERY 1 MONTH
STARTS DATE_ADD(DATE_SUB(CURDATE(), INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH + 1 HOUR)
ON COMPLETION PRESERVE ENABLE
DO
BEGIN
    SET @next_month = DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y%m');
    SET @sql = CONCAT('CREATE TABLE IF NOT EXISTS orders_', @next_month, ' LIKE orders_template');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

生產級檢查清單

主庫 ON，從庫 OFF——納入主從切換 SOP。
所有事件加 ON COMPLETION PRESERVE——保留歷史便於審計。
命名規範 + COMMENT 完整——無註釋事件 = 影子資產。
定期巡檢 information_schema.events——發現 DISABLED、LAST_EXECUTED 異常、EXECUTION_COUNT=0 的事件。
長耗時事件評估併發影響——必要時拆分為多批次或遷移至外部調度平台。
備份包含 mysql.event 表——避免遷移/恢復丟失定時任務。