萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 事件排程器實現定時執行預存程序:開啟、建立、驗證全流程

利用 MySQL 原生 Event Scheduler 替代外部 Crontab,實現資料庫層面的定時任務 (如每日統計彙總)。

1. 開啟事件排程器

-- 任意一種方式即可,持久化請寫入 my.cnf [mysqld] event_scheduler=ON
SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = 1;

驗證:SHOW VARIABLES LIKE 'event_scheduler'; → ON。

2. 建立預存程序 (每日統計範例)

DELIMITER $$
CREATE PROCEDURE `Get_Info_Every_Day`()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE v_status VARCHAR(40);
  DECLARE v_duedate, v_closedate DATETIME;
  DECLARE v_overdue INT;

  DECLARE cur CURSOR FOR
    SELECT b.status, b.duedate, b.closedate, DATEDIFF(NOW(), b.duedate)
    FROM actionitem a
    JOIN actionitemdetail b ON a.id_actionitem = b.fk_actionitem
    WHERE a.finishdate = 0
      AND b.status <> 'forinfo'
      AND (b.actionby LIKE '%WEC%' OR b.actionby LIKE '%Consortium%');

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  -- 計數器初始化
  SET @total=@open=@overdue=@due7=@ptp=@nodue=@pending=@eps=@wci=@closed=0;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO v_status, v_duedate, v_closedate, v_overdue;
    IF done THEN LEAVE read_loop; END IF;

    IF LOWER(v_status) = 'open' THEN
      CASE
        WHEN v_overdue IS NULL THEN SET @nodue = @nodue + 1;
        WHEN v_overdue > 0 THEN SET @overdue = @overdue + 1;
        WHEN v_overdue BETWEEN -6 AND 0 THEN SET @due7 = @due7 + 1;
        ELSE SET @open = @open + 1;
      END CASE;
    ELSE
      CASE LOWER(v_status)
        WHEN 'closed' THEN SET @closed = @closed + 1;
        WHEN 'forinfo' THEN SET @ptp = @ptp + 1; -- 依業務對應
        WHEN 'pending jpmo' THEN SET @pending = @pending + 1;
        WHEN 'escalated to pcc for support' THEN SET @eps = @eps + 1;
        WHEN 'waiting for customer input' THEN SET @wci = @wci + 1;
      END CASE;
    END IF;
  END LOOP;
  CLOSE cur;

  -- 統計準時關閉數 (最近 7 天)
  SELECT COUNT(*) INTO @ptp FROM actionitemdetail
  WHERE DATEDIFF(NOW(), duedate) BETWEEN 0 AND 6
    AND status = 'Closed' AND DATEDIFF(closedate, duedate) < 0
    AND (actionby LIKE '%WEC%' OR actionby LIKE '%Consortium%');

  SET @total = @open + @overdue + @due7 + @nodue + @pending + @eps + @wci + @closed;

  INSERT INTO daily_statistic (Total, Open, overdue, DueWithin7Days, PTP, NoDueDate, PendingJPMO, EPS, WCI, Closed)
  VALUES (@total, @open, @overdue, @due7, @ptp, @nodue, @pending, @eps, @wci, @closed);
END$$
DELIMITER ;

3. 建立 Event 定時觸發

USE cddl;
DROP EVENT IF EXISTS e_statistics_daily;
CREATE EVENT e_statistics_daily
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 02:00:00'  -- 避開業務高峰
ON COMPLETION PRESERVE
DO CALL Get_Info_Every_Day();

4. 驗證與監控

-- 查看事件狀態
SHOW EVENTS FROM cddl\G
-- 查看執行歷史 (需開啟 general_log 或查詢 mysql.event 表)
SELECT * FROM daily_statistic ORDER BY id DESC LIMIT 10;
-- 監控事件執行錯誤
SHOW ERRORS;

優缺點與最佳實踐

優點缺點/風險
資料庫原生、無外部依賴單執行緒序列執行,長任務會阻塞後續事件
備份/遷移時連同事件一起攜帶無內建重試、失敗通知機制
權限控制細緻 (DEFINER/INVOKER)複雜邏輯建議移至應用層排程 (Quartz/Hangfire/K8s CronJob)

適用於「輕量、資料庫內閉環、容忍單點」的定時統計、清理、歸檔任務。

copyright © 萬盛學電腦網 all rights reserved