MySQL中的事件调度基础学习教程

CREATE  [DEFINER = { user | CURRENT_USER }]  EVENT  [IF NOT EXISTS]  event_name  ON SCHEDULE schedule  [ON COMPLETION [NOT] PRESERVE]  [ENABLE | DISABLE | DISABLE ON SLAVE]  [COMMENT 'comment']  DO event_body;schedule:  AT timestamp [+ INTERVAL interval] …  | EVERY interval  [STARTS timestamp [+ INTERVAL interval] …]  [ENDS timestamp [+ INTERVAL interval] …]interval:  quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |       WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |       DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

ON SCHEDULEAT timestamp [+ INTERVAL interval] …| EVERY interval  [STARTS timestamp [+ INTERVAL interval] …]  [ENDS timestamp [+ INTERVAL interval] …]

<数字> {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |      WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |      DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

set global event_scheduler=on

[mysqld]event-scheduler=on

CREATE  [DEFINER = { user | CURRENT_USER }]  EVENT  [IF NOT EXISTS]  event_name  ON SCHEDULE schedule  [ON COMPLETION [NOT] PRESERVE]  [ENABLE | DISABLE | DISABLE ON SLAVE]  [COMMENT 'comment']  DO event_body; schedule:  AT timestamp [+ INTERVAL interval] ...   | EVERY interval  [STARTS timestamp [+ INTERVAL interval] ...]  [ENDS timestamp [+ INTERVAL interval] ...]interval: quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |       WEEK | SECOND | YEAR_MONTH | DAY_HOUR |DAY_MINUTE |DAY_SECOND | HOUR_MINUTE |HOUR_SECOND | MINUTE_SECOND}

ALTER  [DEFINER = { user | CURRENT_USER }]  EVENT event_name  [ON SCHEDULE schedule]  [ON COMPLETION [NOT] PRESERVE]  [RENAME TO new_event_name]  [ENABLE | DISABLE | DISABLE ON SLAVE]  [COMMENT 'comment']  [DO event_body]

DROP EVENT [IF EXISTS] event_name

CREATE EVENT e_hourly  ON SCHEDULE   EVERY 1 HOUR  COMMENT ‘Clears out sessions table each hour.'  DO   DELETE FROM site_activity.sessions;

delimiter |CREATE EVENT e  ON SCHEDULE   EVERY 5 SECOND  DO   BEGIN    DECLARE v INTEGER;    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;    SET v = 0;    WHILE v < 5 DO     INSERT INTO t1 VALUES (0);     UPDATE t2 SET s1 = s1 + 1;     SET v = v + 1;    END WHILE;  END |delimiter ;

mysql.eventinformation_schema.eventsshow eventsshow create event

CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `t1` datetime DEFAULT NULL, `id2` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=106 DEFAULT CHARSET=utf8

CREATE EVENT IF NOT EXISTS test ON SCHEDULE EVERY 3 SECONDON COMPLETION PRESERVEDO INSERT INTO test(id,t1) VALUES('',NOW());

CREATE EVENT IF NOT EXISTS testON SCHEDULEAT CURRENT_TIMESTAMP + INTERVAL 1 MINUTEDO TRUNCATE TABLE test.aaa;

CREATE EVENT IF NOT EXISTS testON SCHEDULEAT TIMESTAMP '2012-08-23 00:00:00'DO TRUNCATE TABLE test;

CREATE EVENT IF NOT EXISTS test ON SCHEDULE EVERY 3 SECONDSTARTS '2012-08-22 21:49:00' ENDS '2012-08-22 21:49:00'+ INTERVAL 10 MINUTEON COMPLETION PRESERVEDO INSERT INTO test(id,t1) VALUES('',NOW());

DROP PROCEDURE IF EXISTS test_add;DELIMITER //CREATE PROCEDURE test_add()BEGINDECLARE 1_id INT DEFAULT 1;DECLARE 1_id2 INT DEFAULT 0;DECLARE error_status INT DEFAULT 0;DECLARE datas CURSOR FOR SELECT id FROM test;DECLARE CONTINUE HANDLER FOR NOT FOUND SET error_status=1;OPEN datas;FETCH datas INTO 1_id;REPEATSET 1_id2=1_id2+2;UPDATE test SET id2=1_id2 WHERE id=1_id;FETCH datas INTO 1_id;UNTIL error_statusEND REPEAT;CLOSE datas;END//

CREATE EVENT test ON SCHEDULE EVERY 1 DAYSTARTS '2012-08-22 00:00:00'ENDS '2012-08-22 00:00:00'+INTERVAL 40 DAYON COMPLETION PRESERVE DOCALL test_add();