Schedule SQL Query to execute on specific time/interval

Imagine, you have created some stuff and to demo it, you have created a page where you accept dummy data from your guest users and you are populating them into your database. At some point of time, your database would be flooded with records that would be useless and this will definitely slow down the performance of your database. Now, in this case, you need to manually clean up your database in order to keep the performance of your database at it best.

But, instead of doing it manually you would think about something, which should automate cleanup work on regular interval. At this point of time, you probably think about CRON job. But, instead, we will use MySQL database inbuilt option called EVENTS, which we can use to execute certain task in database like executing SQL script, calling stored procedure etc.. 

Also, please note that, this is not like triggers. Triggers are fired on data or structure change but MySQL events are scheduled based on time/interval.

First of all Enable EVENT Scheduler

SET GLOBAL event_scheduler = ON;

Then, validate current status of Event Scheduler

SELECT @@event_scheduler;

Now, create EVENT to execute your SQL query

CREATE EVENT e_store_ts 
ON SCHEDULE
EVERY 1 HOUR
DO
UPDATE myschema.yourtable set mycolumn='N' -- update this table

List all EVENT created

SHOW EVENTS;

That's it. !!