欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

带你了解MySQL中的事件调度器EVENT

程序员文章站 2022-04-16 22:40:05
mysql中的事件调度器,event,也叫定时任务,类似于unix crontab或windows任务调度程序。event由其名称和所在的schema唯一标识。event根据计划执行特定操作。操作由s...

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}

详细说明可以参考官网

我们通过一个实例来验证下。
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的资料请关注其它相关文章!