1. Brief introduction of events
Event is a procedural database object that MySQL calls at the corresponding time. An event can be called once or started periodically. It is managed by a specific thread, which is called "event scheduler".
Events, like triggers, start when something happens. When a statement is started on the database, the trigger starts, and the event is started based on the scheduling event. Because they are similar to each other, events are also called temporary triggers.
Events take the place of tasks originally scheduled by the operating system, and MySQL's event scheduler can execute one task per second, while the scheduled tasks of the operating system (such as CRON under Linux or task planning under Windows) can only execute once per minute.
2. Advantages and disadvantages of events
2.1 Advantages
Some data timing operations no longer rely on external programs, but directly use the functions provided by the database itself.
It can accomplish one task per second, which is very practical in some environments with high real-time requirements.
2.2 Disadvantages
Timing trigger, not callable.
3 Create events
A create event statement creates an event. Each event consists of two main parts. The first part is event schedule, which indicates when and at what frequency the event starts.
The second part is event action, which is the code executed when the event starts. The action of the event contains an SQL statement, which may be a simple insert or update statement, or a stored procedure or
The benin...end statement block, both of which allow us to execute multiple SQL s.
An event can be active (open) or stopped (closed), which means that the event scheduler checks whether the event action must be invoked, and stops means that the declaration of the event is stored in the directory, but the scheduler does not check whether it should be invoked. Immediately after an event is created, it becomes active, and an event of an activity can be executed once or more.
3.1 Create the following grammar
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_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
Noun Interpretation:
event_name: Evet name created (uniquely determined).
ON SCHEDULE: Plan tasks.
schedule: Determine the execution time and frequency of event (note that time must be future time, past time will be wrong), there are two forms of AT and EVERY.
[ON COMPLETION [NOT] PRESERVE]: Optional. The default is ON COMPLETION NOT PRESERVE, which automatically drops the event when the planned task is completed; ON COMPLETION PRESERVE does not drop.
[COMMENT'comment']: Optional, comment is used to describe event s; equivalent comment, maximum length 64 bytes.
[ENABLE | DISABLE]: Set the state of event, default ENABLE: indicates that the system tried to execute this event, DISABLE: Close the event, you can modify it with alter
DO event_body: An sql statement (which can be a compound statement) that needs to be executed. CREATE EVENT is legal when used in stored procedures.
3.2 Open Close Event Scheduler
3.2.1 The MySQL event scheduler event_scheduler is responsible for calling events, which are turned off by default. This scheduler constantly monitors whether an event is to be invoked or not. To create an event, the scheduler must be opened.
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 scheduler threads
mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| 2 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 3 | event_scheduler | localhost | NULL | Daemon | 6 | Waiting on empty queue | NULL|
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
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
Increase under [mysqld]
event_scheduler = 0 #Or OFF, DISABLED
View scheduler threads
mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 2 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
3.3 example: create a table to record the name and event stamp of each event scheduling
3.3.1 Create test tables
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 event execution results
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 event execution results
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 Every Second)
CREATE event event_now
ON SCHEDULE
EVERY 1 SECOND
DO INSERT INTO event_test VALUES(1);
3.3.4 Create event 4 (calling stored procedures per second)
CREATE DEFINER=`root`@`localhost` EVENT `eventUpdateStatus`
ON SCHEDULE EVERY 1 SECOND
STARTS '2017-11-21 00:12:44'
ON COMPLETION PRESERVE
ENABLE
DO call updateStatus()
3.4 Note:
The default creation event is stored in the current library, and it can also show which library the specified event was created in.
Only events created in the current library can be viewed through show events
When the event is executed, it will be released. If the event is executed immediately, the event will be deleted automatically after execution. The event can be seen by calling several times or waiting for execution.
If two events need to be invoked at the same time, mysql determines the order in which they are invoked, and if you want to specify the order, you need to ensure that one event is executed after at least one second of 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 not been executed.
4 View events
View the events in the current library
mysql> show events;
View all events
mysql> select * from mysql.event;
Historical articles:
JAVA Wechat Enterprise Payment to Change (Ten Minutes)
The Method and Step of Authorizing User openId by Wechat
One micro-signal supports multi-environment web authorization at the same time
Two Signature Algorithms of Wechat MD5 and HMAC-SHA256