Take you to understand the event scheduler EVENT in MySQL

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

Recommend

Detailed tutorial for installing ElasticSearch:7.8.0 cluster with docker

ElasticSearch cluster supports動態請求的方式and靜態配置文件to ...

Introduction to TypeScript interfaces

Table of contents 1. Interface definition 2. Attr...

Don't forget to close the HTML tag

Building web pages that comply with Web standards ...

MySQL Optimization Solution Reference

Problems that may arise from optimization Optimiz...

The difference between Div and table in HTML (discussed in detail in all aspects)

1: Differences in speed and loading methods The di...

A brief discussion of the interesting box model of CSS3 box-sizing property

Everyone must know the composition of the box mod...

Time zone issues with Django deployed in Docker container

Table of contents Time zone configuration in Djan...

CSS code to distinguish ie8/ie9/ie10/ie11 chrome firefox

Website compatibility debugging is really annoyin...

Linux system (Centos6.5 and above) installation jdk tutorial analysis

Article Structure 1. Preparation 2. Install Java ...

How to implement rounded corners with CSS3 using JS

I found an example when I was looking for a way t...

The role and opening of MySQL slow query log

Preface The MySQL slow query log is a type of log...

Website design should pay attention to the sense of color hierarchy

Recently I have been saying that design needs to h...

Vue integrates Tencent TIM instant messaging

This article mainly introduces how to integrate T...

An article to teach you HTML

If you are not committed to becoming an artist, t...

How to install ELK in Docker and implement JSON format log analysis

What is ELK? ELK is a complete set of log collect...