华山资源网

带你了解MySQL中的事件调度器EVENT
数据库 2024/12/23 佚名
3 1

MySQL中的事件调度器，EVENT，也叫定时任务，类似于Unix crontab或Windows任务调度程序。

EVENT由其名称和所在的schema唯一标识。

EVENT根据计划执行特定操作。操作由SQL语句组成，语句可以是BEGIN…END语句块。EVENT可以是一次性的，也可以是重复性的。一次性EVENT只执行一次，周期性EVENT以固定的间隔重复其操作，并且可以为周期性EVENT指定开始日期和时间、结束日期和时间。（默认情况下，定期EVENT在创建后立即开始，并无限期地继续，直到它被禁用或删除。）

EVENT由一个特殊的事件调度器线程执行，用SHOW PROCESSLIST可以查看。

root@database-one 13:44: [gftest]> show variables like '%scheduler%';
+-----------------+-------+
| Variable_name  | Value |
+-----------------+-------+
| event_scheduler | OFF  |
+-----------------+-------+
1 row in set (0.01 sec)

root@database-one 13:46: [gftest]> show processlist;
+--------+------+----------------------+-----------+---------+------+----------+------------------+
| Id   | User | Host         | db    | Command | Time | State  | Info       |
+--------+------+----------------------+-----------+---------+------+----------+------------------+
......
+--------+------+----------------------+-----------+---------+------+----------+------------------+
245 rows in set (0.00 sec)

root@database-one 13:46: [gftest]> set global event_scheduler=1;
Query OK, 0 rows affected (0.00 sec)

root@database-one 13:47: [gftest]> show variables like '%scheduler%';
+-----------------+-------+
| Variable_name  | Value |
+-----------------+-------+
| event_scheduler | ON  |
+-----------------+-------+
1 row in set (0.01 sec)

root@database-one 13:47: [gftest]> show processlist;
+--------+-----------------+----------------------+-----------+---------+------+------------------------+------------------+
| Id   | User      | Host         | db    | Command | Time | State         | Info       |
+--------+-----------------+----------------------+-----------+---------+------+------------------------+------------------+
......
| 121430 | event_scheduler | localhost      | NULL   | Daemon |  33 | Waiting on empty queue | NULL       |
......
+--------+-----------------+----------------------+-----------+---------+------+------------------------+------------------+
246 rows in set (0.01 sec)

可以看到，默认情况下，MySQL的EVENT没有打开，通过设置event_scheduler参数来打开或者关闭EVENT。打开后就会多一个event_scheduler，这个就是事件调度器线程。

除了打开和关闭，还可以禁用，要禁用EVENT，请使用以下两种方法之一：

  • 启动MySQL时用命令行参数

--event-scheduler=DISABLED

  • 在MySQL配置文件中配置参数

event_scheduler=DISABLED

MySQL 5.7中创建EVENT的完整语法如下：

CREATE
  [DEFINER = user]
  EVENT
  [IF NOT EXISTS]
  event_name
  ON SCHEDULE schedule
  [ON COMPLETION [NOT] PRESERVE]
  [ENABLE | DISABLE | DISABLE ON SLAVE]
  [COMMENT 'string']
  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}

详细说明可以参考官网https://dev.mysql.com/doc/refman/5.7/en/create-event.html

我们通过一个实例来验证下。
1）创建一张表。

root@database-one 13:47: [gftest]> create table testevent(id int auto_increment primary key,create_time datetime);
Query OK, 0 rows affected (0.01 sec)

root@database-one 13:50: [gftest]> select * from testevent;
Empty set (0.00 sec)

2）创建一个EVENT，每3秒往表中插一条记录。

root@database-one 13:50: [gftest]> create event insert_date_testevent on schedule every 3 second do
  -> insert into testevent(create_time) values(now());
Query OK, 0 rows affected (0.01 sec)

root@database-one 13:53: [gftest]> show events \G
*************************** 1. row ***************************
         Db: gftest
        Name: insert_date_testevent
       Definer: root@%
      Time zone: +08:00
        Type: RECURRING
     Execute at: NULL
   Interval value: 3
   Interval field: SECOND
       Starts: 2020-03-26 13:53:10
        Ends: NULL
       Status: ENABLED
     Originator: 1303306
