This post has already been read 1520 times!

Database
Sample database cart table contains four columns cart_id, user_id, product_id and created_at.

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


Enable MySQL Event Scheduler
Start MySQL event scheduler by executing following query in PhpMyAdmin or MySQL command prompt.

SET GLOBAL event_scheduler = ON;
Or
SET GLOBAL event_scheduler = 1;

 

MySQL event create


Create a Event
Here the following event will run everyday and clear/delete 10 days old data from cart table based on time stamp

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

 

MySQL event create


Alter Event
If you want to modify the event run time simple you can execute a query in below syntax.

ALTER newEvent
ON SCHEDULE EVERY 5 HOUR
STARTS TIMESTAMP + 3 HOUR 


View Event
Show all the running events.

SHOW EVENTS;

 

MySQL event create


Delete Event
You can drop the event for executing below query.

DROP EVENT newEvent;


Check event schedule status under process tab in PhpMyAdmin root (it will show only when scheduled event is running).

MySQL event create

 

Leave a Reply

Post Navigation