mysql 定时事件(定时任务)

时间:2020-12-13 07:50:51
创建事件(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;