mysql 定時任務

定時任務在mysql中叫做event

1.定時任務需要開啟事件排程器，先看下排程器是否開啟，有多種方式

方式一:
mysql> SELECT @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| OFF               |
+-------------------+
1 row in set

方式二:
mysql> SHOW VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+
1 row in set

方式三:這裡沒有event_scheduler的程式
mysql> SHOW PROCESSLIST;
+----+------+-----------------+------+---------+------+-------+------------------+
| Id | User | Host            | db   | Command | Time | State | Info             |
+----+------+-----------------+------+---------+------+-------+------------------+
|  1 | root | localhost:63086 | NULL | Sleep   | 1140 |       | NULL             |
|  2 | root | localhost:63087 | test | Sleep   |   34 |       | NULL             |
|  4 | root | localhost:63241 | test | Query   |    0 | init  | SHOW PROCESSLIST |
+----+------+-----------------+------+---------+------+-------+------------------+
3 rows in set

如果開啟是這樣的
mysql> SHOW PROCESSLIST;
+----+-----------------+-----------------+------+---------+------+------------------------+------------------+
| Id | User            | Host            | db   | Command | Time | State                  | Info             |
+----+-----------------+-----------------+------+---------+------+------------------------+------------------+
|  1 | root            | localhost:63086 | NULL | Sleep   | 1239 |                        | NULL             |
|  2 | root            | localhost:63087 | test | Sleep   |  133 |                        | NULL             |
|  4 | root            | localhost:63241 | test | Query   |    0 | init                   | SHOW PROCESSLIST |
|  6 | event_scheduler | localhost       | NULL | Daemon  |    1 | Waiting on empty queue | NULL             |
+----+-----------------+-----------------+------+---------+------+------------------------+------------------+
4 rows in set

2.排程器的開啟與關閉
開啟
SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;

關閉
SET GLOBAL event_scheduler = OFF;
SET @@global.event_scheduler = OFF;
SET GLOBAL event_scheduler = 0;
SET @@global.event_scheduler = 0;


3.建立

CREATE
    [DEFINER = { user | CURRENT_USER }]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    DO event_body;

schedule:
    AT timestamp [+ INTERVAL interval] ...
  | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]

interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

這語法看上去很亂的樣子

例項一: 每分鐘向t表寫入一條資料
mysql> desc t;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| dt    | datetime | NO   | PRI | NULL    |       |
+-------+----------+------+-----+---------+-------+
1 row in set

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2015-03-19 16:09:54 |
+---------------------+
1 row in set

mysql> CREATE  EVENT event_t
ON SCHEDULE EVERY 1 MINUTE STARTS '2015-03-19 16:10:30' 
DO INSERT INTO t VALUES (now());
Query OK, 0 rows affected

mysql> select * from t;
Empty set

mysql> select * from t;
+---------------------+
| dt                  |
+---------------------+
| 2015-03-19 16:10:30 |
+---------------------+
1 row in set

mysql> select * from t;
+---------------------+
| dt                  |
+---------------------+
| 2015-03-19 16:10:30 |
| 2015-03-19 16:11:30 |
+---------------------+
2 rows in set


例項二：每分鐘向t表寫入一條資料，5分鐘後停止，也就是執行5次
mysql> select * from t;
Empty set
mysql> CREATE  EVENT event_t
ON SCHEDULE EVERY 1 MINUTE 
STARTS CURRENT_TIMESTAMP 
ENDS CURRENT_TIMESTAMP +  INTERVAL 5 MINUTE
DO INSERT INTO t VALUES (now());
Query OK, 0 rows affected

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2015-03-19 16:21:42 |
+---------------------+
1 row in set

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2015-03-19 16:29:10 |
+---------------------+
1 row in set

mysql> select * from t;
+---------------------+
| dt                  |
+---------------------+
| 2015-03-19 16:21:26 |
| 2015-03-19 16:22:26 |
| 2015-03-19 16:23:26 |
| 2015-03-19 16:24:26 |
| 2015-03-19 16:25:26 |
| 2015-03-19 16:26:26 |
+---------------------+
6 rows in set


例項三：DO後可以跟begin .. end 塊，建立一個t2表
mysql> desc t2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set

mysql> select * from t2;
Empty set

mysql> select * from t;
Empty set

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2015-03-19 16:52:31 |
+---------------------+
1 row in set

mysql> delimiter // 
mysql> CREATE EVENT event_t
ON SCHEDULE EVERY 1 MINUTE 
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP +  INTERVAL 2 MINUTE 
DO BEGIN
INSERT INTO t VALUES (now()) ;
INSERT INTO t2 VALUES (1) ;
END //
Query OK, 0 rows affected

mysql> delimiter;
mysql> select * from t;
+---------------------+
| dt                  |
+---------------------+
| 2015-03-19 16:52:50 |
+---------------------+
1 row in set

mysql> select * from t2
;
+----+
| id |
+----+
|  1 |
+----+
1 row in set

例項四：DO後跟儲存過程

mysql> delimiter //
mysql> CREATE PROCEDURE p_t()
BEGIN
INSERT INTO t VALUES (now()) ;
INSERT INTO t2 VALUES (2);
END //
Query OK, 0 rows affected

mysql> delimiter;
mysql> 
mysql> delete from t;
Query OK, 3 rows affected

mysql> delete from t2;
Query OK, 3 rows affected

mysql> CREATE EVENT event_t
ON SCHEDULE EVERY 1 MINUTE 
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP +  INTERVAL 2 MINUTE 
DO CALL p_t
();
Query OK, 0 rows affected

mysql> select * from t;
+---------------------+
| dt                  |
+---------------------+
| 2015-03-19 17:06:28 |
+---------------------+
1 row in set

mysql> select * from t2;
+----+
| id |
+----+
|  2 |
+----+
1 row in set


4.修改，其實和建立差不多

ALTER
    [DEFINER = { user | CURRENT_USER }]
    EVENT event_name
    [ON SCHEDULE schedule]
    [ON COMPLETION [NOT] PRESERVE]
    [RENAME TO new_event_name]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    [DO event_body]

關閉
ALTER EVENT event_t DISABLE;

修改頻率
ALTER EVENT event_t
ON SCHEDULE EVERY 5 MINUTE