how to use mysql event scheduler

Oct 13, 2012   //   by kdecom   //   event scheduler, mysql  //  No Comments
How to use Mysql Event Scheduler
how to use mysql event scheduler

Mysql has a lot of cool feature one of them is called event scheduler.
I hope all of you knows what is cron job in PHP so mysql event scheduler is sort of same concept as cron job. If you don’t know what is cron job please read this article.

And Till now days i was always using a cron job  to Delete or changed my unwanted Data.

For example :-
You are running a Large website. So you keep all the Email that is been sent from your website into a Email Tables. You table looks like this i guess:-

CREATE TABLE Email
(
email_id INT  AUTO_INCREMENT,
send_to_email_address VARCHAR 255,
email_subject VARCHAR 255,
email_content TEXT,
status VARCHAR 255,
send_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (email_id )
) ;

So let say once email is send you changed the status to “SEND”.

So once your status is “SEND” in that case no longer you need to keep all the email rows intos a database.

So you can use mySQL Event scheduler to delete all the unwanted Email rows so you can clean up your database.

To do that you need to first enabled EVENTS in MYSQL

How to Enabled Events

You could use any:-
SET GLOBAL event_scheduler = ON;
OR 
SET GLOBAL event_scheduler = 1;

HOW TO CREATE AN EVENT

CREATE EVENT EMAIL_CLEAN_UP

ON SCHEDULE EVERY 1 DAY
DO
DELETE FROM Email WHERE status = ‘SEND’

If you want to Drop the event 


How to drop Event

DROP EVENT EMAIL_CLEAN_UP;

I hope that helps:

There is one thing that people would like to do it. After event gets executed…
there is one way you can do is:-

Setup a cron job which checks that event gets executed YES or NO and if yes then Send the events data..

To save the events data you need to make a new table called event_result and save the data inside that table…

so your event looks something like this…

CREATE EVENT EMAIL_CLEAN_UP

ON SCHEDULE EVERY 1 DAY
DO
DELETE FROM Email WHERE status = ‘SEND’;
Insert Into event_result values (‘EMAIL_CLEAN_UP‘,’event_executed_yes‘);



cheers..

Leave a comment

css.php