1、开启事件调度器
确认是否开启
SHOW VARIABLES LIKE 'event_scheduler';
开启命令
SET GLOBAL event_scheduler = ON;
2、创建事件
CREATE EVENT [IFNOT EXISTS] event_name
ONSCHEDULE schedule
[ONCOMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT 'comment']
DO sql_statement;
例如每秒插入一条记录到表:
USE test;
CREATE TABLE time_table (timeline TIMESTAMP);
CREATE EVENT e_test_insert ON SCHEDULE EVERY 1 SECOND DO INSERT INTO test.time_table VALUES(CURRENT_TIMESTAMP);
(即是CREATE EVENT e_test_insert
ON SCHEDULE EVERY 1 SECOND
DO INSERT INTO test.time_table VALUES(CURRENT_TIMESTAMP);
)
例如将该表清空:
CREATE EVENT e_test_truncate
ON SCHEDULE AT TIMESTAMP '2014-09-29 11:20:00'
DO TRUNCATE TABLE test.time_table;
例如每天定时清空:
CREATE EVENT e_test_truncate_daily
ON SCHEDULE EVERY 1 DAY
STARTS '2014-09-29 00:00:00'
DO TRUNCATE TABLE test.time_table;
3、关闭和开启事件
ALTER EVENT event_name DISABLE;
ALTER EVENT event_name ENABLE;
4、删除事件
DROP EVENT [IF EXISTS] event_name
5、查看事件
SHOW EVENTS;
确认是否开启
SHOW VARIABLES LIKE 'event_scheduler';
开启命令
SET GLOBAL event_scheduler = ON;
2、创建事件
CREATE EVENT [IFNOT EXISTS] event_name
ONSCHEDULE schedule
[ONCOMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT 'comment']
DO sql_statement;
例如每秒插入一条记录到表:
USE test;
CREATE TABLE time_table (timeline TIMESTAMP);
CREATE EVENT e_test_insert ON SCHEDULE EVERY 1 SECOND DO INSERT INTO test.time_table VALUES(CURRENT_TIMESTAMP);
(即是CREATE EVENT e_test_insert
ON SCHEDULE EVERY 1 SECOND
DO INSERT INTO test.time_table VALUES(CURRENT_TIMESTAMP);
)
例如将该表清空:
CREATE EVENT e_test_truncate
ON SCHEDULE AT TIMESTAMP '2014-09-29 11:20:00'
DO TRUNCATE TABLE test.time_table;
例如每天定时清空:
CREATE EVENT e_test_truncate_daily
ON SCHEDULE EVERY 1 DAY
STARTS '2014-09-29 00:00:00'
DO TRUNCATE TABLE test.time_table;
3、关闭和开启事件
ALTER EVENT event_name DISABLE;
ALTER EVENT event_name ENABLE;
4、删除事件
DROP EVENT [IF EXISTS] event_name
5、查看事件
SHOW EVENTS;