INSERT/UPDATE/DELETE）crontab），MySQL 5.1+ 支持SET GLOBAL event_scheduler = ON;（或 =1）my.cnf [mysqld] 下加 event_scheduler = ON，重啟生效mysqld --event_scheduler=ONSHOW VARIABLES LIKE 'event_scheduler'; 或 SHOW PROCESSLIST; 看到 event_scheduler 線程最佳實踐：配置文件 + 運行時雙保險，防重啟失效。
EVENT 權限（mysql.user.Event_priv）CREATE ROUTINE、EXECUTE 權限UPDATE mysql.user SET Event_priv = 'Y' WHERE User='bfsql' AND Host='%';
FLUSH PRIVILEGES;
CREATE EVENT [IF NOT EXISTS] event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT 'comment']
DO sql_statement;
schedule 支持：
AT timestamp [+ INTERVAL interval] — 一次性EVERY interval [STARTS timestamp] [ENDS timestamp] — 循環| 需求 | SQL |
|---|---|
| 每秒插入時間戳 | CREATE EVENT e_test_insert ON SCHEDULE EVERY 1 SECOND DO INSERT INTO test.aaa VALUES (CURRENT_TIMESTAMP); |
| 5 天后清空表（一次性） | CREATE EVENT e_test ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 DAY DO TRUNCATE TABLE test.aaa; |
| 每天 01:00 清空表 | CREATE EVENT e_test ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_SUB(CURDATE(), INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 HOUR) DO TRUNCATE TABLE test.aaa; |
| 每月 1 號 02:00 統計 | CREATE EVENT e_month ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(DATE_SUB(CURDATE(), INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 2 HOUR) DO CALL STAT(); |
| 每季度 1 號 03:00 | CREATE EVENT e_quarter ON SCHEDULE EVERY 1 QUARTER STARTS DATE_ADD(DATE(CONCAT(YEAR(CURDATE()),'-',ELT(QUARTER(CURDATE()),1,4,7,10),'-1')), INTERVAL 3 HOUR) DO CALL SEASON_STAT(); |
| 每年 1 月 1 日 04:00 | CREATE EVENT e_year ON SCHEDULE EVERY 1 YEAR STARTS DATE_ADD(DATE(CONCAT(YEAR(CURDATE())+1,'-1-1')), INTERVAL 4 HOUR) DO CALL YEAR_STAT(); |
ALTER EVENT e_test ENABLE/DISABLE;ALTER EVENT e_test ON SCHEDULE EVERY 5 DAY;DROP EVENT [IF EXISTS] e_test;主庫開啟事件調度器，從庫關閉！ 事件執行的 SQL 會寫 Binlog 同步到從庫；若從庫也開啟，同一邏輯雙重執行 → 數據衝突、卡庫。切主時記得翻轉開關。
