Usage of event in mysql _ MySQL

Usage of event in mysql

I. Basic concepts
The event concept was introduced in MySQL. The event is a time trigger, which is different from triggers event triggering. The event is similar to a linux crontab scheduled task for time triggering. It can be used independently or by calling a stored procedure to trigger related SQL statements or stored procedures at a specific time point.

II. applicability
You can use event to process operations that have fixed requirements at intervals, such as creating tables and deleting data.

For example, use event to automatically create three tables to be used for the next month at on the first day of each month.

3. permission
When you use event to call an SQL statement separately, you must have the event permission to view and create the statement. when you call the SQL statement, you must have the permission to execute the SQL statement. The Event permission settings are stored in the Event_priv field of the mysql. user table and mysql. db table.

When event and procedure are used in combination, you must have the create routine permission to view and create a stored procedure. when calling a stored procedure for execution, you must use the excute permission. when a stored procedure calls a specific SQL statement, you must have the permission to execute this SQL statement.
The EVENT command has the following types:

(1) query the mysql. event table;

(2) run the show events command;

(3) run the show full events command;

(4) query the information_schema.events table

(5) show create event.

In short, we recommend that you use the root user to create and maintain events more frequently.

IV. Basic syntax

4.1 enable timer
To make the event take effect, the constant GLOBAL event_scheduler of MySQL must be on or 1.
-- Check whether the timer is enabled

Show variables like 'event _ schedount ';

-- Enable the timer 0: off 1: on

Set global event_scheduler = 1;

When you set the event plan to 0 or OFF, that is, when you close the event plan process, no new event will be executed, but the existing running event will be executed until it is completed.

For our online environment, when using event, note that the timer is enabled on the master database, and the timer is disabled from the Slave Database. if event triggers all operations, binlog is recorded for master-slave synchronization, enabling the timer on the slave database may cause the card Library. After switching the master database, remember to open the timer on the new master database.
Please pay special attention!

4.2 create an event
The create event syntax is as follows:
CREATE EVENT
[If not exists] ------------------------------------------------- * Mark 1
Event_name ------------------------------------------------------- * Mark 2

On schedule schedule ------------------------------------ * Mark 3
[On completion [NOT] PRESERVE] ----------------- * Mark 4
[ENABLE | DISABLE] ------------------------------------------ * Mark 5
[COMMENT 'comment'] ------------------------------------------ * Mark 6
DO SQL _statement --------------------------------------------------- * Mark 7

Note:

Note 1: [if not exists]

If not exists is used, it is created only when the event with the same name does NOT exist. Otherwise, it is ignored. We recommend that you do not make sure that the event is successfully created.

Note 2: event_name

The maximum name length can be 64 bytes. The name must be unique in the current Dateabase. the same database cannot have an event with the same name.

Common tasks of using event are to create tables, insert data, delete data, clear tables, and delete tables.

To avoid the inconvenience caused by naming conventions, it is recommended that the event name be able to describe the entire event. The recommended naming rules are as follows: action name_( INTO/FROM _) table name_time, for example:
1. create (clear/delete) the fans table every month:
Create (truncate/drop) _ table_fans_month;
2. insert (delete) data from the fans table every day:
Insert (delete) _ into (from) _ fans_day;
Note 3: ON SCHEDULE

You can SCHEDULE a task on schedule in either of the following ways:

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

2. the number and time unit of EVERY time (unit) [STARTS timestamp] [ENDS timestamp] to complete repeated scheduled tasks.

In two scheduled tasks, the TIMESTAMP can be of any TIMESTAMP or DATETIME data type. the TIMESTAMP must be greater than the current time.

In a repeated scheduled task, the number of time (unit) can be an integer of any non-Null value. The time unit is key words: YEAR, MONTH, DAY, HOUR, MINUTE or SECOND.

Note: Other time units are also valid, such as QUARTER, WEEK, YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, HOUR_MINUTE, HOUR_SECOND, and MINUTE_SECOND. We do not recommend using these nonstandard time units.

Note 4: [on completion [NOT] PRESERVE]

The on completion parameter indicates "when this event does not occur again", that is, when a single scheduled task is completed or when a repetitive scheduled task is executed to the ENDS stage. PRESERVE is used to prevent the EVENT from being dropped after execution. we recommend that you use this parameter to view the EVENT details.

Note 5: [ENABLE | DISABLE]
The Enable and Disable parameters indicate setting the event status. Enable indicates that the system will execute this event. Disable indicates that the system does not execute this event.

You can use the following command to close or enable the event:

Alter event event_name ENABLE/DISABLE
Note 6: [COMMENT 'comment']

The COMMENT appears in the metadata, which is stored in the COMMENT column of the information_schema table. The maximum length is 64 bytes. 'Comment' indicates that the comment content is placed between single quotes. comments are recommended to indicate more comprehensive information.

Note 7: DO SQL _statement

The DO SQL _statement field indicates the SQL statement or stored procedure to be executed for the event. The SQL statement can be a compound statement, for example:
BEGIN
Create table test1; // CREATE a TABLE (test required)
Drop table test2; // delete a TABLE
CALL proc_test1 (); // CALL the stored procedure
END

Use the BEGIN and END identifiers to place the compound SQL statement in the execution order. Of course, SQL statements have restrictions, which are the same as the restrictions on SQL statements in Function functions and Trigger triggers, if you cannot use certain SQL statements in Function functions and Trigger triggers, you cannot use them in the same EVENT. Specifically, there are the following:

LOCK TABLES
UNLOCK TABLES
CREATE EVENT
ALTER EVENT
LOAD DATA

4.3 execution logic
For (the event each event that has been created)
If (the event status is not DISABLE)
And (the current time is before the ENDS time)
And (the current time is after STARTS Time)
And (the time after the last execution)
And (not executed)
Then:
Create a new thread
Pass the SQL statement of the event to the new thread
(This thread will be automatically closed after execution is completed)

4.4 modify an event
Use alter event to modify the EVENT. the specific ALTER syntax is as follows, which is similar to the syntax for EVENT creation:
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 delete an event
EVENT uses the drop event statement to delete the created EVENT. The syntax is as follows:

DROP EVENT
[If exists]
Event_name


However, when an event is running, deleting the event will not stop the event and the event will be executed until it is completed. When you use the drop user and drop database statements, the events contained in the statements are deleted.

5. common instances
The e_test () stored procedure is automatically called every second.

Create event if not exists e_test
On schedule every 1 SECOND
ON COMPLETION PRESERVE
Do call e_test ();

Execute the STAT () stored procedure at on the first day of each month:

CREATE EVENT NOT EXISTS STAT
On schedule every 1 month starts DATE_ADD (DATE_SUB (CURDATE (), interval day (CURDATE ()-1 DAY), INTERVAL 1 MONTH), INTERVAL 1 HOUR)
ON COMPLETION PRESERVE ENABLE
DO
BEGIN
Call stat ();
END

BitsCN.com