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;
从 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;
