利用 MySQL 原生 Event Scheduler 替代外部 Crontab,實現資料庫層面的定時任務 (如每日統計彙總)。
-- 任意一種方式即可,持久化請寫入 my.cnf [mysqld] event_scheduler=ON
SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = 1;
驗證:SHOW VARIABLES LIKE 'event_scheduler'; → ON。
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 ;
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();
-- 查看事件狀態
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) |
適用於「輕量、資料庫內閉環、容忍單點」的定時統計、清理、歸檔任務。