Step by step, we will explain how to create a scheduled event plan in MySQL and how to create a scheduled mysql event plan.

Step by step, we will explain how to create a scheduled event plan in MySQL and how to create a scheduled mysql event plan.

I. Use Process
1. There are three methods to check whether the event plan (Scheduler) is currently enabled:

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

 
2. There are four ways to enable the event Scheduler:

SET GLOBAL event_scheduler = 1;SET @@global.event_scheduler = 1;SET GLOBAL event_scheduler = ON;SET @@global.event_scheduler = ON;

Key value 1 or ON indicates enabled; 0 or OFF indicates disabled;
 
3. permission on the event plan:
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. (Flush privileges ;)
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.

SELECT HOST,USER,Event_priv FROM mysql.user;

Obtain the users and databases currently logged on: SELECT CURRENT_USER (), SCHEMA ();
From Figure1, we can know that bfsql @ % has no Event_priv permission. the following error occurs when you create an event for this user:
Error Code: 1044 Access denied for user 'bfsql' @ '%' to database 'blog'
If the preceding error occurs, run the following SQL statement to grant the Event creation permission to bfsql @ %:

UPDATE mysql.user SET Event_priv = 'Y' WHERE HOST='%' AND USER='bfsql'; FLUSH PRIVILEGES; 

Finally, you can view all permissions through show grants for 'bfsql' @ '%;
 
4. Create an event:
(1) syntax for event creation:

CREATE EVENT [IF NOT EXISTS] event_nameON SCHEDULE schedule[ON COMPLETION [NOT] PRESERVE][ENABLE | DISABLE][COMMENT 'comment']DO sql_statement

(2) An example of event creation is as follows:

DELIMITER $$CREATE EVENT IF NOT EXISTS e_blogON SCHEDULE EVERY 30 SECONDON COMPLETION PRESERVEDO BEGINCALL MoveBlogData();END$$DELIMITER ;

 
The DO SQL _statement field indicates the SQL statement or stored procedure to be executed for the event. Here, the SQL statement can be a composite statement. The BEGIN and END identifiers are used to place the composite SQL statement in the execution order.

-- Execute create event EVENT1 on schedule every 9 day starts now () on completion preserve enable do begin call total () EVERY nine days from now on (); END -- execute create event EVENT2 on schedule every 1 month starts DATE_ADD (DATE_SUB (CURDATE (), interval day (CURDATE ()-1 DAY) at ON the first DAY of each MONTH ), INTERVAL 1 MONTH), INTERVAL 1 HOUR) on completion preserve enable do begin call stat (); END --- create event TOTAL_SEASON_EVENT on schedule every 1 quarter starts DATE_ADD (DATE (CONCAT (YEAR (CURDATE ()),'-', ELT (QUARTER (CURDATE (),), '-', 1), INTERVAL 1 QUARTER), INTERVAL 2 HOUR) on completion preserve enable do begin call SEASON_STAT (); END -- execute create event TOTAL_YEAR_EVENT on schedule every 1 year starts DATE_ADD (DATE (CONCAT (YEAR (CURDATE () at ON January 1, January 1 EVERY YEAR ()) + 1, '-', 1, '-', 1), INTERVAL 4 HOUR) on completion preserve enable do begin call YEAR_STAT (); END

 
5. Enable and disable the event:
Enable an event:

ALTER EVENT e_test ON COMPLETION PRESERVE ENABLE;

Close an event:

ALTER EVENT e_test ON COMPLETION PRESERVE DISABLE;

Ii. Example:
The mysql timer is an event provided by the system, while the timer in oracle is a job provided by the system. To put it bluntly, create the following table:

create table mytable (id int auto_increment not null,name varchar(100) not null default '',introduce text not null,createtime timestamp not null,constraint pk_mytable primary key(id))

Create a stored procedure. The stored procedure is mainly provided to the mysql timer event for calling and executing:

create procedure mypro()BEGINinsert into mytable (name,introduce,createtime) values ('1111','inner mongolia',now());end;

Here is a simple example.

Then create the mysql timer event:

create event if not exists eventJob on schedule every 1 second on completion PRESERVEdo call mypro();

Set this parameter to run once every second.

At this point, all the preparations have been completed. After all these preparations are completed, mysql has enabled the mysql timer to take advantage of the Timer:

Set global event_scheduler = 1; -- start the timer set global event_scheduler = 0; -- stop the timer.

Then you need to enable the event:

Alter event eventJob on completion preserve enable; -- enable event alter event eventJob on completion preserve disable; -- disable event show variables like '% sche %'; -- view the timer status

So far, you can see in the mytable table in the database. The system inserts a piece of data every second.

select * from mytable