详解MySQL新增的功能（事件调度器）实例

#方式一 SHOW VARIABLES LIKE 'event_scheduler'; #方式二 SELECT @@event_scheduler; #方式三 SHOW PROCESSLIST; #查看事件状态 SHOW EVENTS;

SET GLOBAL event_scheduler = ON;

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 event_insert_t2 on schedule every 3 second on completion preserve do insert into test2(department,time_v) value('1',now());

CREATE EVENT IF NOT EXISTS event_truncate_test2 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 10 MINUTE DO TRUNCATE TABLE test2;

DROP EVENT IF EXISTS event_truncate_test2; CREATE EVENT event_truncate_test2 ON SCHEDULE AT TIMESTAMP '2015-04-17 14:42:00' DO TRUNCATE TABLE test2;

CREATE EVENT IF NOT EXISTS event_truncate_test2 ON SCHEDULE EVERY 3 SECOND STARTS CURRENT_TIMESTAMP + INTERVAL 5 day ENDS CURRENT_TIMESTAMP + INTERVAL 1 month ON COMPLETION PRESERVE DO INSERT INTO test2(department,time_v) VALUES('1',NOW());

alter event event_insert_t2 disable;

DROP EVENT IF EXISTS event_insert_t2;