MySQL 5.1+ 內建 **Event Scheduler (事件排程器)**,可在資料庫層級定義週期性任務,取代作業系統 `cron` / `Task Scheduler`,優點:隨資料庫備份遷移、權限統一管理、無需外部依賴。
-- 臨時啟用 (重啟失效)
SET GLOBAL event_scheduler = ON; -- 或 1
-- 永久啟用 (my.cnf / my.ini [mysqld] 區段)
event_scheduler = ON
驗證:`SHOW VARIABLES LIKE 'event_scheduler';` 或 `SHOW PROCESSLIST;` 觀察 `event_scheduler` 執行緒。
CREATE EVENT [IF NOT EXISTS] event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT 'comment']
DO sql_statement;
| 類型 | 語法範例 | 說明 |
|---|---|---|
| 一次性 | `AT '2024-12-31 23:59:59'` | 指定時間執行一次,完成預設刪除 (除非 PRESERVE) |
| 週期性 | `EVERY 1 DAY STARTS '2024-01-01 02:00:00'` | 每天固定時間,支援 SECOND/MINUTE/HOUR/DAY/WEEK/MONTH/YEAR |
| 複雜週期 | `EVERY 1 WEEK STARTS '2024-01-01 03:00:00' ENDS '2024-12-31'` | 可設定結束時間 |
CREATE EVENT `SyncListTest`
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
DO
INSERT INTO `list_test` (col1, col2, col3, col4, col5)
SELECT col1, col2, col3, col4, col5 FROM `table2` WHERE id = 510;
CREATE EVENT `DailyJob`
ON SCHEDULE EVERY 1 DAY
STARTS TIMESTAMP '2024-01-01 02:30:00' -- 注意:MySQL 伺服器時區 (system_time_zone) 決定執行時間
DO
CALL `usp_DailySettlement`();
DELIMITER $$
CREATE PROCEDURE `TEST_KKK`()
BEGIN
DECLARE v_num INT DEFAULT 0;
SELECT (20 - COUNT(*)) INTO v_num
FROM `get_stock_list` WHERE `FLAG` = 0 AND `STIME` = CURDATE();
IF v_num > 0 THEN
SET @stmt = 'INSERT INTO `get_stock_list` (`stock_code`,`stock_name`,`close`,`raises`,`stime`)
SELECT `stock_code`,`stock_name`,`close`,`result1`,`selectd`
FROM `choice_stock`
WHERE `stock_code` NOT IN (SELECT `stock_code` FROM `get_stock_list` WHERE `stime`=CURDATE() AND `flag`=0)
AND `selectd` = CURDATE() ORDER BY `id` LIMIT ?';
PREPARE stmp FROM @stmt;
SET @a = v_num;
EXECUTE stmp USING @a;
DEALLOCATE PREPARE stmp;
END IF;
END$$
DELIMITER ;
-- 建立每日觸發事件
CREATE EVENT `RunStockPick`
ON SCHEDULE EVERY 1 DAY STARTS '09:30:00'
DO CALL `TEST_KKK`();
-- 查看所有事件
SHOW EVENTS FROM `db_name`;
-- 查看特定事件定義
SHOW CREATE EVENT `db_name`.`event_name`;
-- 啟用/停用
ALTER EVENT `event_name` ENABLE;
ALTER EVENT `event_name` DISABLE;
-- 刪除
DROP EVENT [IF EXISTS] `event_name`;
-- 查看執行狀態 (需開啟 general_log 或效能架構)
SELECT * FROM information_schema.events;\code>