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