创建事件(CREATE EVENT)
先来看一下它的语法:
CREATE EVENT [IF NOT EXISTS] event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT 'comment']
DO sql_statement / call proce() ; --这可以是sql语句或 call 存储过程名()
schedule:
AT TIMESTAMP [+ INTERVAL INTERVAL]
| EVERY INTERVAL [STARTS TIMESTAMP] [ENDS TIMESTAMP]
INTERVAL:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH
1) 首先来看一个简单的例子来演示每秒插入一条记录到数据表
USE test;
CREATE TABLE aaa (timeline TIMESTAMP);
CREATE EVENT e_test_insert
ON SCHEDULE EVERY 1 SECOND
DO INSERT INTO test.aaa VALUES (CURRENT_TIMESTAMP);
等待3秒钟后,再执行查询看看:
mysql> SELECT * FROM aaa;
2)每天定时清空test表: CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
starts TIMESTAMP '2011-07-08 12:00:00'DO TRUNCATE TABLE test.aaa;
3) 每天定时清空test表,5天后停止执行:CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.aaa;
4) 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;