分类: Mysql/postgreSQL
2013-04-07 11:17:41
原文地址:MySQL5.1 event使用方法 作者:alang85
在MySQL5.1中计划调度用创建EVENT在表示。翻译为事物觉得不妥,此事物非Translation。
首先要查看EVENT开关,否则创建的EVENT不会运行。
mysql> select @@event_scheduler;
mysql> show variables like 'event_scheduler';
如果没有开启,开启它。
mysql> set GLOBAL event_scheduler=ON;
或
mysql> set GLOBAL event_scheduler=1;
--------
创建事物模板
DELIMITER $$
-- SET GLOBAL event_scheduler = ON$$ -- required for event to execute but not create
CREATE /*[DEFINER = { user | CURRENT_USER }]*/ EVENT `test`.`event_name`
ON SCHEDULE
/* uncomment the example below you want to use */
-- scheduleexample 1: run once
-- AT 'YYYY-MM-DD HH:MM.SS'/CURRENT_TIMESTAMP { + INTERVAL 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...] }
-- scheduleexample 2: run at intervals forever after creation
-- EVERY 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...]
-- scheduleexample 3: specified start time, end time and interval for execution
/*EVERY 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...]
STARTS CURRENT_TIMESTAMP/'YYYY-MM-DD HH:MM.SS' { + INTERVAL 1[HOUR|MONTH|WEEK|DAY|MINUTE|...] }
ENDS CURRENT_TIMESTAMP/'YYYY-MM-DD HH:MM.SS' { + INTERVAL 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...] } */
/*[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT 'comment']*/
DO
BEGIN
(sql_statements)
END$$
DELIMITER ;
例如创建一个EVENT,每5分钟执行,如下:
DELIMITER $$
CREATE EVENT `event_sync` ON SCHEDULE EVERY 5 MINUTE
ON COMPLETION PRESERVE ENABLE
DO
BEGIN
CALL pro_sync(); //调用存储过程时,如果此存储过程没有实参为NULL,必须加上()否则,事物不执行。
END$$
DELIMITER ;