mysql event

最新推荐文章于 2021-01-27 14:08:15 发布

congza7488

最新推荐文章于 2021-01-27 14:08:15 发布

阅读量106

点赞数

[event schedule]

[事件调度]

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

作者：tc-ql@163.com

1. Event_scheduler

缺省状态event schedule没有开启

SQL>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;

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

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

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

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 |

DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

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

需要重复执行的要制定every，every表示执行频率，并且要实现某种频率还需要搭配starts和ends来限制。

当前才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)

含有一个简单的动态SQL的SP。

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;

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

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

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

2 rows in set (0.00 sec)

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