Detailed explanation of the use of MySQL events

In system management or database management, it is often necessary to execute a certain command or SQL statement periodically. At this time, the mysql event will be used. To use this function, you must ensure that the mysql version is 5.1 or above.

1. First check whether the event is enabled,

SHOW VARIABLES LIKE 'event_scheduler';
SELECT @@event_scheduler;
SHOW PROCESSLIST;

星sql

2. We first enable the mysql event, 1. Modify it through dynamic parameters: SET GLOBAL event_scheduler = ON; Note: You still need to add event_scheduler=ON to my.cnf. Because if it is not added, the mysql restart event will return to its original state.

3. Next I create a test database table,

CREATE TABLE `test` (  
`ID` int(11) NOT NULL AUTO_INCREMENT,  
`lpnam` datetime DEFAULT NULL,  
PRIMARY KEY (`id`))
ENGINE=InnoDB 
AUTO_INCREMENT=106 
DEFAULT CHARSET=utf8

Create a Database table as test

4. Next we create mysql event,

CREATE EVENT IF NOT EXISTS test123 ON SCHEDULE EVERY 3 SECOND
ON COMPLETION PRESERVE
DO INSERT INTO ceshisy(lpname) VALUES(NOW());

execute sql creation mysql event

5. After the creation is completed, let’s check whether it was executed and check the database table

6. Summarize the grammar:

DEFINER: 定义事件执行的时候检查权限的用户。
ON SCHEDULE schedule: 定义执行的时间和时间间隔。
ON COMPLETION [NOT] PRESERVE: 定义事件是一次执行还是永久执行，默认为一次执行，即NOT PRESERVE。
ENABLE | DISABLE | DISABLE ON SLAVE: 定义事件创建以后是开启还是关闭，以及在从上关闭。如果是从服务器自动同步主上的创建事件的语句的话，会自动加上DISABLE ON SLAVE。
COMMENT 'comment': 定义事件的注释。

The above is the detailed content of Detailed explanation of the use of MySQL events. For more information, please follow other related articles on the PHP Chinese website!