An explanation of the usage of the event in MySQL

First, the basic concept
The mysql5.1 version begins with the introduction of the event concept. Event is not only a "time trigger", unlike the events triggered by the triggers, the event is similar to a Linux crontab scheduled task for time triggering. Trigger a related SQL statement or stored procedure at a specific point in time by using a separate or called stored procedure.

Ii. Scope of application
For operations that have fixed requirements at regular intervals, such as creating tables, deleting data, and so on, you can use event to handle it.

For example, use the event to automatically create three tables to be used next month on 1st 1 o'clock in the morning of the month.

Third, the use of rights
When invoking an SQL statement using event alone, viewing and creating requires the user to have the event permission, and when the SQL statement is called, the user is required to have permission to execute that SQL. The settings for the event permission are saved in the Event_priv field of the Mysql.user table and the Mysql.db table.

When event and procedure are used together, viewing and creating stored procedures requires the user to have the Create routine permission, and the call to the stored procedure executes with the Excute permission, which requires the user to have permission to execute the SQL statement when the stored procedure invokes it.
There are several types of view event commands:

(1) Inquiry mysql.event form;

(2) through the show events command;

(3) through the show full events command;

(4) by querying the Information_schema.events table

(5) SHOW CREATE EVENT.

In summary, the low frequency of event usage is recommended for root user creation and maintenance.

Iv. Basic Grammar

4.1 Turn on Timer
For the event to work, the MySQL constant global event_scheduler must be on or 1.
--Check whether the timer is turned on

Copy CodeThe code is as follows:
SHOW VARIABLES like ' Event_scheduler ';
--Open Timer 0:off 1:on
Copy CodeThe code is as follows:
SET GLOBAL event_scheduler = 1;
When you set the event plan to 0 or off, that is, when the event planning process is closed, no new events are executed, but the existing running events are executed to completion

For our online environment, when using the event, notice that the timer is turned on in the main library, the timer is turned off from the library, the event trigger all operations are recorded binlog for master-slave synchronization, and the timer opening from the library is likely to cause the card library. After switching the main library, remember to turn on the timer on the new Main library.
Please pay special attention!

4.2 Creating events
The syntax for CREATE EVENT is as follows:
CREATE EVENT
[IF not EXISTS]---------------------------------------------* Callout 1
Event_Name-----------------------------------------------------* Callout 2

On SCHEDULE SCHEDULE------------------------------------* Callout 3
[on completion [NOT] PRESERVE]-----------------* Callout 4
[ENABLE | DISABLE]----------------------------------------* Callout 5
[COMMENT ' COMMENT ']--------------------------------------* Callout 6
Do sql_statement-----------------------------------------------* Callout 7

Description:

callout 1:[if not EXISTS]

If not EXISTS is used, it is only created if the event with the same name does not exist, otherwise it is ignored. It is not recommended to ensure that event creation succeeds.

Callout 2:event_name

The maximum length of a name can be 64 bytes. The name must be unique in the current dateabase, and the same database cannot have an event with the same name.

Common work with event is to create tables, insert data, delete data, empty tables, and delete tables.

To avoid the inconvenience of naming the specification, it is best to have the event name have the ability to describe the entire event. The recommended naming rules are: Action name _ (into/from_) Table name _time, for example:
1. Create (empty/delete) Fans table every month:
Create (Truncate/drop) _table_fans_month;
2. Insert (delete) data from fans table daily:
Insert (delete) _into (from) _fans_day;
Callout 3:on SCHEDULE

on SCHEDULE Scheduled Tasks, there are two ways to set up scheduled tasks:

1. At timestamp, used to complete a single scheduled task.

2. Every time unit [starts timestamp] [ends timestamp], which is used to complete a recurring scheduled task.

In both scheduled tasks, the timestamp can be any of the timestamp and datetime data types, and the timestamp needs to be greater than the current time.

In a recurring scheduled task, the number of times (units) can be an integer of any non-null (NOT NULL), the time unit is the keyword: year,month,day,hour,minute or second.

