MySQL之事件学习整理(转)
MySQL之事件学习整理
在使用事件之前需要激活MySQL的事件调度器(Event Scheduler),激活方式如下:
set global event_scheduler=1
或者 set global event_scheduler=on
然后我们可以查看调度器是否已经开启:
show variables like 'event_scheduler';
或者select @@event_scheduler;
如果我们要关闭调度器则采取相反的动作即可:
set global event_scheduler=off;
下面总结event的相关处理方法:
1 .创建event 命令如下:
create event [if not exists] event_name SCHEDULE schedule [on completion [not] preserve] [enable|disable] do sql_statements;
schedule :
at timestamp [+interval interval] | every interval [starts timestamp] [ends timestamp]
interval:
quantity {year|quarter|month|week|day|hour|minute|second}
2 .修改event命令如下:
alter event event_name enable;//事件可用
alter event event_name disable;//事件不可用
3 .删除event命令如下:
drop event event_name;
event举例如下:
首先建立一个表
create table t(id int not null auto_increment ,my_time timestamp)engine='InnoDB' default charset='utf8';
建立每秒向表中插入数据的event:
create event insert_event schedule every 1 second on completion preserve do
insert into t(my_time) values(now());
修改insert_event 不可用:
alter event insert_event disable;
删除insert_event:
drop event insert_event;
好了,今天的事件学习就整理到这吧.....