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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29339009/viewspace-1466322/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29339009/viewspace-1466322/