• notice
  • Congratulations on the launch of the Sought Tech site

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

Tags

Technical otaku

Sought technology together

Related Topic

1 Comments

author

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

Leave a Reply

+