本文共 2889 字，大约阅读时间需要 9 分钟。
event event 类似 oracle job 功能, linux crontab 任务 从 MySQL 5.1.12 开始被支持 默认系统关闭该功能 root@localhost[(none)]>show variables like 'event%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | OFF | +-----------------+-------+ 1 row in set (0.00 sec) 修改 my.ini, 重启 mysql 或者 set global event_scheduler=on; 方法都可以启动 event_scheduler=1 授权用户创建, 修改, 丢弃事件 grant event on new.* to tt@localhost; 查询事件方法 select * from information_schema.events; show events; 语法 - CREATE [DEFINER = { user | CURRENT_USER }] <- 默认 DEFINER = CURRENT_USER EVENT [IF NOT EXISTS] event_name ON SCHEDULE schedule [ON COMPLETION [NOT] PRESERVE] <- 通常 EVENT 过期马上丢弃 not peserve 不丢弃 [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}
测试前提 pro_del_early - create table u1 ( id int, time datetime ); delimiter // create procedure pro_del_early() begin declare d1 time; select now() - INTERVAL 1800 SECOND into d1; delete from u1 where time < d1; end // delimiter ;
时间定义 - 一次性执行 ON SCHEDULE at current_timestamp + interval 4 hour ON SCHEDULE at current_timestamp - interval 4 hour <- 注意, 创建成功, 但不执行, 无效时间 ON SCHEDULE at current_timestamp + interval '6:15' HOUR_MINUTE 重复每天执行 ON SCHEDULE every 1 day ON SCHEDULE every 1 day STARTS CURRENT_TIMESTAMP + interval 4 hour on schedule every 30 minute 指定时间范围 从 4 小时后至某个固定时间, 没隔三日开始一次 ON SCHEDULE every 3 day STARTS current_timestamp + interval 4 hour ends "2012-11-11 11:11:00" ON SCHEDULE every 3 day STARTS '2012-03-01 11:11:00' ends "2012-11-11 11:11:00" on schedule EVERY 2 WEEK STARTS CURRENT_TIMESTAMP + INTERVAL '6:15' HOUR_MINUTE on schedule EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK
ex1 - 每天当前时间执行 pro_del_early 存储过程 create event if not exists ev1 ON SCHEDULE every 1 day do call pro_del_early();
ex2 - 一次性 当前 4 小时候执行 pro_del_early create event if not exists ev2 ON SCHEDULE at current_timestamp + interval 4 hour ON COMPLETION NOT PRESERVE enable do call pro_del_early();
ex3 - 每天执行, 当前时候三小时后开始 create event if not exists ev3 ON SCHEDULE every 1 day STARTS CURRENT_TIMESTAMP + interval 4 hour ON COMPLETION NOT PRESERVE enable do call pro_del_early();
ex4 - 每 30 分钟执行一次 create event if not exists ev4 ON SCHEDULE every 30 minute ON COMPLETION NOT PRESERVE enable do call pro_del_early();
ex5 - 两个时间段之间重复执行 create event if not exists ev5 ON SCHEDULE every 3 day STARTS '2012-03-03 11:11:00' ENDS '2012-11-11 11:11:00' ON COMPLETION NOT PRESERVE enable do call pro_del_early();
启用与关闭 - 关闭某个 schedule alter event ev1 disable; alter event ev1 enable;
转载地址：http://tqnni.baihongyu.com/