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} |