mysql的计划任务与事件调度实例分析

本文实例讲述了mysql的计划任务与事件调度。分享给大家供大家参考，具体如下：

mysql事件是基于预定义的时间表运行的任务，因此有时它被称为预定事件。mysql事件也被称为“时间触发”，因为它是由时间触发的，而不是像触发器一样更新表来触发的。mysql事件类似于UNIX中的cron作业或Windows中的任务调度程序。我们可以在优化数据库表，清理日志，归档数据或在非高峰时间生成复杂的报告的时候使用mysql事件。

mysql使用一个名为事件调度线程的特殊线程来执行所有调度的事件。我们可以通过执行以下命令来查看事件调度程序线程的状态：

SHOW PROCESSLIST;

mysql> SHOW PROCESSLIST;+----+------+-----------------+----------+---------+------+----------+------------------+| Id | User | Host      | db    | Command | Time | State  | Info       |+----+------+-----------------+----------+---------+------+----------+------------------+| 2 | root | localhost:50405 | NULL   | Sleep  | 1966 |     | NULL       || 3 | root | localhost:50406 | yiibaidb | Sleep  | 1964 |     | NULL       || 4 | root | localhost:50407 | yiibaidb | Query  |  0 | starting | SHOW PROCESSLIST |+----+------+-----------------+----------+---------+------+----------+------------------+3 rows in set

SET GLOBAL event_scheduler = ON;

mysql> SHOW PROCESSLIST;+----+-----------------+-----------------+----------+---------+------+------------------------+------------------+| Id | User      | Host      | db    | Command | Time | State         | Info       |+----+-----------------+-----------------+----------+---------+------+------------------------+------------------+| 2 | root      | localhost:50405 | NULL   | Sleep  | 1986 |            | NULL       || 3 | root      | localhost:50406 | luyaran | Sleep  | 1984 |            | NULL       || 4 | root      | localhost:50407 | luyaran | Query  |  0 | starting        | SHOW PROCESSLIST || 5 | event_scheduler | localhost    | NULL   | Daemon |  6 | Waiting on empty queue | NULL       |+----+-----------------+-----------------+----------+---------+------+------------------------+------------------+4 rows in set

SET GLOBAL event_scheduler = OFF;

CREATE EVENT [IF NOT EXIST] event_nameON SCHEDULE scheduleDOevent_body

首先，在CREATE EVENT子句之后指定事件名称。事件名称在数据库模式中必须是唯一的。

其次，在ON SCHEDULE子句后面加上一个表。如果事件是一次性事件，则使用语法：AT timestamp [+ INTERVAL]，如果事件是循环事件，则使用EVERY子句：EVERY interval STARTS timestamp [+INTERVAL] ENDS timestamp [+INTERVAL]

第三，将DO语句放在DO关键字之后。请注意，可以在事件主体内调用存储过程。如果您有复合SQL语句，可以将它们放在BEGIN END块中。

我们先来创建一个messages表来做演示：

CREATE TABLE IF NOT EXISTS messages (  id INT PRIMARY KEY AUTO_INCREMENT,  message VARCHAR(255) NOT NULL,  created_at DATETIME NOT NULL);

CREATE EVENT IF NOT EXISTS test_event_01ON SCHEDULE AT CURRENT_TIMESTAMPDO INSERT INTO messages(message,created_at) VALUES('Test MySQL Event 1',NOW());

mysql> SELECT * FROM messages;+----+--------------------+---------------------+| id | message      | created_at     |+----+--------------------+---------------------+| 1 | Test MySQL Event 1 | 2017-08-03 04:23:11 |+----+--------------------+---------------------+1 row in set

SHOW EVENTS FROM testdb;

CREATE EVENT test_event_02ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTEON COMPLETION PRESERVEDO  INSERT INTO messages(message,created_at)  VALUES('Test MySQL Event 2',NOW());

mysql> SELECT * FROM messages;+----+--------------------+---------------------+| id | message      | created_at     |+----+--------------------+---------------------+| 1 | Test MySQL Event 1 | 2017-08-03 04:23:11 || 2 | Test MySQL Event 2 | 2017-08-03 04:24:48 |+----+--------------------+---------------------+2 rows in set

mysql> SHOW EVENTS FROM testdb;+--------+---------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+| Db   | Name     | Definer    | Time zone | Type   | Execute at     | Interval value | Interval field | Starts | Ends | Status  | Originator | character_set_client | collation_connection | Database Collation |+--------+---------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+| testdb | test_event_02 | root@localhost | SYSTEM  | ONE TIME | 2017-08-03 04:24:48 | NULL      | NULL      | NULL  | NULL | DISABLED |     0 | utf8         | utf8_general_ci   | utf8_general_ci  |+--------+---------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+1 row in set

CREATE EVENT test_event_03ON SCHEDULE EVERY 1 MINUTESTARTS CURRENT_TIMESTAMPENDS CURRENT_TIMESTAMP + INTERVAL 1 HOURDO  INSERT INTO messages(message,created_at)  VALUES('Test MySQL recurring Event',NOW());

mysql> SELECT * FROM messages;+----+----------------------------+---------------------+| id | message          | created_at     |+----+----------------------------+---------------------+| 1 | Test MySQL Event 1     | 2017-08-03 04:23:11 || 2 | Test MySQL Event 2     | 2017-08-03 04:24:48 || 3 | Test MySQL recurring Event | 2017-08-03 04:25:20 || 4 | Test MySQL recurring Event | 2017-08-03 04:26:20 || 5 | Test MySQL recurring Event | 2017-08-03 04:27:20 |+----+----------------------------+---------------------+5 rows in set

DROP EVENT [IF EXISTS] event_name;

DROP EVENT IF EXISTS test_event_03;

更多关于MySQL相关内容感兴趣的读者可查看本站专题：《MySQL索引操作技巧汇总》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》

希望本文所述对大家MySQL数据库计有所帮助。

更多相关文章

随机推荐