navicat创建MySql定时任务的方法详解

SHOW VARIABLES LIKE '%event_sche%';

SET GLOBAL event_scheduler = 1;

SET GLOBAL event_scheduler = 0;

1. 在创建事件myevent1小时后执行，执行一条更新

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天后停止执行

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

5.5天后开启每天定时清空test表，一个月后停止执行

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表(只执行一次，任务完成后就终止该事件)

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

[ON COMPLETION [NOT] PRESERVE]可以设置这个事件是执行一次还是持久执行，默认为NOT PRESERVE。

ALTER EVENT myevent 
... 具体修改内容

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