JIYIK CN >

This tutorial will guide you through creating a MySQL EVENTtimer, also known as a MySQL timer.

We will see how to set a timer in MySQL to let MySQL manipulate the data (eg. UPDATE// data) INSERT. DELETEIt will also take a backup (if required) at the mentioned date and time.

We will study MySQL considering different scenarios EVENT. Also, discuss its importance and benefits. For this article, we are using MySQL version 8.0.27, but you can get the newer version (if available) from their official website.

MySQL 事件and its importance

MySQL EVENTis used to schedule tasks for certain operations - for example, updating a column, fetching a table, or taking a database backup.

You can 创建``事件have it executed only once or repeatedly at a given interval - for example, every minute, hour, month or year.

There is no fixed number of statements to create EVENT. EVENTThere can be many lines in the body.

If EVENTthe has more than one line, the body must be contained in BEGINand ENDblocks. It is also important to know that scheduled EVENTcommands are stored in and executed 数据库in the scheduled 日期and .时间

Creating events is important for the following benefits:

• Get Everyone on the Same Page
• Set your goals
• Manage schedules and execute events
• Used to optimize tables and update data
• Generate useful reports for off-peak hours
• Lets you prioritize your tasks
• Save your money and time

EventScheduling a program or setting a timer in MySQL

Delays are very costly to both production teams and brands.

Scheduling 事件or setting timers for specific fields (columns) and then dumping them into separate files for project reports will save their time. This way, it will let them focus on other productive tasks.

MySQL uses a single event_schedulerthread named to execute all events. If event_schedulerthe value of is ON, then EVENTis executed; otherwise, it is not.

You have to event_schedulerset the value of to using the following command ON.

SET GLOBAL event_scheduler = ON;

Now, if you 创建one 事件, how do you know it will be executed? Use the following command to see.

SHOW PROCESSLIST;

Let's look at the output before and after event_schedulersetting the value of to .ON

In the first output, you cannot execute EVENT, but in the second output, you can. This is because the second screenshot event_schedulerhas ON.

Output:

You can 创建``事件use it for different purposes - for example, 事件to run it only once or once a year, or at a given interval. Let's start understanding with the help of sample code.

We personhave a table in database studentwhich has the following records at current time.

Let's take a look at one-time events. The sample code is as follows 创建.事件

SET GLOBAL event_scheduler = ON;

CREATE EVENT IF NOT EXISTS ONE_TIME_EVENT
ON SCHEDULE AT CURRENT_TIMESTAMP
DO
INSERT INTO person.student (id, FIRST_NAME, LAST_NAME, GENDER, EMAIL)
VALUES 
(7, 'Thomas', 'Christoper', 'Male', 'tchristopher@gmail.com');

In the code given above, we event_schedulerset the value of ONto execute EVENT.

We then created one EVENT, named it ONE_TIME_EVENT, and scheduled it on CURRENT_TIMESTAMP. This EVENTmeans it will CURRENT_TIMESTAMPexecute once on and then expire.

This is created only if it does not exist EVENT. This EVENTjob is a row INSERTin studentthe table.

After creating and executing this EVENT, check studentthe table to see the new record (see the following screenshot).

Output:

Suppose you need 创建an event that will CURRENT_TIMESTAMPbe executed 20 seconds after 事件. This event will only be executed once. Write the following code to understand.

CREATE EVENT IF NOT EXISTS ONE_TIME_EVENT_WITH_INTERVAL
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 20 SECOND
DO
INSERT INTO person.student 
VALUES 
(8, 'Suzu', 'Aly', 'Fmale', 'aly.suzu@yahoo.com');

Output:

studentYou can see the new record in the table after 20 seconds .

Remember, if you check it before 20 seconds have passed, you won't studentsee this new row in the table. Let the twenty seconds pass, then check.

Use the following code to see how many events are created and scheduled.

SHOW EVENTS FROM classicmodels;

Output:

Why aren't there any listed in the screenshot above EVENT? Because all of our events are now expired, according to this post.

You can use ON COMPLETION PRESERVEthe clause to track all events, regardless of whether they are expired or not, as shown below.

CREATE EVENT TEST_PRESERVE_CLAUSE
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
ON COMPLETION PRESERVE
DO
INSERT INTO tests(test_name,test_date)
VALUES('Testing Preserve Clause',NOW());

What if you want 创建an event that CURRENT_TIMESTAMPstarts at , 分钟executes once every , and 小时expires after . It is called a recurring event.事件

You can use the following code to create and practice.

CREATE EVENT RECURRING_EVENT
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
INSERT INTO Tests(test_name,test_date)
 VALUES('This is recurring event test',NOW());

In the code given above, we use STARTSand ENDSto tell EVENTthe start and end time. You can also delete the event when you think that you don't need it now.

EVENTSubstitute your name in the following code name.

DROP EVENT [IF EXIST] name;

Let's take another situation where you create EVENTand hand over the work to another developer. Isn't it COMMENTeasy to understand? Of course not.

CREATE EVENTAnd COMMENTpractice by adding , as shown below.

CREATE EVENT RECURRING_EVENT
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
COMMENT 'It will add a new record every minture for one hour'
DO
INSERT INTO Tests(test_name,test_date)
 VALUES('This is recurring event test',NOW());

We have EVENTlearned using only one line in the body EVENT. What if you have multiple lines? Let's understand it with the following sample code.

delimiter |
CREATE EVENT IF NOT EXISTS EVENT_FOR_UPDATE_COLUMNS_AND_DUMP
ON SCHEDULE AT current_timestamp
DO BEGIN
UPDATE student SET GENDER = 'M' WHERE student.GENDER = 'Male';
UPDATE student SET GENDER = 'F' WHERE student.GENDER = 'Female';
Table student INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/file.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n';   
END |
delimiter ;

EVENTYou'll see three queries in the body of the provided code . Whenever you EVENThave multiple lines in the body of the , enclose them in BEGINand END, as described above.

In this example, we create a CURRENT_TIMESTAMP. EVENTIt will UPDATEupdate the column based on the statement GENDERand export the data to .CSVa .

For export, you must use this path C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/file.csv(if you have installed MySQL in C drive). Otherwise, you will get the following error.

Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

If you have installed MySQL somewhere else, use the EVENT scheduler and then use the following command to find the path where your files will be exported.

SHOW VARIABLES LIKE "secure_file_priv";  

in conclusion

EVENTWe have learned about MySQL scheduler in this tutorial .

We learned how to create events that will execute once and only once at a provided interval. We also explored creating events with multiple rows, updating the data, and then exporting it to separate files.