Hint: Other time units are also legal such as: QUARTER, WEEK, Year_month,day_hour,day_minute,day_second,hour_minute,hour_second, Minute_second, It is not recommended to use these non-standard time units.

Callout 4: [on completion [NOT] PRESERVE]

The on completion parameter means "When this event does not recur", that is, when a single scheduled task is completed or when a recurring scheduled task is executed to the ends stage. The role of preserve is to allow the event to be dropped after execution, and it is recommended to use this parameter in order to view the event details.

Callout 5:[enable | DISABLE]
The parameter enable and disable indicate the state of the set event. Enable indicates that the system will execute this event. Disable indicates that the system does not execute the event.

The event can be closed or turned on with the following command:

ALTER EVENT Event_Name enable/disable
callout 6:[comment ' COMMENT ']

The comment appears in the metadata, which is stored in the comment column of the Information_schema table, with a maximum length of 64 bytes. ' Comment ' means placing comments between single quotes, and it is recommended to use annotations to express more comprehensive information.

Callout 7:do sql_statement

The Do sql_statement field represents the SQL statement or stored procedure that the event needs to execute. The SQL statement here can be a compound statement, for example:
BEGIN
CREATE table test1;//(need to test)
drop table test2;//Delete tables
Call Proc_test1 ();//Invoke stored procedure
END

Use the Begin and end identifiers to place the composite SQL statements between the execution order. Of course, the SQL statement is limited, and its limitations are the same as the function functions and trigger trigger in the SQL statement, if you do not use in function functions and trigger trigger some SQL, the same can not be used in the event. Clearly, there are several:

LOCK TABLES
UNLOCK TABLES
CREATE EVENT
ALTER EVENT
LOAD DATA

4.3 Execution logic
For (events created each event this has been created)
If (the state of the event is not disable)
and (current time before ends time)
and (the current time is after starts time)
and (elapsed time since last execution)
and (not executed)
Then:
To create a new thread
Pass the SQL statement of the event to the new thread
(The thread will close automatically when it finishes executing)

4.4 Modifying events
Modify the event using Alter event, with the specific alter syntax similar to the syntax for creating an event:
ALTER EVENT
Event_Name

On SCHEDULE SCHEDULE
[RENAME to New_event_name]
[on completion [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT ' COMMENT ']
Do sql_statement


4.5 Deleting events
Event using the Drop EVENT statement to delete the events that have been created, the syntax is as follows:

DROP EVENT
[IF EXISTS]
Event_Name


However, when an event is running, deleting the event does not cause the event to stop, and the event is executed until it finishes. Using the drop user and drop DATABASE statements will also delete the events contained therein.

V. Examples of common use
Automatically calls the e_test () stored procedure every second

Copy CodeThe code is as follows:
CREATE EVENT IF not EXISTS e_test
On SCHEDULE every 1 SECOND
On completion PRESERVE
Do call E_test ();
One 1 o'clock in the morning per month performs a stat () stored procedure:
Copy CodeThe code is as follows:
CREATE EVENT not EXISTS STAT
On SCHEDULE every 1 MONTH starts Date_add (Date_add (Date_sub (), Curdate Day (INTERVAL ()) – 1 day), Curdate 1 INTERVAL H), INTERVAL 1 HOUR)
On completion PRESERVE ENABLE
Do
BEGIN
Call STAT ();
END

Other than that:

When preserve is on completion, when the event expires, the event will be disable, but the event will still exist
When completion not preserve is on, the event is automatically deleted when the event expires.

Articles you may be interested in:

• The usage of the INSERT, UPDATE, delete, and replace statements in MySQL database
• MySQL Order by statement usage and optimization
• Explain usage in MySQL
• MySQL sql_mode= "" Role description
• The use of Replace in MySQL
• MySQL full-text search match against usage
• MySQL string intercept function substring usage description
• MySQL Replace function replaces the use of a string statement
• MySQL's SQL mode usage

An explanation of the usage of the event in MySQL