849
mysql event
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;
最後更新：2017-04-02 22:16:23