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');

或

create event test

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还是会继续运行.