If you are running a large web application then you must have lots of data(unwanted data also).For example, spam emails,unused records of the users.All these spamy and junk data will cause problem of database overload and backups.Its performance will also affected.So what could be the solution to overcome this problem.The solution is topic of this post called “MySQL Event Scheduler”.
From the release of MySQL 5.1.6 comes the inclusion of a scheduler. Perfect name "The scheduler" that schedules tasks within the DB.
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 ,
userID INT,
product_id INT,
created TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
PRIMARY KEY ( cartID )
);Turn Scheduler On
The scheduler is a backend process. Basically this means a separate thread runs constantly behind the scenes looking for events run. For this to happen, the MySQL GLOBAL variable event_scheduler must be set to ON. This is done with the following SQL statement.
SET GLOBAL event_scheduler = ON;
Or
SET GLOBAL event_scheduler = 1;
Schedule An Event
There are several options available to define when an event is to be executed.The schedule could be a MYSQL TIMESTAMP or may be an INTERVAL. Most of you are familiar with the MYSQL TIMESTAMP format as 2010-05-20 11:30:00. The interval can be any of the following.
• YEAR
• MONTH
• WEEK
• DAY
• HOUR
• MINUTE
• SECOND
Create an Event
An event is created using the CREATE EVENT syntax. The event name could be a string of up to 64 characters.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 <= DATE_SUB(NOW(), INTERVAL 10 DAY) ;
ALTER An Event
Up to this point, events have been created with various time intervals and frequencies. In order to edit an event, the ALTER EVENT statement is used as follows.
ALTER newEvent
ON SCHEDULE EVERY 5 HOUR
STARTS TIMESTAMP + 3 HOUR DROP An Event
To remove of DROP an event is similar to all MySQL DROP statements, in that the syntax follows the same.DROP EVENT newEvent;
Of course, if the event named "newEvent" does not exist, MySQL will return an error. To avoid this, just as with other DROP statements, the use of IF EXISTS is recommended.
DROP EVENT IF EXISTS newEvent;View Event
Show all the running events.SHOW EVENTS;
