第二篇：MySQL的定时任务

1.使用范围

MySQL的定时器任务不是所有的MySQL版本都支持，Mysql 5.1之后才开始支持的新特性。

2.具体语法和SQL

– 查看当前是否已开启事件调度器
SHOW VARIABLES LIKE ‘event_scheduler’;

• 要想保证能够执行event事件，就必须保证定时器是开启状态，默认为关闭状态
  如果为OFF，表示关闭
  
  如果为ON，表示开启
  
  – 开启事件调度器
  SET GLOBAL event_scheduler = ON;
  SET GLOBAL event_scheduler =1;
  – 关闭事件调度器
  SET GLOBAL event_scheduler = OFF;
  SET GLOBAL event_scheduler = 0;
  – 删除事件
  DROP EVENT IF EXISTS e_test_insert;
  – 创建事件
  标准语法：

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} 

创建一个事件：每隔3秒往user表中插1条数据
CREATE EVENT e_test_insert
ON SCHEDULE EVERY 3 SECOND
DO INSERT test.user (NAME,age)VALUE(‘qiang’,4);

– 关闭事件任务
ALTER EVENT e_test_insert ON COMPLETION PRESERVE DISABLE;
– 开启事件任务
ALTER EVENT e_test_insert ON COMPLETION PRESERVE ENABLE;
– 查看事件任务
SHOW EVENTS ;

– 修改事件任务
标准语法：
ALTER EVENT event_name
[ON SCHEDULE schedule]
[RENAME TO new_event_name]
[ON COMPLETION [NOT] PRESERVE]
[COMMENT ‘comment’]
[ENABLE | DISABLE]
[DO sql_statement]

把e_test_insert事件修改为每1秒插入1条数据

alter EVENT e_test_insert
ON SCHEDULE EVERY 1 SECOND
DO INSERT test.`user` (NAME,age)VALUE('qiang',4);