Take you to understand the event scheduler EVENT in MySQL

The event scheduler in MySQL, EVENT, is also called a scheduled task, similar to Unix crontab or Windows task scheduler.

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

EVENT performs specific actions according to a schedule. An operation consists of SQL statements, which can be a BEGIN...END statement block. An EVENT can be one-time or recurring. A one-time EVENT is executed only once, while a periodic EVENT repeats its operation at fixed intervals. You can specify the start date and time, and the end date and time for a periodic EVENT. (By default, a recurring event begins immediately after it is created and continues indefinitely until it is disabled or deleted.)

EVENT is executed by a special event scheduler thread, which can be viewed using 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)

As you can see, by default, MySQL's EVENT is not turned on. You can turn EVENT on or off by setting the event_scheduler parameter. After opening it, there will be an additional event_scheduler, which is the event scheduler thread.

In addition to opening and closing, you can also disable. To disable an EVENT, use one of the following two methods:

Start MySQL with command line parameters

--event-scheduler=DISABLED

Configure parameters in the MySQL configuration file

event_scheduler=DISABLED

The complete syntax for creating an EVENT in MySQL 5.7 is 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 detailed instructions, please refer to the official website https://dev.mysql.com/doc/refman/5.7/en/create-event.html

Let's verify this through 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 \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) 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 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)

From the data in the table, we can see that the created insertion timer task is running normally.

In addition to using the show event command, you can also query the detailed information of EVENT from mysql.event or information_schema.events, or use the show create event command to view it.

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)

The above is the detailed content of the event scheduler EVENT in MySQL. For more information about the MySQL event scheduler EVENT, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:

Getting Started Guide for MySQL Stored Procedures, Triggers, and Event Schedulers
MySQL database triggers from beginner to proficient
Detailed explanation of MySQL trigger trigger example
Introduction to the use and advantages and disadvantages of MySQL triggers
Detailed explanation of the idea of MySQL trigger detecting a statement in real time for backup and deletion
MySQL uses events to complete scheduled tasks
How to configure MySQL scheduled tasks (EVENT events) in detail
Analysis of MySQL's planned tasks and event scheduling examples
MySQL events and triggers topic refinement

<<: Detailed explanation of Js class construction and inheritance cases

>>: Detailed steps to install web server using Apache httpd2.4.37 on centos8