mysql的一些操作

定时任务：

--对于表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!