MySQL：event的简单使用

1、开启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)

2、语法体

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 ;

3、周期或者时间点语法

每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

5天后开启每天定时清空test表，一个月后停止执行
on schedule every 1 day
starts current_timestamp + interval 5 day
ends current_timestamp + interval 1 month

4、操作event

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

5、查看event

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