MySQL：event的简单使用

来源：互联网发布：betterzip 4 for mac 编辑：程序博客网时间：2024/05/22 03:37

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 DAYSTARTS '2016-07-14 01:00:00'COMMENT 'delete lepus slow_query_history'DOBEGINTRUNCATE 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 second10天后执行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 daystarts current_timestamp + interval 5 dayends 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: BEGINTRUNCATE 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: 1CHARACTER_SET_CLIENT: utf8COLLATION_CONNECTION: utf8_general_ci  DATABASE_COLLATION: utf8_general_ci

0 0