I came across one problem when I was creating tutorials. The problem was how to delete records from database tables AUTOMATICALLY? Now you would be thinking why I had to do this? When I created LIVE DEMO of Inserting records into table using jQuery & PHP I felt that after some time my database would be flooded with records that are just ‘useless’. So I had to manually delete records each day in order to keep the performance of my database as well as website.
Then I thought there should be something like CRON jobs so that I can automatically delete records from the table in MySQL. Then I learned about MySQL EVENTS . First of all let me clear you it’s not like triggers. Triggers are fired on “data change” but MySQL events are scheduled based on various factors like time etc.
There are various cases when you will need MySQL events. Some of the uses are listed below:
1> To delete junk records automatically in a regular interval of time. E.g. delete every week.
2> Calling a stored procedure/routine at particular time.
3> Automatically perform any scheduled sql operation.
In simple words you can schedule your SQL query using MySQL events .
NOTE: You can use either PhpMyAdmin or MySQL command prompt to work with events.
Let’s start with our simple tutorial. I am using PhpMyAdmin, go to PhpMyAdmin then run a SQL query there to turn on the scheduler. You need to set the scheduler value to ON or 1 in order to work with events. Here is the command which will turn your scheduler:
SET GLOBAL EVENT_SCHEDULER = ON;
ORSET GLOBAL EVENT_SCHEDULER = 1;
Once you have invoked your scheduler you can see it in the process list. By the following command:
Now we can proceed and create our EVENT. Here is how I created Event for the solution of my problem. It will delete all records from class table in every 3 days.
CREATE EVENT delete_class_data ON SCHEDULE EVERY 3 DAY DO DELETE FROM <code>class</code>;
You can refer official documentation of MySQL to create events. There are various parameters that can be used to create an EVENT for your need.
Now you have created an event and if you want to see your events in future just fire a simple command:
It will list all the events with details about each event.
To update your event MySQl offers you ALTER command. You can update the working of event by manipulating the SQL query, you can also change the schedule or event running time.
I my example I am changing my Event (delete_class_data) run time. It will run the event once – one hour after the ALTER command is fired.
ALTER EVENT delete_class_data ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
Further if you want to DROP the created event in future, you can simply use the command:
DROP event delete_class_data;
I hope you have understood MySQL events and how to work with them. You can also use MySQL events with PHP to schedule things like publishing blog post in your website etc. I highly recommend you to once go through the official documentation. Share the simple tutorial in order to help others.