character_set_client: utf8
collation_connection: utf8_general_ci
 Database Collation: utf8_general_ci
1 row in set (0.00 sec)

3）过一会，去表中查询数据。

root@database-one 13:53: [gftest]> select * from testevent;
+----+---------------------+
| id | create_time     |
+----+---------------------+
| 1 | 2020-03-26 13:53:10 |
| 2 | 2020-03-26 13:53:13 |
| 3 | 2020-03-26 13:53:16 |
| 4 | 2020-03-26 13:53:19 |
| 5 | 2020-03-26 13:53:22 |
| 6 | 2020-03-26 13:53:25 |
| 7 | 2020-03-26 13:53:28 |
| 8 | 2020-03-26 13:53:31 |
| 9 | 2020-03-26 13:53:34 |
| 10 | 2020-03-26 13:53:37 |
| 11 | 2020-03-26 13:53:40 |
| 12 | 2020-03-26 13:53:43 |
| 13 | 2020-03-26 13:53:46 |
| 14 | 2020-03-26 13:53:49 |
| 15 | 2020-03-26 13:53:52 |
| 16 | 2020-03-26 13:53:55 |
+----+---------------------+
16 rows in set (0.00 sec)

从表里数据可以看到，创建的插数定时任务已经在正常运行了。

EVENT的详细信息除了用show event命令，还可以从mysql.event或information_schema.events中查询，也可以用show create event命令查看。

root@database-one 00:09: [gftest]> select * from mysql.event \G
*************************** 1. row ***************************
         db: gftest
        name: insert_date_testevent
        body: insert into testevent(create_time) values(now())
       definer: root@%
     execute_at: NULL
   interval_value: 3
   interval_field: SECOND
       created: 2020-03-26 13:53:10
      modified: 2020-03-26 13:53:10
    last_executed: 2020-03-26 16:09:37
       starts: 2020-03-26 05:53:10
        ends: NULL
       status: ENABLED
    on_completion: DROP
      sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
       comment:
     originator: 1303306
      time_zone: +08:00
character_set_client: utf8
collation_connection: utf8_general_ci
    db_collation: utf8_general_ci
      body_utf8: insert into testevent(create_time) values(now())
1 row in set (0.00 sec)

root@database-one 00:09: [gftest]> select * from information_schema.events \G
*************************** 1. row ***************************
    EVENT_CATALOG: def
    EVENT_SCHEMA: gftest
     EVENT_NAME: insert_date_testevent
       DEFINER: root@%
      TIME_ZONE: +08:00
     EVENT_BODY: SQL
  EVENT_DEFINITION: insert into testevent(create_time) values(now())
     EVENT_TYPE: RECURRING
     EXECUTE_AT: NULL
   INTERVAL_VALUE: 3
   INTERVAL_FIELD: SECOND
      SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
       STARTS: 2020-03-26 13:53:10
        ENDS: NULL
       STATUS: ENABLED
    ON_COMPLETION: NOT PRESERVE
       CREATED: 2020-03-26 13:53:10
    LAST_ALTERED: 2020-03-26 13:53:10
    LAST_EXECUTED: 2020-03-27 00:10:22
    EVENT_COMMENT:
     ORIGINATOR: 1303306
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
 DATABASE_COLLATION: utf8_general_ci
1 row in set (0.02 sec)

root@database-one 00:10: [gftest]> show create event insert_date_testevent \G
*************************** 1. row ***************************
        Event: insert_date_testevent
      sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
      time_zone: +08:00
    Create Event: CREATE DEFINER=`root`@`%` EVENT `insert_date_testevent` ON SCHEDULE EVERY 3 SECOND STARTS '2020-03-26 13:53:10' ON COMPLETION NOT PRESERVE ENABLE DO insert into testevent(create_time) values(now())
character_set_client: utf8
collation_connection: utf8_general_ci
 Database Collation: utf8_general_ci
1 row in set (0.00 sec)

以上就是带你了解MySQL中的事件调度器EVENT的详细内容，更多关于MySQL 事件调度器EVENT的资料请关注其它相关文章！

华山资源网 Design By www.eoogi.com
广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！
华山资源网 Design By www.eoogi.com
www.eoogi.com 华山资源网
120,135影音资源
344,641技术资源
22,817软件资源
435,032站长资源

更新日志

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接
华山资源网 Design By www.eoogi.com