mysql中事件调度_带你了解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的资料请关注萬仟网其它相关文章！

如您对本文有疑问或者有任何想说的，请点击进行留言回复，万千网友为您解惑！