MySQL 定時事件排程器 建立與管理完整教學

一、事件排程器啟用與檢查

1. 檢查狀態（三種方法）

SHOW VARIABLES LIKE 'event_scheduler';
SELECT @@event_scheduler;
SHOW PROCESSLIST;

2. 啟用/關閉（四種方法）

SET GLOBAL event_scheduler = 1;     -- 或 ON
SET @@global.event_scheduler = 1;
SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
-- 關閉用 0 或 OFF

二、權限控制

• 單獨 EVENT：建立/檢視需 EVENT 權限，執行需對應 SQL 權限。權限儲存於 mysql.user 和 mysql.db 的 Event_priv 欄位。
• EVENT + Stored Procedure：建立需 CREATE ROUTINE，呼叫需 EXECUTE，內部 SQL 需對應權限。

-- 授權範例
UPDATE mysql.user SET Event_priv = 'Y' WHERE Host='%' AND User='bfsql';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'bfsql'@'%';

三、建立事件語法

CREATE EVENT [IF NOT EXISTS] event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT 'comment']
DO sql_statement

排程類型範例

-- 每 30 秒執行一次
CREATE EVENT IF NOT EXISTS e_blog
ON SCHEDULE EVERY 30 SECOND
ON COMPLETION PRESERVE
DO BEGIN CALL MoveBlogData(); END;

-- 立即開始，每 9 天執行
CREATE EVENT EVENT1
ON SCHEDULE EVERY 9 DAY STARTS NOW()
ON COMPLETION PRESERVE ENABLE
DO BEGIN CALL TOTAL(); END;

-- 每月 1 號凌晨 1 點
CREATE EVENT EVENT2
ON SCHEDULE EVERY 1 MONTH
STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(), INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH), INTERVAL 1 HOUR)
ON COMPLETION PRESERVE ENABLE
DO BEGIN CALL STAT(); END;

-- 每季首日凌2點
CREATE EVENT TOTAL_SEASON_EVENT
ON SCHEDULE EVERY 1 QUARTER
STARTS DATE_ADD(DATE_ADD(DATE(CONCAT(YEAR(CURDATE()),'-',ELT(QUARTER(CURDATE()),1,4,7,10),'-',1)), INTERVAL 1 QUARTER), INTERVAL 2 HOUR)
ON COMPLETION PRESERVE ENABLE
DO BEGIN CALL SEASON_STAT(); END;

-- 每年 1/1 凌晨 4 點
CREATE EVENT TOTAL_YEAR_EVENT
ON SCHEDULE EVERY 1 YEAR
STARTS DATE_ADD(DATE(CONCAT(YEAR(CURDATE())+1,'-',1,'-',1)), INTERVAL 4 HOUR)
ON COMPLETION PRESERVE ENABLE
DO BEGIN CALL YEAR_STAT(); END

四、事件啟用/停用

ALTER EVENT e_test ON COMPLETION PRESERVE ENABLE;   -- 啟用
ALTER EVENT e_test ON COMPLETION PRESERVE DISABLE;  -- 停用

五、實戰範例

-- 1. 建表
CREATE TABLE mytable (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL DEFAULT '',
  introduce TEXT NOT NULL,
  createtime TIMESTAMP NOT NULL
);

-- 2. 建立預存程序
CREATE PROCEDURE mypro()
BEGIN
  INSERT INTO mytable (name, introduce, createtime)
  VALUES ('1111', 'inner mongolia', NOW());
END;

-- 3. 建立事件（每秒執行）
CREATE EVENT IF NOT EXISTS eventJob
ON SCHEDULE EVERY 1 SECOND
ON COMPLETION PRESERVE
DO CALL mypro();

-- 4. 啟動排程器與事件
SET GLOBAL event_scheduler = 1;
ALTER EVENT eventJob ON COMPLETION PRESERVE ENABLE;

-- 5. 驗證
SELECT * FROM mytable;  -- 每秒新增一筆