MySQL Event Scheduling

In large applications, like shopping carts, a huge volume of data is managed. This huge volume of data makes the database overloaded. Thus, unused data needs to be deleted from the table. In this tutorial, we will discuss how to schedule this deletion operation in the MySQL database.

Let us consider a sample table cart as defined below:

CREATE TABLE cart (
cart_id INT  AUTO_INCREMENT,
user_id INT,
product_id INT,
entry_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (cart_id)
);

To start the event scheduler the following query is to be executed. You can run the query either in PhpMyAdmin or in the command prompt of MySQL.

SET GLOBAL event_scheduler = ON;

OR

SET GLOBAL event_scheduler = 1;

Here, we are creating a scheduler clearCartEvent, which will run in every day, and will delete the records that are more than 10 days old.

CREATE EVENT clearCartEvent ON SCHEDULE EVERY 1 DAY DO 
DELETE FROM cart 
WHERE entry_time <= DATE_SUB(NOW(), INTERVAL 10 DAY) ;

If you feel that you don't need to keep the data for such a long time and you want to update the event. If you want to change the event to maintain the data only for 12 hours, you can update the event. The following query is written to alter the existing event query.

ALTER clearCartEvent ON SCHEDULE EVERY 12 HOUR STARTS TIMESTAMP + 3 HOUR

To check all the running events, you can run the following command.

SHOW EVENTS;

If you want to drop an event, you can drop by executing the below query.

DROP EVENT newEvent;

Happy Exploring!