mysql 定时任务 event

查看当前是否已开启事件调度器，可执行如下SQL：
SHOW VARIABLES LIKE 'event_scheduler';
或SELECT @@event_scheduler;
或SHOW PROCESSLIST;


开启event_scheduler
SET GLOBAL event_scheduler = 1;
或SET GLOBAL event_scheduler = ON;


创建事件(CREATE EVENT)
CREATE EVENT [IFNOT EXISTS] event_name
 ONSCHEDULE schedule
 [ONCOMPLETION [NOT] PRESERVE]
 [ENABLE | DISABLE]
 [COMMENT 'comment']
 DO sql_statement;


 schedule:
　　 AT TIMESTAMP [+ INTERVAL INTERVAL]
　　 | EVERY INTERVAL [STARTS TIMESTAMP] [ENDS TIMESTAMP]
　　
　　INTERVAL:
　　 quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
　　 WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
　　 DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}


eg:
　CREATE EVENT e_test_insert
  ON SCHEDULE EVERY 1 SECOND
  DO INSERT INTO test.aaa VALUES(CURRENT_TIMESTAMP);


  5天后开启每天定时清空test表，一个月后停止执行：
　CREATE EVENT e_test
  ON SCHEDULE EVERY 1 DAY
  STARTS CURRENT_TIMESTAMP+ INTERVAL 5 DAY
  ENDS CURRENT_TIMESTAMP+ INTERVAL 1 MONTH
  DO TRUNCATETABLE test.aaa;


  [ON COMPLETION [NOT] PRESERVE]可以设置这个事件是执行一次还是持久执行，默认为NOT PRESERVE。
  每天定时清空test表(只执行一次，任务完成后就终止该事件)：
　CREATE EVENT e_test
  ONSCHEDULE EVERY 1 DAY
  ONCOMPLETION NOT PRESERVE
  DO TRUNCATETABLE test.aaa;


 修改事件(ALTER EVENT)
  ALTER EVENT event_name
  [ONSCHEDULE schedule]
  [RENAME TOnew_event_name]
  [ONCOMPLETION [NOT] PRESERVE]
  [COMMENT 'comment']
  [ENABLE | DISABLE]
  [DO sql_statement]


  临时关闭事件 ALTER EVENT e_test DISABLE;
  开启事件 ALTER EVENT e_test ENABLE;
  删除事件(DROP EVENT) DROP EVENT [IF EXISTS] event_name