在MySQL5.5之后,可以创建事件调度,具体语法如下:
CREATE [DEFINER = {user
| CURRENT_USER }] EVENT [IF NOT EXISTS]event_name
ON SCHEDULEschedule
[ON COMPLETION [NOT] PRESERVE] [ENABLE | DISABLE | DISABLE ON SLAVE] [COMMENT 'comment
'] DOevent_body
;schedule
: ATtimestamp
[+ INTERVALinterval
] ... | EVERYinterval
[STARTStimestamp
[+ INTERVALinterval
] ...] [ENDStimestamp
[+ INTERVALinterval
] ...]interval
:quantity
{YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
例如:我计划从2014-04-14的17:28分开始每一个星期执行一个命令或事件,这里执行一个存储过程daily().
mysql>create event dodaily on schedule every 1 week starts '2014-04-14 17:28:00' on completion preserve enable do call daily();
查看调度器状态:
mysql>show events \G;
禁用某事件调度
mysql>alter event dodaily disable
重启某事件调度
mysql>alter event dodaily enable
删除某事件调度mysql>drop event dodaily ;
在设置了事件调度后,还要确认MySQL的事件调度器是否有开启,默认是关闭的,用以下命令查询其状态
mysql>show variables like '%scheduler%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
1 row in set (0.00 sec)
开启事件调度器用以下命令:
mysql>set GLOBAL event_scheduler = 1;
mysql>show variables like '%scheduler%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
但注意,当你重启MySQL服务时,事件调度器就会自动关闭,以上方法只是暂时开启,若想永久开启,就要修改MySQL的配置文件 (该文件,Linux的在/usr目录my..cnf下,Windows在其安装目录my-default.ini),打开配置文件,在[mysqld]增加以下配置
event_scheduler=ON
保存后,重启MySQL服务:service mysql restart, 那事件调度器就会永久开启了。