带你了解MySQL中的事件调度器EVENT
作者:数据和云 时间:2024-01-18 05:55:16
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)
来源:https://cloud.tencent.com/developer/article/1608131
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
python 将字符串中的数字相加求和的实现
![](https://img.aspxhome.com/file/2023/4/66004_0s.png)
MySQL中varchar和char类型的区别
![](https://img.aspxhome.com/file/2023/3/129903_0s.png)
python之PyMongo使用总结
JavaScript实现秒杀时钟倒计时
![](https://img.aspxhome.com/file/2023/6/126176_0s.jpg)
Python实战之制作天气查询软件
![](https://img.aspxhome.com/file/2023/7/128207_0s.gif)
js 混淆加密工具代码
mysql 5.5.27 winx64安装配置方法图文教程
![](https://img.aspxhome.com/file/2023/8/116778_0s.jpg)
Centos部署django服务nginx+uwsgi的方法
SQLServer 循环批处理
Python字体反爬实战案例分享
![](https://img.aspxhome.com/file/2023/9/75929_0s.png)
mysql判断当前时间是否在开始与结束时间之间且开始与结束时间允许为空
![](https://img.aspxhome.com/file/2023/1/84541_0s.png)
离线状态下在jupyter notebook中使用plotly实例
![](https://img.aspxhome.com/file/2023/6/65466_0s.jpg)
python requests.post带head和body的实例
js实现axios限制请求队列
python自动化测试之如何解析excel文件
Python创建字典的八种方式
Opencv图像添加椒盐噪声、高斯滤波去除噪声原理以及手写Python代码实现方法
![](https://img.aspxhome.com/file/2023/9/88949_0s.jpg)
Win10系统下Pytorch环境的搭建过程
![](https://img.aspxhome.com/file/2023/1/112371_0s.png)
Vue mock.js模拟数据实现首页导航与左侧菜单功能
![](https://img.aspxhome.com/file/2023/1/127171_0s.png)
Python Unittest自动化单元测试框架详解
![](https://img.aspxhome.com/file/2023/2/67822_0s.png)