MySQL 存儲過程與定時事件實戰：數據歸檔自動化、事件調度器啟用與權限控制

MySQL 存儲過程 + Event 定時任務：實現數據自動歸檔清理

存儲過程：半年前用戶數據歸檔並刪除

CREATE PROCEDURE `archive_old_accounts`()
BEGIN
  DECLARE row_cnt INT DEFAULT 0;
  DECLARE file_suffix VARCHAR(20);
  DECLARE export_sql TEXT;
  
  -- 統計待歸檔行數
  SELECT COUNT(*) INTO row_cnt
  FROM `S_ACCOUNT`
  WHERE DATE_ADD(`registerTime`, INTERVAL 6 MONTH) <= NOW();
  
  IF row_cnt > 0 THEN
    -- 生成唯一文件名（Unix時間戳）
    SET file_suffix = UNIX_TIMESTAMP(NOW());
    
    -- 動態構建導出SQL (INTO OUTFILE 需 FILE 權限、secure_file_priv 目錄)
    SET export_sql = CONCAT(
      "SELECT * FROM `S_ACCOUNT` ",
      "WHERE DATE_ADD(`registerTime`, INTERVAL 6 MONTH) <= NOW() ",
      "INTO OUTFILE 'C:/backup/", file_suffix, ".txt' ",
      "FIELDS TERMINATED BY ',' ",
      "OPTIONALLY ENCLOSED BY '\"' ",
      "LINES TERMINATED BY '\\n'"
    );
    
    -- 預處理執行動態SQL
    PREPARE stmt FROM export_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    -- 刪除已歸檔數據
    DELETE FROM `S_ACCOUNT`
    WHERE DATE_ADD(`registerTime`, INTERVAL 6 MONTH) <= NOW();
    
    COMMIT;
  END IF;
END;

導出格式參數說明

創建定時事件

-- 每天凌晨2點執行
CREATE EVENT `daily_archive_event`
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 02:00:00'
ON COMPLETION NOT PRESERVE
ENABLE
DO CALL `archive_old_accounts`();

事件調度器啟用（關鍵步驟）

臨時啟用（重啟失效）

SET GLOBAL event_scheduler = ON;
-- 或
SET @@global.event_scheduler = 1;

永久啟用（配置文件）

# /etc/my.cnf 或 /etc/mysql/my.cnf
[mysqld]
event_scheduler = 1

修改後：service mysql restart

狀態檢查

SHOW VARIABLES LIKE 'event_scheduler';
-- ON = 啟用, OFF = 關閉

SHOW EVENTS;  -- 查看所有事件狀態

權限要求

• 創建過程/事件：CREATE ROUTINE、EVENT 權限
• 執行INTO OUTFILE：FILE 權限 (僅全局授予)
• 目錄寫入權限：MySQL 進程用戶需有 secure_file_priv 指定目錄的寫權限

常見問題排查

監控與維護

-- 查看事件執行歷史 (需開啟 general_log 或自建日誌表)
SELECT * FROM mysql.event;

-- 手動觸發測試
CALL archive_old_accounts();

-- 禁用/刪除事件
ALTER EVENT daily_archive_event DISABLE;
DROP EVENT daily_archive_event;