Shulou(Shulou.com)05/31 Report--

This article will explain in detail how the event scheduler EVENT in MySQL is, and the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

The event scheduler EVENT in MySQL can trigger certain operations in the database according to the set time period, similar to the scheduled task mechanism.

The event scheduler in MySQL, EVENT, also known as timing tasks, is similar to the Unix crontab or Windows task scheduler.

The EVENT is uniquely identified by its name and the schema in which it resides.

EVENT performs specific actions according to the plan. The operation consists of SQL statements, which can be BEGIN. END statement block. EVENT can be disposable or repetitive. The one-time EVENT is performed only once, the periodic EVENT repeats its operation at regular intervals, and the start date and time, end date and time can be specified for the periodic EVENT. By default, a periodic EVENT starts immediately after it is created and continues indefinitely until it is disabled or deleted. )

The EVENT is executed by a special event scheduler thread, which can be viewed with SHOW PROCESSLIST.

Root@database-one 13:44: [gftest] > show variables like'% scheduler%' +-+-+ | Variable_name | Value | +-+-+ | event_scheduler | OFF | +-+-+ 1 row in set (0.01sec) 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.01sec) 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)

As you can see, by default, MySQL's EVENT is not turned on, and you can turn EVENT on or off by setting the event_scheduler parameter. When opened, there will be one more event_scheduler, which is the event scheduler thread.

In addition to turning it on and off, you can also disable it. To disable EVENT, use one of the following two methods:

Use command line parameters when starting MySQL

-- event-scheduler=DISABLED

Configure parameters in the MySQL configuration file

Event_scheduler=DISABLED

The complete syntax for creating an EVENT in MySQL 5.7is as follows:

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}

For more information, please refer to the official website https://dev.mysql.com/doc/refman/5.7/en/create-event.html.

Let's verify it with an example.

1) create a table.

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) create an EVENT and insert a record into the table every 3 seconds.

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\ gateway * 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: 1303306character_set_client: utf8collation_connection: utf8_general_ci Database Collation: utf8_general_ci1 row in set (2020 sec)

3) after a while, query the data in the table.

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 1353 position | 43 | 13 | 2020-03-26 1353 range | 14 | 2020-03-26 | 26 13:53:49 | | 15 | 0-03-26 13:53:52 | | 16 | 0-03-26 13:53:55 | +-+-+ 16 rows in set (2020 sec)

As you can see from the data in the table, the created insertion timing task is already running normally.

In addition to using the show event command, the details of EVENT can also be queried from mysql.event or information_schema.events, or can be viewed with the show create event command.

Root@database-one 00:09: [gftest] > select * from mysql.event\ gateway * 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:00character_set_client: utf8collation_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\ Gaming activities * * * 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: 1303306CHARACTER_SET _ CLIENT: utf8COLLATION_CONNECTION: utf8_general_ci DATABASE_COLLATION: utf8_general_ci1 row in set (0.02 sec) root@database-one 00:10: [gftest] > show create event insert_date_testevent\ gateway * 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 1353 ON COMPLETION NOT PRESERVE ENABLE DO insert into testevent (create_time) values (now ()) character_set_client: utf8collation_connection: utf8_general_ci Database Collation: utf8_general_ci1 row in set (0.00 sec) about the event scheduler EVENT in MySQL, that's all. I hope the above content can be of some help to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.