MySQL Event Scheduler

This tutorial will show you how we can use MySQL Event Scheduler to schedule or automate some tasks periodically at a particular interval.

MySQL Events are tasks that run according to a schedule. Therefore, it is sometimes referred to as scheduled events. When we create an event, we are creating a named database object containing one or more SQL statements to be executed at one or more regular intervals, beginning and ending at a specific date and time.

For more information please go through http://dev.mysql.com/doc/refman/5.1/en/events.html

MySQL Events have the following major features and properties:

1. An event, assigned to a schema, is uniquely identified by its name
2. An event performs a specific action according to a schedule. An event’s timing can be either one-time or recurrent. A one-time event executes one time only. A recurrent event repeats its action at a regular interval, and the schedule for a recurring event can be assigned a specific start day and time, end day and time, both, or neither.
3. We can create, modify, and drop scheduled events using SQL statements intended for these purposes. Syntactically invalid event creation and modification statements fail with an appropriate error message.
4. Many of the properties of an event can be set or modified using SQL statements. These properties include the event’s name, timing, persistence (that is, whether it is preserved following the expiration of its schedule), status (enabled or disabled), action to be performed, and the schema to which it is assigned.
5. An event’s action statement may include most SQL statements permitted within stored routines.

So using the MySQL Event Scheduler concept I will show you how to move a blog which is one month older from one table to another table. I will also move all comments associated with those blogs. For this to work, I will create one Event scheduler which will run once a week at a particular time.

I will now create all required tables in the following way.

Table – blog

The following table contains blogs which are newly created and when these blogs get one month older then these blogs will be moved to the table called blog_archive

Table structure for blog

The above table has several columns but few requires some explanation as shown below:

blog_slug – is the static URL generated from blog title. It is required for SEO optimization
blog_category_id – the blog belongs to a particular category in the category table given later
comment_enabled – whether people are allowed to comment on this blog. Value is either 0 or 1, 0 means not enabled and 1 means enabled.
blog_deleted – Value is either 0 or 1. If it is marked as 1 then the blog will be moved to the table blog_archive otherwise not
comment_invite – a user can invite another user to leave a comment for a blog
blog_excert – blog excert which is required if you want to display only a few words or portion or summary of the blog
blog_tags – comma separated tags or keywords associated with this blog
blog_approved – only approved blogs get published or shown to the external users. Value is either 0 or 1, 0 means not approved and 1 means approved.

Data for the table blog

Table structure for blog_archive

Data for the table blog_archive

Now we will move also all blog comments to the different tables. So we will create blog_comments and blog_comment_archive

Table structure for blog_comment

The above table has several columns but few requires some explanation as shown below:

comment_parent_id – useful when you need nested comments. a comment can be under another comment.
comment_approved – only approved comments get published or shown to the external users. Value is either 0 or 1, 0 means not approved and 1 means approved.
blog_id – comment belong to a particular blog
name – a user name who posts a comment
email – a user’s valid email who posts a comment
web – a user’s valid website who posts a comment. This is an optional.
ip – IP address of the user’s system from where a user posts a comment

Data for the blog_comment

Table structure for blog_comment_archive

Data for the blog_comment_archive

We have blog categories so the below table is for that

Table structure for category

Data for the category

I will create two schedulers – one for marking blog as deleted and another scheduler will move the deleted blogs to the blog_archive table.

Mark blog as deleted when blogs are older than one month using the following event scheduler.

Now I will create an event which will run once a week and will move all blogs which are older than one month.

That’s all. Thanks for your reading.

Soumitra

Software Professional, I am passionate to work on web/enterprise application. For more information please go to about me. You can follow on Twitter. You can be a friend on Facebook or Google Plus or Linkedin

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.