event的使用例子

开启event

SET GLOBAL event_scheduler = 1;  mysql> SHOW VARIABLES LIKE 'event_scheduler' ;  +-----------------+-------+  | Variable_name   | Value |  +-----------------+-------+  | event_scheduler | ON    |  +-----------------+-------+  1 row in set (0.00 sec)  

语法体

CREATE      [DEFINER = { user | CURRENT_USER }]      EVENT      [IF NOT EXISTS]      event_name      ON SCHEDULE schedule      [ON COMPLETION [NOT] PRESERVE]      [ENABLE | DISABLE | DISABLE ON SLAVE]      [COMMENT 'comment']      DO event_body;  schedule:      AT timestamp [+ INTERVAL interval] ...      | EVERY interval      [STARTS timestamp [+ INTERVAL interval] ...]      [ENDS timestamp [+ INTERVAL interval] ...]  interval:      quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}  

例子：

DELIMITER $  CREATE EVENT del_history ON SCHEDULE EVERY 10 DAY  STARTS '2016-07-14 01:00:00'  COMMENT 'delete lepus slow_query_history'  DO  BEGIN  TRUNCATE TABLE `lepus`.`mysql_replication_history`;  TRUNCATE TABLE `lepus`.`mysql_status_history`;  TRUNCATE TABLE `lepus`.`mysql_slow_query_review`;  TRUNCATE TABLE `lepus`.`mysql_slow_query_review_history`;  END $  DELIMITER ;  

周期或者时间点语法

每1秒执行  on schedule every 1 second  10天后执行  on schedule at current_timestamp + interval 10 day  指定日期时间执行  on schedule at timestamp '2016-08-16 00:00:00'  每天凌晨3点执行  on schedule every 1 day   starts '2016-05-18 03:00:00' (设定从第二天凌晨3点开始)  每天定时执行，5天后停止执行  on schedule every 1 day   ends current_timestamp + interval 5 day  [plain] view plain copy5天后开启每天定时清空test表，一个月后停止执行  on schedule every 1 day  starts current_timestamp + interval 5 day  ends current_timestamp + interval 1 month  

操作event

临时关闭事件  alter event del_history disable;  临时开启事件  alter event del_history enable;  删除计划任务  drop event del_history;  

查看event

show events;  mysql> select * from information_schema.events\G  *************************** 1. row ***************************         EVENT_CATALOG: def          EVENT_SCHEMA: lepus            EVENT_NAME: del_history               DEFINER: root@localhost             TIME_ZONE: SYSTEM            EVENT_BODY: SQL      EVENT_DEFINITION: BEGIN  TRUNCATE TABLE `lepus`.`mysql_replication_history`;  TRUNCATE TABLE `lepus`.`mysql_status_history`;  TRUNCATE TABLE `lepus`.`mysql_slow_query_review`;  TRUNCATE TABLE `lepus`.`mysql_slow_query_review_history`;  END            EVENT_TYPE: RECURRING            EXECUTE_AT: NULL        INTERVAL_VALUE: 10        INTERVAL_FIELD: DAY              SQL_MODE:                 STARTS: 2016-07-14 01:00:00                  ENDS: NULL                STATUS: ENABLED         ON_COMPLETION: NOT PRESERVE               CREATED: 2016-07-08 18:17:27          LAST_ALTERED: 2016-07-08 18:17:27         LAST_EXECUTED: NULL         EVENT_COMMENT: delete lepus slow_query_history            ORIGINATOR: 1  CHARACTER_SET_CLIENT: utf8  COLLATION_CONNECTION: utf8_general_ci    DATABASE_COLLATION: utf8_general_ci