实例1:
/*开启功能*/
SET GLOBAL event_scheduler = ON;
DELIMITER //
DROP EVENT IF EXISTS `eventlog`;
CREATE EVENT IF NOT EXISTS `eventlog`
ON SCHEDULE
EVERY 1 DAY
STARTS TIMESTAMP(CURRENT_DATE,'09:00:00')
ON COMPLETION PRESERVE ENABLE
DO
BEGIN
DELETE FROM TAB_LOG WHERE TAB_LOG.DT_TIME < DATE_ADD(NOW(),INTERVAL "0" DAY);
END;
//
DELIMITER ;
实例2:
创建event My_enevt,每隔三十秒执行一次
create event if not exists e_test
on schedule every 30 second
on completion preserve
do call Mypro();
实例3:
DROP EVENT
IF EXISTS update_tmpTable;
create event if not exists update_tmpTable
on schedule EVERY 1 DAY STARTS TIMESTAMP (CURRENT_DATE, '10:13:00')
on completion preserve
do DROP TABLE IF EXISTS tmp_table;
alter event update_tmpTable ON COMPLETION PRESERVE ENABLE;
实例4:
DROP EVENT
IF EXISTS create_tmpTable;
create event if not exists create_tmpTable
on schedule EVERY 1 DAY STARTS TIMESTAMP (CURRENT_DATE, '10:19:00')
on completion preserve
do
CREATE table temp_table ENGINE = MEMORY
select * from user;
alter event create_tmpTable ON COMPLETION PRESERVE ENABLE;
实例5:
/*开启功能*/
SET GLOBAL event_scheduler = ON;
DELIMITER //
DROP EVENT IF EXISTS `eventlog`;
CREATE EVENT IF NOT EXISTS `eventlog`
ON SCHEDULE
EVERY 1 DAY
STARTS TIMESTAMP(CURRENT_DATE,'14:50:00')
ON COMPLETION PRESERVE ENABLE
DO
BEGIN
DROP TABLE IF EXISTS temp_table;
CREATE table temp_table ENGINE = MEMORY
select * from user;
END;
//
DELIMITER ;
关闭事件:
alter event e_test ON COMPLETION PRESERVE DISABLE;开启事件:
alter event e_test ON COMPLETION PRESERVE ENABLE;
1) 首先来看一个简单的例子来演示每秒插入一条记录到数据表 CREATE EVENT e_test_insert ON SCHEDULE EVERY 1 SECOND DO INSERT INTO test.aaa VALUES (CURRENT_TIMESTAMP); 2) 5天后清空test表: CREATE EVENT e_test ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 DAY DO TRUNCATE TABLE test.aaa; 3) 2013年7月20日12点整清空test表: CREATE EVENT e_test ON SCHEDULE AT TIMESTAMP '2013-07-20 12:00:00' DOTRUNCATE 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; [ON COMPLETION [NOT] PRESERVE]可以设置这个事件是执行一次还是持久执行,默认为NOT PRESERVE 8) 每天定时清空test表(只执行一次,任务完成后就终止该事件): CREATE EVENT e_test ON SCHEDULE EVERY 1 DAY ON COMPLETION NOT PRESERVE DO TRUNCATE TABLE test.aaa; [COMMENT comment]可以给该事件加上注释 2>修改事件(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; 3>删除事件(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;