Summary
MySQL EVENT is a scheduled task, similar to crontab task on Linux
It mainly has the following properties
- In mysql, the objects of timing events are globally unique
- event performs operations according to the schedule, including one SQL operation. You can also use begin and end to implement multiple SQL composite execution
- The permission of event (create, modify and delete) is controlled and given
- You can use SQL statements to set or modify many properties of events. These properties include the name of the event, time, persistence (i.e. whether to keep it after its schedule expires), status (enabled or disabled), action to be performed, and the schema assigned to it
How to turn on
Check the system variable. ON means started, OFF means stopped (default), or DISABLED means unable to run. It is not recommended to use this parameter value. The status cannot be modified while running
mysql> show variables like '%event_scheduler%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | ON | +-----------------+-------+ 1 row in set (0.00 sec)
As long as this function is enabled, you can see the threads of this module, which run and execute all scheduled tasks
mysql> show processlist\G; *************************** 1. row *************************** Id: 454 User: event_scheduler Host: localhost db: NULL Command: Daemon Time: 45647 State: Waiting for next activation Info: NULL
The open command is as follows, and the close command is the same
SET GLOBAL event_scheduler = ON; SET @@GLOBAL.event_scheduler = ON; SET GLOBAL event_scheduler = 1; SET @@GLOBAL.event_scheduler = 1;
In addition, because event is a global variable, if the global variable is not specified, an error will be reported
mysql> set @@event_scheduler = OFF; ERROR 1229 (HY000): Variable 'event_scheduler' is a GLOBAL variable and should be set with SET GLOBAL
Authority grant
EVENT controls the creation, modification and deletion of events. grant grants permissions. The basic format is as follows
mysql> GRANT EVENT ON test.* TO test; Query OK, 0 rows affected (0.04 sec)
Because event is a permission at the global schema level, it cannot be empowered for a table
mysql> GRANT EVENT ON test.test01 TO test; ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the manual to see which privileges can be used
Obviously, for the SQL statement contained in events, the user executing the scheduled task must have relevant permissions. Otherwise, the scheduled task will trigger, but the task execution will fail. At this time, you can see the information in the mysql log
##Relevant information
Basic syntax for creating event s
CREATE EVENT CREATE EVENT [IF NOT EXISTS] event_name ON SCHEDULE schedule [ON COMPLETION [NOT] PRESERVE] [ENABLE | DISABLE] [COMMENT 'comment'] DO sql_statement; schedule: AT timestamp [+ INTERVAL interval] | EVERY interval [STARTS timestamp] [ENDS timestamp] interval: quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
System table information, which records each created scheduled task
mysql> select * from information_schema.events\G; *************************** 1. row *************************** EVENT_CATALOG: def --Always be def EVENT_SCHEMA: test --Database name of EVENT_NAME: event_test_insert --event name DEFINER: root@% --Create this event Users TIME_ZONE: SYSTEM --Scheduling time zone, default is SYSTEM EVENT_BODY: SQL --Body language EVENT_DEFINITION: INSERT INTO test.event_test VALUES (CURRENT_TIMESTAMP) --What needs to be done is DO Part of EVENT_TYPE: RECURRING --Event recurrence type, once( TRANSIENT)Repeat ( REPEATING) EXECUTE_AT: NULL --If the timing of the event is EVERY Clause instead of AT Clause (that is, the event type is repeating)，The value of this column is NULL. INTERVAL_VALUE: 1 --every For a one-time event, this column is null INTERVAL_FIELD: SECOND --every The unit part of, which is used to manage the time of the event, here is the 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 --Valid when an event is created or changed and executed under it SQL Pattern STARTS: 2020-03-19 13:51:52 --starts Events for clause ENDS: NULL --The event definition contains ENDS Loop event for clause STATUS: DISABLED --ENABLE, DISABLE or SLAVESIDE_DISABLED One of the three values for event Enabled state of ON_COMPLETION: PRESERVE CREATED: 2020-03-19 13:51:52 --Event creation time LAST_ALTERED: 2020-03-19 13:53:10 --Last modified LAST_EXECUTED: 2020-03-19 13:53:10 --Last execution time EVENT_COMMENT: --Remarks ORIGINATOR: 0 --The server id，The default is 0. CHARACTER_SET_CLIENT: utf8 --character COLLATION_CONNECTION: utf8_general_ci DATABASE_COLLATION: latin1_swedish_ci
In addition to viewing the system table, you can view the created events in the following ways, and the output information is basically the same
SHOW EVENTS; SHOW CREATE EVENT xxxx; SELECT * FROM MYSQL.EVENTS;
Test creation
Create a test table
mysql> CREATE TABLE kmtest(timeline varchar(20)); Query OK, 0 rows affected (0.02 sec)
Timed cycle task
Plan to insert the current time in the table every second, as follows
mysql> CREATE EVENT event_kmtest ON SCHEDULE EVERY 1 SECOND DO INSERT INTO test.kmtest VALUES (CURRENT_TIMESTAMP); Query OK, 0 rows affected (0.00 sec) mysql> select * from kmtest limit 2; +---------------------+ | timeline | +---------------------+ | 2020-03-20 14:54:07 | | 2020-03-20 14:54:08 | +---------------------+ 2 rows in set (0.00 sec)
Stop the current scheduled task
mysql> ALTER EVENT event_kmtest ON COMPLETION PRESERVE DISABLE; Query OK, 0 rows affected (0.00 sec)
If you no longer need it, you can delete the task
mysql> drop event event_kmtest; Query OK, 0 rows affected (0.00 sec)
Set a time to execute
Create a task to execute at a certain time
mysql> CREATE EVENT event_kmtest ON SCHEDULE AT '2020-03-20 15:20:20' DO set @time= CURRENT_TIMESTAMP;INSERT INTO test.kmtest VALUES (CURRENT_TIMESTAMP); Query OK, 0 rows affected (0.00 sec)
Come back after this time
mysql> select * from kmtest; +---------------------+ | timeline | +---------------------+ | 2020-03-20 15:20:20| +---------------------+ 1 row in set (0.00 sec)
During the period of waiting for execution, you can see the execution information of event. After the expiration of execution, it will be removed automatically. You can see that type is one time
mysql> show events\G; *************************** 1. row *************************** Db: test Name: event_kmtest Definer: root@% Time zone: SYSTEM Type: ONE TIME
Multiple SQL
An event contains multiple SQL. In the process of using, there may be more than one SQL that needs to be executed sometimes. In this case, you need to use the delimiter and begin end to create it. Examples are as follows:
delimiter | CREATE EVENT event_kmtest ON SCHEDULE EVERY 1 SECOND DO begin set @time= CURRENT_TIMESTAMP; INSERT INTO test.kmtest VALUES (CURRENT_TIMESTAMP); end | delimiter;
PS: the syntax is correct, but when I execute on the mysql client command line, it will default to line feed output. I don't know why. It's OK to use some tools to execute
