定时任务:
--对于表dayrecord定时清空event
DELIMITER $$
CREATE EVENT `event_dayrecord` ON SCHEDULE EVERY 1 DAY STARTS '2011-01-10 00:00:00' ON COMPLETION PRESERVE ENABLE DO DELETE FROM dayrecord$$
DELIMITER ;
--开启事件event_dayrecord
ALTER event event_dayrecord ON COMPLETION PRESERVE ENABLE;
查看错误:
SHOW ENGINE INNODB STATUS;
在使用这个功能之前必须确保event_scheduler已开启,可执行
SET GLOBAL event_scheduler = 1;或
SET GLOBAL event_scheduler = ON;来开启
要查看当前是否已开启事件调度器,可执行如下SQL:
SHOW VARIABLES LIKE 'event_scheduler';或
SELECT @@event_scheduler;或
SHOW PROCESSLIST
创建事件语法:
创建事件(CREATE EVENT)
先来看一下它的语法:
CREATE EVENT [IF NOT EXISTS] event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT 'comment']
DO sql_statement;
schedule:
AT TIMESTAMP [+ INTERVAL INTERVAL]
| EVERY INTERVAL [STARTS TIMESTAMP] [ENDS TIMESTAMP]
INTERVAL:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}1)
定时插入:
CREATE TABLE aaa (timeline TIMESTAMP);
CREATE EVENT e_test_insert
ON SCHEDULE EVERY 1 SECOND
DO INSERT INTO test.aaa VALUES (CURRENT_TIMESTAMP);等待3秒钟后,再执行查询看看:
5天后清空test表:
CREATE EVENT e_test
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.aaa;3)
2007年7月20日12点整清空test表:
CREATE EVENT e_test
ON SCHEDULE AT TIMESTAMP '2007-07-20 12:00:00'
DO TRUNCATE TABLE test.aaa;4) 每天定时清空test表:
CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
DO TRUNCATE TABLE test.aaa;5) 5天后开启每天定时清空test表:
CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.aaa;6) 每天定时清空test表,5天后停止执行:
CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.aaa;7) 5天后开启每天定时清空test表,一个月后停止执行:
CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH
DO TRUNCATE TABLE test.aaa;
三、修改事件(ALTER EVENT)
ALTER EVENT event_name
[ON SCHEDULE schedule]
[RENAME TO new_event_name]
[ON COMPLETION [NOT] PRESERVE]
[COMMENT 'comment']
[ENABLE | DISABLE]
[DO sql_statement]1) 临时关闭事件
ALTER EVENT e_test DISABLE;2) 开启事件
ALTER EVENT e_test ENABLE;3) 将每天清空test表改为5天清空一次:
ALTER EVENT e_test
ON SCHEDULE EVERY 5 DAY;四、删除事件(DROP EVENT)
语法很简单,如下所示:
DROP EVENT [IF EXISTS] event_name例如删除前面创建的e_test事件
DROP EVENT e_test;当然前提是这个事件存在,否则会产生ERROR 1513 (HY000): Unknown event错误,因此最好加上IF EXISTS
DROP EVENT IF EXISTS e_test;五、后续
该特性确实非常有用,可作为定时清空数据表、监控主从服务器、汇总数据到另一张表等等,并且可以精确到每秒,实时性也可以得到保障。
Enjoy it!