mysql event 用法_MySQL5.1 event使用方法

在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 ;