ITPub博客

首页 > Linux操作系统 > Linux操作系统 > mysql event

mysql event

原创 Linux操作系统 作者:tangchaoql 时间:2011-08-03 17:20:10 0 删除 编辑

 [event schedule]

 [事件调度]

 [事件调度任务,MySQL 5.1开始也提供强大的计划任务,使得许多工作变得更简单。OS的计划任务相似,他们不一样的是由数据库内部执行的。例如,Unix著名的crontab服务就提供了强大的计划任务支持。]

作者:tc-ql@163.com


 

 

 

 

 

1. Event_scheduler

 

缺省状态event schedule没有开启

SQL>show processlist;

+-------+----------+-----------+------+---------+------+-------+------------------+

| Id    | User     | Host      | db   | Command | Time | State | Info             |

+-------+----------+-----------+------+---------+------+-------+------------------+

| 15104 | tangchao | localhost | NULL | Query   |    0 | NULL  | show processlist |

+-------+----------+-----------+------+---------+------+-------+------------------+

1 row in set (0.00 sec)

 

SQL>SELECT variable_value

    -> FROM information_schema.global_variables

    -> WHERE variable_name = 'event_scheduler';

+----------------+

| variable_value |

+----------------+

| OFF            |

+----------------+

1 row in set (0.00 sec)

 

 

调度任务支持动态开关,布尔值ON/1表示打开,OFF/0表示关闭。

SQL>set @@global.event_scheduler=ON;

Query OK, 0 rows affected (0.00 sec)

 

SQL>set @@global.event_scheduler=1;

Query OK, 0 rows affected (0.00 sec)

 

SQL>SELECT variable_value FROM information_schema.global_variables WHERE variable_name = 'event_scheduler';

+----------------+

| variable_value |

+----------------+

| ON             |

+----------------+

1 row in set (0.00 sec)

 

SQL>

 

 

 

这时调度任务线程开启,没有任务,所以等待队列是空。

SQL>show processlist;

+-------+-----------------+-----------+------+---------+------+------------------------+------------------+

| Id    | User            | Host      | db   | Command | Time | State                  | Info             |

+-------+-----------------+-----------+------+---------+------+------------------------+------------------+

| 15104 | tangchao        | localhost | NULL | Query   |    0 | NULL                   | show processlist |

| 15138 | event_scheduler | localhost | NULL | Daemon  |   82 | Waiting on empty queue | NULL             |

+-------+-----------------+-----------+------+---------+------+------------------------+------------------+

2 rows in set (0.00 sec)

 

SQL>

 

 

帮助手册里面有创建事件(就是计划任务)的SQL语法。

SQL>help create event

Name: 'CREATE EVENT'

Description:

Syntax:

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}

 

 

只执行一次的,使用AT,在什么时间点执行。

需要重复执行的要制定everyevery表示执行频率,并且要实现某种频率还需要搭配startsends来限制。

 

 

当前才test下进行测试说明调度任务。

SQL>SELECT DATABASE() FROM DUAL;

+------------+

| DATABASE() |

+------------+

| test       |

+------------+

1 row in set (0.00 sec)

 

SQL>SELECT table_name FROM information_schema.tables WHERE table_schema='test'

    -> ;

+-----------------+

| table_name      |

+-----------------+

| 192_168_122_105 |

| nagios          |

+-----------------+

2 rows in set (0.00 sec)

 

 

 

SQL>CREATE TABLE t1(

    -> id INT AUTO_INCREMENT,

    -> date_time DATETIME DEFAULT '1980-01-01 00:00:00',

    -> PRIMARY KEY(id)

    -> );

Query OK, 0 rows affected (0.00 sec)

 

SQL>SHOW CREATE TABLE t1;

+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                                                                               |

+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| t1    | CREATE TABLE `t1` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `date_time` datetime DEFAULT '1980-01-01 00:00:00',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=gbk |

+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

 

创建一个以创建时间开始,频率10秒向T1表插入数据的调度任务。

SQL>CREATE EVENT e_t1_insert

    -> ON SCHEDULE

    -> EVERY 10 SECOND

    -> ENABLE

    -> DO

    -> INSERT INTO t1(date_time) VALUES(NOW());

Query OK, 0 rows affected (0.00 sec)

 

SQL>SELECT id,date_time

    -> FROM t1;

+----+---------------------+

| id | date_time           |

+----+---------------------+

|  1 | 2011-06-09 11:00:23 |

|  2 | 2011-06-09 11:00:33 |

|  3 | 2011-06-09 11:00:43 |

|  4 | 2011-06-09 11:00:53 |

|  5 | 2011-06-09 11:01:03 |

|  6 | 2011-06-09 11:01:13 |

|  7 | 2011-06-09 11:01:23 |

|  8 | 2011-06-09 11:01:33 |

+----+---------------------+

8 rows in set (0.00 sec)

 

我们在创建的时指定ENABLE开启这个事件,也可以指定DISABLE不开启这个事件。

 

停止这个事件调度任务。

SQL>ALTER EVENT e_t1_insert DISABLE;

Query OK, 0 rows affected (0.00 sec)

 

因为一个创建成功的任务也可能执行失败,所以计划任务创建成功,也要进行测试,确保能正确运行。可以通过error log来查找任务失败的原因。

 

 

 

 

