带你了解MySQL中的事件调度器EVENT
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的资料请关注其它相关文章!
上一篇: *逗B,谁看谁爆笑!
下一篇: 入局社区团购赛道,美团胜算几何?
推荐阅读
-
老生常谈mysql event事件调度器(必看篇)
-
带你了解MySQL中的事件调度器EVENT
-
js在浏览器中的event loop事件队列示例详解
-
老生常谈mysql event事件调度器(必看篇)
-
MySQL事件调度器Event Scheduler详解
-
[MySQL 5.1 体验]MySQL 事件调度器(Event Scheduler)
-
MySQL事件调度器Event Scheduler详解_MySQL
-
MySQL事件调度器Event Scheduler详细解说_MySQL
-
Mysql Linux安装和事件调度器(Event Scheduler)
-
[MySQL 5.1 体验]MySQL 事件调度器(Event Scheduler)