Mysql的event schedule可以讓你設置你的mysql數據庫在某個時間段執行你想要的動作
create event test1
on schedule every 1 day
starts '2007-09-01 12:00:00'
on completion not preserve
do insert into yyy values('hhh','uuu');
或
on schedule at '2007-09-01 12:00:00' + interval 1 day
on completion not preserve
do insert into yyy values('hhh','uuu')
解釋:從2007年9月1日開始,每天對表yyy在12:00:00進行插入操作,并且只執行一次.
使用這個功能之前必須確保event_scheduler已開啟,可執行
set global event_scheduler=1;
或
set global event_scheduler=on;
來開啟,也可以直接在啟動命令上加上--event_scheduler=1.例如:
mysqld...--event_scheduler=1
另外也可以直接在mysql.ini或者mysql.cnf中添加
event_scheduler=1
要查看當前是否已經開啟時間調度器,可以執行如下sql:
show variables like 'event-scheduler';
或者
select @@event_scheduler;
或者show processlist;
二,創建時間(create event)
create event [if not exists] event_name on schedule [on completion[not] preserve] [enable|disable] [comment 'comment'] do sql_statement; schedule: at timestamp [+interval interval] |every interval [starts timestamp][ends timestamp] interval: quantity{year|quarter|month|day|hour|minute| week|second|year_month|day_hour|day_minute| day_second|hour_minute|hour_second|minute_second} |
1)首先來看一個簡單的例子來演示每秒插入一條記錄到數據表:
use test
create table aaa(timeline timestamp);
create event e_test_insert
on schedule every 1 second
do insert into test.aaa values(current_timestamp);
等待三秒,再執行查詢看看:
mysql>select * from test.aaa;
就可以看到有三條數據存在
2)5天后清空aaa表:
create event e_test
on schedule at current_timestamp+interval 5 day
do truncate table test.aaa;
3)2007年7月20日12點整清空aaa表:
create event e_test
on schedule at timestamp '2007-07-20 12:00:00'
do truncate table test.aaa;
4)每天定時清空aaa表:---執行之后,是指每天當前創建時間執行該event
create event e_test
on schedule every 1 day
do truncate table test.aaa;
5)5天后開啟每天定時清空aaa表:
create event e_test
on schedule every 1 day
starts current_timestamp +interval 5 day
do truncate table test.aaa;
6)每天定時清空aaa表,5天后停止執行:
create event e_test
on schedule every 1 day
ends current_timestamp + interval 5 day
do truncate table test.aaa;
7)5天后開啟每天定時清空aaa表,一個月后停止執行
create event e_test
on schedule every 1 day
starts current_timestamp + interval 5 day
ends current_timestamp + interval 1 month
do truncate table test.aaa;
三,修改事件(alter event)
alter event event_name
[on schedule schedule]
[rename to new_event_name]
[on completion [not] preserve]
[comment 'comment']
[enable|disable]
[do sql_statement]
1)臨時關閉事件
alter event e_test disable
2)開啟事件
alter event e_test enable
3)將每天清空aaa表修改成每5天清空一次
alter event e_test
on schedule every 5 day;
四,刪除事件(drop event)
語法很簡單,如下所示:
drop event [if exists] event_name
例如刪除前面創建的e_test事件
drop event e_test
當前前提是這個事件存在,否則會產生error 1513(HY000):unknown event錯誤,因此最好加上if exists
drop event if exists e_test
另外當在my.ini或者my.cnf中添加了event_scheduler=1參數,那么在mysql啟動之后,存在的event還是會繼續運行.