较为复杂的执行和动态SQL,可以使用SP来实现,嵌套到时间调度里面按时执行即可。

 

测试表

SQL>CREATE TABLE t2(

    -> id INT

    -> );

Query OK, 0 rows affected (0.00 sec)

 

含有一个简单的动态SQLSP

SQL>DELIMITER $$

SQL>CREATE PROCEDURE p1()

    -> BEGIN

    -> DECLARE v_max INT DEFAULT 0;

    -> DECLARE v_table VARCHAR(20)DEFAULT NULL;

    -> DECLARE v_sql VARCHAR(100) DEFAULT NULL;

    -> DECLARE stmt_sql VARCHAR(100) DEFAULT NULL;

    -> DECLARE stmt VARCHAR(100) DEFAULT NULL;

    ->

    -> WHILE v_max < 100 DO

    ->

    ->         SET v_sql = CONCAT('INSERT INTO t2 VALUES(',v_max,')',';');

    ->         SET @stmt_sql = v_sql;

    ->         PREPARE stmt FROM @stmt_sql;

    ->         EXECUTE stmt;

    ->         DEALLOCATE PREPARE stmt;

    ->         SET v_max = v_max + 1;

    ->

    -> END WHILE;

    -> END$$

Query OK, 0 rows affected (0.00 sec)

 

SQL>DELIMITER ;

 

 

 

创建一个调用p1的事件调度任务

SQL>CREATE EVENT e_t2_insert  ON SCHEDULE 

    -> EVERY 30 SECOND        

    -> STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE   

    -> ENDS CURRENT_TIMESTAMP + INTERVAL 11 MINUTE 

    -> ENABLE 

    -> COMMENT 't2 test' 

    -> DO CALL p1()

    -> ;

Query OK, 0 rows affected (0.00 sec)

 

 

等待11分钟后完成任务

该任务从创建后1分钟开始执行

创建后11分钟停止执行

执行频率是30

可以计算出这个表应该有2100行数据

 

看结果:

SQL>SELECT COUNT(1) AS 'single_count',id as 'sum_count' FROM t2 GROUP BY id;

+--------------+-----------+

| single_count | sum_count |

+--------------+-----------+

|           21 |         0 |

|           21 |         1 |

|           21 |         2 |

|           21 |         3 |

|           21 |         4 |

|           21 |         5 |

|           21 |         6 |

|           21 |         7 |

|           21 |         8 |

|           21 |         9 |

........................

 

 

执行完后改调度任务是被删除的,而不是DISABLE状态,所以调度队列为空,查看SHOW EVENTS也无e_t2_insert存在。

SQL>SHOW PROCESSLIST ;SHOW EVENTS;

+-------+-----------------+-----------+------+---------+------+------------------------+------------------+

| Id    | User            | Host      | db   | Command | Time | State                  | Info             |

+-------+-----------------+-----------+------+---------+------+------------------------+------------------+

| 15138 | event_scheduler | localhost | NULL | Daemon  |   36 | Waiting on empty queue | NULL             |

| 18458 | tangchao        | localhost | test | Query   |    0 | NULL                   | SHOW PROCESSLIST |

+-------+-----------------+-----------+------+---------+------+------------------------+------------------+

2 rows in set (0.00 sec)

 

+------+-------------+------------+-----------+-----------+------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+

| Db   | Name        | Definer    | Time zone | Type      | Execute at | Interval value | Interval field | Starts              | Ends | Status   | Originator | character_set_client | collation_connection | Database Collation |

+------+-------------+------------+-----------+-----------+------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+

| test | e_t1_insert | tangchao@% | SYSTEM    | RECURRING | NULL       | 10             | SECOND         | 2011-06-09 11:00:23 | NULL | DISABLED |        105 | gbk                  | gbk_chinese_ci       | gbk_chinese_ci     |

+------+-------------+------------+-----------+-----------+------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+

1 row in set (0.00 sec)

 

 

 

如果要使任务完成后不删除

需要增加ON COMPLETION  PRESERVE语句表示完成后保存它,以便日后备查。

 

SQL>CREATE EVENT e_t2_insert 

    ->

    -> ON SCHEDULE  

    -> EVERY 30 SECOND         

    ->

    -> STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE    

    -> ENDS CURRENT_TIMESTAMP + INTERVAL 11 MINUTE  

    ->

    -> ON COMPLETION  PRESERVE           

    -> ENABLE  

    ->

    -> COMMENT 't2 test'  

    ->

    -> DO

    -> CALL p1()

    -> ;

Query OK, 0 rows affected (0.00 sec)

 

表示调度队列里面有事件。

SQL>show processlist;

+-------+-----------------+-----------+------+---------+------+-----------------------------+------------------+

| Id    | User            | Host      | db   | Command | Time | State                       | Info             |

+-------+-----------------+-----------+------+---------+------+-----------------------------+------------------+

| 15138 | event_scheduler | localhost | NULL | Daemon  |    9 | Waiting for next activation | NULL             |

| 18458 | tangchao        | localhost | test | Query   |    0 | NULL                        | show processlist |

+-------+-----------------+-----------+------+---------+------+-----------------------------+------------------+

 

 

 

尽量使用定点计划任务,每次到达时间点开始执行

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

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2011-08-03

  • 博文量
    5
  • 访问量
    11952