MySQL 存儲過程 + Event 事件：實現定時自動清理歷史資料

三步走部署流程

1. 建立存儲過程（封裝刪除邏輯）。
2. 建立 Event 事件（定義排程、呼叫過程）。
3. 啟用 Event Scheduler 並開啟事件。

1. 存儲過程：參數化保留天數

DELIMITER //
CREATE PROCEDURE `del_data`(IN `date_inter` INT)
BEGIN
    -- 刪除 t_xxx 表 7 天前資料
    DELETE FROM `t_xxx` WHERE `date` < DATE_SUB(CURDATE(), INTERVAL 7 DAY);
    -- 刪除 t_log 表參數指定天數前資料
    DELETE FROM `t_log` WHERE `date` < DATE_SUB(CURDATE(), INTERVAL `date_inter` DAY);
END//
DELIMITER ;

2. Event 事件：每天凌晨 3 點執行

CREATE EVENT `del_event`
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 03:00:00'
ON COMPLETION PRESERVE
ENABLE
DO CALL `del_data`(30);  -- 保留 30 天日誌

3. 事件生命周期管理

-- 查看事件狀態
SHOW EVENTS;

-- 暫停事件
ALTER EVENT `del_event` ON COMPLETION PRESERVE DISABLE;

-- 啟用事件
ALTER EVENT `del_event` ON COMPLETION PRESERVE ENABLE;

-- 檢查 Event Scheduler 是否開啟
SHOW VARIABLES LIKE '%sche%';
-- 啟用 Scheduler（動態生效，重啟失效需寫入 my.cnf）
SET GLOBAL event_scheduler = ON;

進階：單一 Event 內嵌邏輯（不推薦生產）

DELIMITER |
CREATE EVENT `e_daily`
ON SCHEDULE EVERY 1 DAY
COMMENT '每日彙總並清理 session 表'
DO BEGIN
    INSERT INTO `site_activity`.`totals` (`time`, `total`)
    SELECT CURRENT_TIMESTAMP, COUNT(*) FROM `site_activity`.`sessions`;
    DELETE FROM `site_activity`.`sessions`;
END |
DELIMITER ;

最佳實踐提醒

• 分批刪除： 大表單次 DELETE 易鎖表、產生巨大 Binlog。建議存儲過程內用迴圈每次 LIMIT 5000，配合 SLEEP(0.1) 降低衝擊。
• 分區表替代： MySQL 5.7+ 支援 PARTITION BY RANGE (TO_DAYS(date))，配合 ALTER TABLE ... DROP PARTITION 秒級清理，無鎖無 Binlog 膨脹。
• 監控告警： 監控 information_schema.EVENTS 的 LAST_EXECUTED，超過預期未執行即報警。