When you will need MySQL Events?
(1) To delete junk records automatically on regular interval of time. E.g. delete every week.
(2) Used to Call a stored procedure at specific time.
(3) Automatically perform any scheduled SQL operation.
Three basic steps are required to schedule a task.
• Event Name
• Event Interval
• SQL Statement
Create a Table – Cart
CREATE TABLE cart(
cartID INT AUTO_INCREMENT ,
created TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
PRIMARY KEY ( cartID )
Turn Scheduler On
SET GLOBAL event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
Schedule An Event
Create an Event
CREATE EVENT newEvent
ON SCHEDULE EVERY 1 DAY
DELETE FROM cart WHERE created <= DATE_SUB(NOW(), INTERVAL 10 DAY) ;
ALTER An Event
ON SCHEDULE EVERY 5 HOUR
STARTS TIMESTAMP + 3 HOUR
DROP An EventTo remove of DROP an event is similar to all MySQL DROP statements, in that the syntax follows the same.
DROP EVENT newEvent;
DROP EVENT IF EXISTS newEvent;
View EventShow all the running events.