Detailed explanation of mysql timing task (event event) implementation 03/28 Update SLTechnology News&Howtos

Detailed explanation of mysql timing task (event event) implementation

2025-03-28 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

1. Brief introduction of event

Events (event) are procedural database objects that MySQL calls at the appropriate time. An event can be called once or started periodically, and it is managed by a specific thread, which is called the event Scheduler.

Events, like triggers, are started when something happens. When a statement is started on the database, the trigger starts, and the event starts based on the scheduled event. Because they are similar to each other, events are also called temporary triggers.

Events replace work that can only be performed by the scheduled tasks of the operating system, and MySQL's event Scheduler can be accurate to execute one task per second, while the scheduled tasks of the operating system (such as CRON under Linux or task scheduling under Windows) can only be executed once per minute.

2 advantages and disadvantages of events

2.1 benefits

Some data timed operations no longer rely on external programs, but directly use the functions provided by the database itself.

It can execute one task per second, which is very practical in some environments with high real-time requirements.

2.2 shortcomings

It is triggered regularly and cannot be called.

3 create an event

A create event statement creates an event. Each event consists of two main parts. the first part is event scheduling (eventschedule), which indicates when and how often the event starts.

The second part is the event action (event action), which is the code executed when the event starts. The action of the event contains a SQL statement, which may be a simple insert or update statement, or it can make a stored procedure or

Benin...end statement block, both of which allow us to execute multiple SQL.

An event can be active (open) or stopped (closed), which means that the event scheduler checks whether the event action must be called, and stopping means that the declaration of the event is stored in the directory, but the scheduler does not check whether it should be called. Immediately after an event is created, it becomes active, and an active event can be executed one or more times.

3.1 the creation syntax is as follows

CREATE [DEFINER = {user | CURRENT_USER}] EVENT [IF NOT EXISTS] event_name ON SCHEDULE schedule [ON COMPLETION [NOT] PRESERVE] [ENABLE | DISABLE | DISABLE ON SLAVE] [COMMENT 'comment'] 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_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

The noun explains:

Event_name: the name of the event created (the only one that is determined).

ON SCHEDULE: schedule tasks.

Schedule: determines the time and frequency of event execution (note that the time must be in the future, the past time can go wrong), there are two forms of AT and EVERY.

[ON COMPLETION [NOT] PRESERVE]: optional. The default is ON COMPLETION NOT PRESERVE, that is, the event is automatically drop after the scheduled task is executed; ON COMPLETION PRESERVE does not drop.

[COMMENT 'comment']: optional, comment is used to describe event; rather annotated with a maximum length of 64 bytes.

[ENABLE | DISABLE]: set the status of event. Default ENABLE: the system attempts to execute this event. DISABLE: close the event. You can modify it with alter.

DO event_body: the sql statement (which can be a compound statement) to be executed. CREATE EVENT is legal when used in stored procedures.

3.2 enable and close event Scheduler

3.2.1 MySQL event Scheduler event_scheduler is responsible for invoking events, which is turned off by default. This scheduler constantly monitors whether an event is to be called, and to create an event, you must open the scheduler.

Mysql > show variables like'% event_scheduler%'; +-+-+ | Variable_name | Value | +-+-+ | event_scheduler | OFF | +-+-+

3.2.2 Open event Scheduler

Through the command line

You can use any of the following command lines

SET GLOBAL event_scheduler = ON; SET @ @ global.event_scheduler = ON; SET GLOBAL event_scheduler = 1; SET @ @ global.event_scheduler = 1

Through the configuration file my.cnf

Event_scheduler = 1 # or ON

View the scheduler thread

3.2.3 close event Scheduler

Through the command line

You can use any of the following command lines

SET GLOBAL event_scheduler = OFF; SET @ @ global.event_scheduler = OFF; SET GLOBAL event_scheduler = 0; SET @ @ global.event_scheduler = 0

Through the configuration file my.cnf

Add under [mysqld]

Event_scheduler = 0 # or OFF,DISABLED

View the scheduler thread

3.3 for example: create a table to record the name and event stamp of each event schedule

3.3.1 create a test table

Mysql > drop table if exists events_list; mysql > create table events_list (event_name varchar (20) not null, event_started timestamp not null)

3.3.2 create event 1 (start event immediately)

Create event event_now on schedule at now () do insert into events_list values ('event_now', now ())

View the result of event execution

Mysql > select * from events_list +-+-+ | event_name | event_started | +-+-+ | event_now | 2014-07-01 04:06:40 | +-- -+

3.3.3 create event 2 (start event per minute)

Create event test.event_minute on schedule every 1 minute do insert into events_list values ('event_now', now ())

View the result of event execution

Mysql > select * from events_list +-+-+ | event_name | event_started | +-+-+ | event_now | 2014-07-01 04:26:53 | | event_now | 2014-07-01 04:27:53 | | event_now | 2014 -07-01 04:28:53 | +-

3.3.3 create event 3 (start event per second)

CREATE event event_now ON SCHEDULE EVERY 1 SECONDDO INSERT INTO event_test VALUES (1)

3.3.4 create event 4 (call stored procedures per second)

CREATE DEFINER= `root` @ `localhost` EVENT `eventUpdateStatus` ON SCHEDULE EVERY 1 SECOND STARTS '2017-11-21 00 ON SCHEDULE EVERY 12 SECOND STARTS 44' ON COMPLETION PRESERVE ENABLE DO call updateStatus ()

3.4 Note:

The default creation event is stored in the current library, and you can also display the library in which the specified event is created

Only events created in the current library can be viewed through show events

The event is released as soon as it is executed. If the event is executed immediately, the event will be deleted automatically, and it can be seen by calling the event several times or waiting for the event to be executed.

If two events need to be called at the same time, mysql determines the order in which they are called, and if you want to specify the order, you need to ensure that one event is executed at least 1 second after the other event

For recursively scheduled events, the end date cannot be before the start date.

Select can be included in an event, but its result disappears as if it had never been executed.

4 View events

View events in the current library

Mysql > show events

View all events

Mysql > select * from mysql.event

The above is the whole content of this article, I hope it will be helpful to your study, and I also hope that you will support it.

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.