Schedule SQL Query Using phpMyAdmin- MySQL Events

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;

OR

SET GLOBAL EVENT_SCHEDULER = 1;

Once you have invoked your scheduler you can see it in the process list. By the following command:

SHOW PROCESSLIST

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 `class`;

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:

SHOW EVENTS

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.

6 thoughts on “Schedule SQL Query Using phpMyAdmin- MySQL Events”

  1. I want to make a website but i have no knowledge of programming .pls help how can make my website???

  2. Hi,
    Thank you for your post.
    May I know how can we manually trigger the event to check if the scheduler is being setup properly?
    Thank you.

    1. You can reduce the time period of the event and test it and if it is working properly then alter and update the time again!! This is one simple way i can think of (y)

  3. Very nice and clear tutorial, i am using phpmyadmin but didn’t know about this. I came to your blog from indiblogger, i was searching for the blogs related to programming. You are doing great work.

Comments are closed.