Gradually explain the creation of timing event plans in MySQL
One, use process
1.There are 3 ways to check whether the event plan (scheduler) is currently enabled:
SHOW VARIABLES LIKE'event_scheduler'; SELECT @@event_scheduler; SHOW PROCESSLIST;
2.There are 4 ways to turn on the event plan (scheduler) switch:
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 means turn on; 0 or OFF means turn off;
3.About the authority of the event plan:
When using event alone to call a SQL statement, viewing and creating requires the user to have the event permission, and when calling the SQL statement, the user is required to have the permission to execute the SQL.The event permission settings are saved in the mysql.user table and mysql.db in the Event_priv field.(FLUSH PRIVILEGES;)
When event and procedure are used together, the user must have create routine permission to view and create stored procedures, excute permission is required to call the stored procedure execution, and the user has the permission to execute the SQL when the stored procedure calls a specific SQL statement.
SELECT HOST,USER,Event_priv FROM mysql.user;
Get the currently logged-in user and database: SELECT CURRENT_USER(), SCHEMA();
It can be seen from Figure1 that bfsql@% does not have Event_priv permission.When creating an event under this user, the following error will occur:
Error Code: 1044Access denied for user'bfsql'@'%' to database'blog'
If the above error occurs, execute the following SQL to give bfsql@% the permission to create Event:
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 event:
(1) The syntax for creating an event is as follows:
CREATE EVENT [IF NOT EXISTS] event_name ON SCHEDULE schedule [ON COMPLETION [NOT] PRESERVE] [ENABLE | DISABLE] [COMMENT'comment'] DO sql_statement
(2) An example of creating an event is as follows:
DELIMITER $$ CREATE EVENT IF NOT EXISTS e_blog ON SCHEDULE EVERY 30 SECOND ON COMPLETION PRESERVE DO BEGIN CALL MoveBlogData(); END$$ DELIMITER;
The DO sql_statement field indicates the SQL statement or stored procedure that the event needs to execute.The SQL statement here can be a compound statement.Use the BEGIN and END identifiers to place the compound SQL statement in the order of execution.
--From now on, it will be executed regularly every nine days CREATE EVENT EVENT1 ON SCHEDULE EVERY 9 DAY STARTS NOW() ON COMPLETION PRESERVE ENABLE DO BEGIN CALL TOTAL(); END --Executed at 1:00 am on the first day of every month CREATE EVENT EVENT2 ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(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 ---Executed at 2 am on the first day of every quarter CREATE EVENT TOTAL_SEASON_EVENT ON SCHEDULE EVERY 1 QUARTER STARTS DATE_ADD(DATE_ADD(DATE(CONCAT(YEAR(CURDATE()),'-',ELT(QUARTER(CURDATE()),1,4,7,10),'-',1)) ,INTERVAL 1 QUARTER),INTERVAL 2 HOUR) ON COMPLETION PRESERVE ENABLE DO BEGIN CALL SEASON_STAT(); END --Executed at 4 o'clock in the morning on January 1 every year CREATE EVENT TOTAL_YEAR_EVENT ON SCHEDULE EVERY 1 YEAR STARTS DATE_ADD(DATE(CONCAT(YEAR(CURDATE()) + 1,'-',1,'-',1)),INTERVAL 4 HOUR) ON COMPLETION PRESERVE ENABLE DO BEGIN CALL YEAR_STAT(); END
5.Event opening and closing:
Turn on an event:
ALTER EVENT e_test ON COMPLETION PRESERVE ENABLE;
Close an event:
ALTER EVENT e_test ON COMPLETION PRESERVE DISABLE;
Two.Examples:
The mysql timer is the event provided by the system, and the timer in the oracle is the job provided by the system.Without further ado, create a table below:
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 here is mainly provided to the timer event of mysql to call to execute:
create procedure mypro() BEGIN insert into mytable (name,introduce,createtime) values ββ('1111','inner mongolia',now()); end;
I wrote it briefly here, just to illustrate an example.
Then create the mysql timer event:
create event if not exists eventJob on schedule every 1 second on completion PRESERVE do call mypro();
Here is set to execute once every second
So far, all the preparatory work has been written.After finishing these, if mysql wants to use the timer to do the preparatory work, it is to turn on the mysql timer:
SET GLOBAL event_scheduler=1;-start timer SET GLOBAL event_scheduler=0;-stop the timer
The event will be opened next:
ALTER EVENT eventJob ON COMPLETION PRESERVE ENABLE;-open event ALTER EVENT eventJob ON COMPLETION PRESERVE DISABLE;-Close event SHOW VARIABLES LIKE'%sche%';-View timer status
So far, you can go to the table mytable in the database to check, the system will insert a piece of data every second, hehe, the task is complete.
select * from mytable
lipitor 80mg pills & lt;a href="https://lipiws.top/"& gt;buy lipitor 80mg generic& lt;/a& gt; buy atorvastatin 20mg without preion
Mdvwuu
2024-03-08