navicat創(chuàng)建MySql定時任務(wù)的方法詳解

SHOW VARIABLES LIKE '%event_sche%';

SET GLOBAL event_scheduler = 1;

SET GLOBAL event_scheduler = 0;

1. 在創(chuàng)建事件myevent1小時后執(zhí)行，執(zhí)行一條更新

CREATE EVENT myevent
  ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
 UPDATE myschema.mytable SET mycol = mycol + 1;

2.2014年3月20日12點整清空test表：

CREATE EVENT e_test
  ON SCHEDULE AT TIMESTAMP '2014-03-20 12:00:00'
  DO TRUNCATE TABLE test.aaa;

3.5天后開啟每天定時清空test表：

CREATE EVENT e_test
  ON SCHEDULE EVERY 1 DAY
  STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY
  DO TRUNCATE TABLE test.aaa;

4.每天定時清空test表，5天后停止執(zhí)行

CREATE EVENT e_test
  ON SCHEDULE EVERY 1 DAY
  ENDS CURRENT_TIMESTAMP + INTERVAL 5 DAY
  DO TRUNCATE TABLE test.aaa;

5.5天后開啟每天定時清空test表，一個月后停止執(zhí)行：

CREATE EVENT e_test
  ON SCHEDULE EVERY 1 DAY
  STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY
  ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH
  DO TRUNCATE TABLE test.aaa;

6.每天定時清空test表(只執(zhí)行一次，任務(wù)完成后就終止該事件)：

CREATE EVENT e_test
  ON SCHEDULE EVERY 1 DAY
  ON COMPLETION NOT PRESERVE
  DO TRUNCATE TABLE test.aaa;

[ON COMPLETION [NOT] PRESERVE]可以設(shè)置這個事件是執(zhí)行一次還是持久執(zhí)行，默認為NOT PRESERVE。

ALTER EVENT myevent 
... 具體修改內(nèi)容

ALTER EVENT myevent DISABLE;

ALTER EVENT myevent ENABLE;

ALTER EVENT olddb.myevent
RENAME TO newdb.myevent;

ALTER EVENT olddb.myevent
RENAME TO newdb.myevent;

DROP EVENT [IF EXISTS] event_name

information_schema.events
mysql.event

show create event countsum \G

USE sem；
SHOW EVENTS \G