MySQL 事件調度器進階實戰：複雜業務邏輯封裝、動態 SQL、併發控制與監控運維

Event Scheduler 進階：從簡單定時到企業級定時任務平台

MySQL 5.1+ Event Scheduler 由專用線程驅動，支援「間隔執行」與「定點觸發」。配合 Stored Procedure 封裝業務，可構建輕量級定時平台，替代外部 Crontab。

事件創建進階語法

CREATE EVENT ev_name
ON SCHEDULE
  EVERY 1 DAY
  STARTS '2024-01-01 03:00:00'
  ENDS '2025-12-31 23:59:59'
ON COMPLETION PRESERVE  -- 執行後保留 (默認 DROP)
ENABLE                  -- 創建即啟用 (默認)
DO
  CALL sp_complex_business();

業務封裝最佳實踐：存儲過程 + 動態 SQL + 併發鎖

DELIMITER $$
CREATE PROCEDURE `sp_sync_stock_daily`()
BEGIN
  DECLARE v_lock INT DEFAULT 0;
  DECLARE v_num INT DEFAULT 0;
  
  -- 1. 獲取應用級鎖 (防止重疊執行)
  SELECT GET_LOCK('sp_sync_stock_daily', 10) INTO v_lock;
  IF v_lock = 0 THEN
    SELECT 'Another instance running, exit' AS msg;
    LEAVE proc_label;
  END IF;
  
  -- 2. 計算需同步數量
  SELECT (20 - COUNT(*)) INTO v_num
  FROM get_stock_list WHERE flag=0 AND stime=CURDATE();
  
  IF v_num > 0 THEN
    -- 3. 動態構建 LIMIT 參數化 SQL
    SET @sql = CONCAT(
      'INSERT INTO get_stock_list (stock_code,stock_name,close,raises,stime) ',
      'SELECT stock_code,stock_name,close,result1,stime ',
      'FROM choice_stock ',
      'WHERE stock_code NOT IN (SELECT stock_code FROM get_stock_list WHERE stime=CURDATE() AND flag=0) ',
      'AND stime=CURDATE() ORDER BY id LIMIT ', v_num
    );
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
  
  -- 4. 釋放鎖
  DO RELEASE_LOCK('sp_sync_stock_daily');
  
  proc_label:
  SELECT v_num AS synced_count;
END $$
DELIMITER ;

函數封裝複用邏輯

DELIMITER $$
CREATE FUNCTION `fn_need_sync_count`() RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
  DECLARE v_cnt INT;
  SELECT COUNT(*) INTO v_cnt FROM get_stock_list WHERE flag=0 AND stime=CURDATE();
  RETURN IF(v_cnt < 20, 20 - v_cnt, 0);
END $$
DELIMITER ;

-- 事件中調用
CREATE EVENT ev_sync_stock
ON SCHEDULE EVERY 1 DAY STARTS '03:00:00'
DO
  CALL sp_sync_stock_daily();

時區陷阱與解決

• Event 線程繼承 @@system_time_zone (OS 時區)，非 @@session.time_zone。
• 生產環境建議：OS 時區 = UTC，MySQL default-time-zone='+00:00'，應用層統一 UTC 存儲，顯示層轉換。
• 若無法改 OS 時區，在 Procedure 開頭 SET time_zone = '+08:00'; 強制會話時區。

監控與運維

-- 查看所有事件狀態
SELECT EVENT_NAME, EVENT_SCHEMA, STATUS, LAST_EXECUTED, STARTS, ENDS, INTERVAL_VALUE, INTERVAL_FIELD
FROM information_schema.EVENTS;

-- 即時執行狀態
SHOW STATUS LIKE 'Event%';

-- 手動觸發 (測試用)
CALL sp_sync_stock_daily();

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

-- 修改排程
ALTER EVENT ev_sync_stock ON SCHEDULE EVERY 6 HOUR;

避坑清單

1. 權限：創建者需 EVENT 權限；執行時以 DEFINER 權限運行 (預設創建者)，確保 DEFINER 存在且有業務表權限。
2. 長事務：Event 內大批量操作需分批提交，避免長鎖表阻塞主業務。
3. 異常處理：Procedure 內 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION ... 記錄錯誤日誌表，防止靜默失敗。
4. 備份還原：mysqldump --events 導出事件；還原後檢查 event_scheduler=ON。
5. 版本限制：5.1 單線程執行事件，5.6+ 支援 event_scheduler=ON 多線程 (實驗性)。高併發定時任務建議外部調度器 (Airflow、XXL-Job)。