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
DROP TABLE IF EXISTS `blog`; CREATE TABLE `blog` ( `blog_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `blog_title` varchar(250) NOT NULL DEFAULT '', `blog_slug` varchar(255) NOT NULL DEFAULT '', `blog_content` mediumtext NOT NULL, `blog_category_id` int(4) unsigned NOT NULL DEFAULT '0', `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `last_updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `blog_deleted` tinyint(1) NOT NULL DEFAULT '0', `comment_enabled` tinyint(1) NOT NULL DEFAULT '0', `comment_invite` varchar(255) NOT NULL, `comments_count` smallint(4) NOT NULL DEFAULT '0', `blog_excert` text NOT NULL, `blog_tags` varchar(359) DEFAULT '', `blog_approved` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`blog_id`), KEY `article_title` (`blog_title`), KEY `article_slug` (`blog_slug`) ) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8;
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
insert into `blog`(`blog_id`,`blog_title`,`blog_slug`,`blog_image_name`,`blog_content`,`blog_category_id`,`created_on`,`last_updated`,`blog_deleted`,`comment_enabled`,`comment_invite`,`comments_count`,`blog_excert`,`blog_tags`,`blog_approved`,`user_id`) values (23,'Test MySQL','test-mysql','<p>mysql blog content</p>',3,'2013-02-01 18:26:10','2013-02-20 03:42:49',0,1,'',20,'<p>mysql blog content</p>','mysql',1),(25,'Test PHP','test-php','<p>php blog content</p>',2,'2013-02-01 18:44:07','2013-02-20 03:30:16',0,1,'',60,'<p>php blog content</p>','php',1),(26,'Test Java','test-java','<p>java blog content</p>\r\n<p><code></p>\r\n<p>public String hello(){</p>\r\n<p> return \"Hello\";</p>\r\n<p>}</p>\r\n<p></code></p>\r\n<p>Test HTML code</p>\r\n<p><code></p>\r\n<p><html></p>\r\n<p><body></p>\r\n<p><b>This text will be in bold</b></p>\r\n<p><p>This text is inside a paragraph</p></p>\r\n<p></body></p>\r\n<p></html></p>\r\n<p></code></p>',1,'2013-02-01 22:48:19','2013-02-20 03:20:20',0,1,'',70,'<p>java blog content</p>\r\n<p><code></p>\r\n<p>public String hello(){</p>\r\n<p> return \"Hello\";</p>\r\n<p>}</p>\r\n<p></code></p>\r\n<p>Test HTML code</p>\r\n<p><code></p>\r\n<p><html></p>\r\n<p><body></p>\r\n<p><b>This text will be in bold</b></p>\r\n<p><p>This text is inside a paragraph</p></p>\r\n<p></body></p>\r\n<p></html></p>\r\n<p></code></p>','java',1),(28,'Test Java Post','test-java-post','<p>java blog content</p>',1,'2013-02-06 07:56:25','0000-00-00 00:00:00',0,1,'',100,'<p>java blog content</p>','java',1);Table structure for blog_archive
DROP TABLE IF EXISTS `blog_archive`; CREATE TABLE `blog_archive` ( `blog_id` int(10) unsigned NOT NULL, `blog_title` varchar(250) NOT NULL, `blog_slug` varchar(255) NOT NULL, `blog_content` mediumtext NOT NULL, `blog_category_id` int(4) unsigned NOT NULL, `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `last_updated` datetime NOT NULL, `comment_enabled` tinyint(1) NOT NULL, `comment_invite` varchar(255) NOT NULL, `comments_count` smallint(4) NOT NULL, `blog_excert` text NOT NULL, `blog_tags` varchar(359) DEFAULT NULL, `blog_approved` tinyint(1) NOT NULL, PRIMARY KEY (`blog_id`), KEY `article_title` (`blog_title`), KEY `article_slug` (`blog_slug`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Data for the table blog_archive
insert into `blog_archive`(`blog_id`,`blog_title`,`blog_slug`,`blog_image_name`,`blog_content`,`blog_category_id`,`created_on`,`last_updated`,`comment_enabled`,`comment_invite`,`comments_count`,`blog_excert`,`blog_tags`,`blog_approved`,`user_id`) values (22,'Test Java Update','test-java-update','<p>java blog content update</p>',1,'2013-02-17 11:01:25','2013-02-17 06:31:25',0,'',100,'<p>java blog content update</p>','java',1),(24,'Test Oracle','test-oracle','<p>oracle blog content</p>',4,'2013-02-17 11:01:43','2013-02-17 06:31:43',0,'',40,'<p>oracle blog content</p>','oracle',1),(27,'Test Mysql another','test-mysql-another','<p>another mysql content</p>',3,'2013-02-17 11:01:37','2013-02-17 06:31:37',0,'',20,'<p>another mysql content</p>','another mysql',1);
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
DROP TABLE IF EXISTS `blog_comment`; CREATE TABLE `blog_comment` ( `comment_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `comment_text` text NOT NULL, `comment_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `comment_parent_id` int(10) unsigned NOT NULL DEFAULT '0', `comment_approved` tinyint(1) unsigned NOT NULL DEFAULT '0', `blog_id` int(10) unsigned NOT NULL DEFAULT '0', `name` varchar(100) NOT NULL, `email` varchar(100) NOT NULL, `web` varchar(255) NOT NULL, `ip` varchar(45) NOT NULL, PRIMARY KEY (`comment_id`) ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
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
insert into `blog_comment`(`comment_id`,`comment_text`,`comment_date`,`comment_parent_id`,`comment_approved`,`blog_id`,`user_id`,`name`,`email`,`web`,`ip`) values (8,'dffdfd','2013-02-15 22:05:52',0,1,29,'trtr','test@test.com','www.test.com','127.0.0.1'),(9,'dssdsd','2013-02-16 22:01:39',0,1,28,'trrt','test@test.com','www.test.com','127.0.0.1'),(10,'cccx','2013-02-16 22:01:48',9,1,28,'dfdf','testemail@example.com','www.test.com','127.0.0.1'),(11,'archive comment','2013-02-17 09:08:04',0,1,22,'dssd','testemail@example.com','www.test.com','127.0.0.1'),(12,'vccvvc','2013-02-19 22:02:52',0,1,29,'sdds','test@test.com','www.test.com','127.0.0.1');
Table structure for blog_comment_archive
DROP TABLE IF EXISTS `blog_comment_archive`; CREATE TABLE `blog_comment_archive` ( `comment_id` int(10) unsigned NOT NULL, `comment_text` text NOT NULL, `comment_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `comment_parent_id` int(10) unsigned NOT NULL, `comment_approved` tinyint(1) unsigned NOT NULL, `blog_id` int(10) unsigned NOT NULL, `name` varchar(100) NOT NULL, `email` varchar(100) NOT NULL, `web` varchar(255) NOT NULL, `ip` varchar(45) NOT NULL, PRIMARY KEY (`comment_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Data for the blog_comment_archive
insert into `blog_comment_archive`(`comment_id`,`comment_text`,`comment_date`,`comment_parent_id`,`comment_approved`,`blog_id`,`user_id`,`name`,`email`,`web`,`ip`) values (0,'another archive','2013-02-17 09:09:37',11,1,22,'fddf','test@test.com','www.test.com','127.0.0.1'),(1,'dfdfddf','2013-01-28 09:24:52',0,1,22,'name','test@test.com','www.test.com','0.0.0.0'),(2,'another comment','2013-01-28 09:30:24',0,1,22,'guest','test@test.com','http://www.test.com','127.0.0.1'),(3,'test comment another','2013-01-28 09:31:58',2,1,22,'hello','test@test.com','www.test.com','127.0.0.1'),(4,'heellloooo','2013-01-28 09:33:57',1,1,22,'name','test@test.com','www.test.com','127.0.0.1'),(5,'test comment *','2013-01-28 13:17:51',0,1,22,'ew','test@test.com','www.test.com','127.0.0.1'),(6,' comment','2013-01-28 13:18:25',0,1,22,'ewwe','test@test.com','www.test.com','127.0.0.1'),(7,'hello','2013-01-28 14:04:24',5,1,22,'d','test@test.com','www.test.com','127.0.0.1');
We have blog categories so the below table is for that
Table structure for category
DROP TABLE IF EXISTS `category`; CREATE TABLE `category` ( `category_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `category_name` varchar(100) NOT NULL DEFAULT '', `category_title` varchar(255) NOT NULL DEFAULT '', `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `last_updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`category_id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
Data for the category
insert into `category`(`category_id`,`category_name`,`category_title`,`created_on`,`last_updated`) values (1,'java','Java','2013-01-23 11:54:47','0000-00-00 00:00:00'),(2,'php','PHP','2013-01-23 11:54:47','0000-00-00 00:00:00'),(3,'mysql','MySQL','2013-01-24 08:09:58','0000-00-00 00:00:00'),(4,'oracle','Oracle','2013-01-24 08:10:06','0000-00-00 00:00:00');
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.
/* Event structure for event `blog_mark_delete` */ /*!50106 DROP EVENT IF EXISTS `blog_mark_delete`*/; DELIMITER $$ /*!50106 CREATE DEFINER=`root`@`localhost` EVENT `blog_mark_delete` ON SCHEDULE EVERY 1 WEEK STARTS '2013-02-17 07:56:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN UPDATE blog SET blog_deleted=1 WHERE created_on < DATE_SUB(NOW(), INTERVAL 1 MONTH); END */$$ DELIMITER ;
Now I will create an event which will run once a week and will move all blogs which are older than one month.
/*!50106 DROP EVENT IF EXISTS `blog_archive`*/; DELIMITER $$ /*!50106 CREATE DEFINER=`root`@`localhost` EVENT `blog_archive` ON SCHEDULE EVERY 1 WEEK STARTS '2013-02-17 08:30:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN -- copy deleted posts INSERT INTO blog_archive (blog_id, blog_title,blog_slug,blog_content, blog_category_id,created_on,last_updated,comment_enabled,comment_invite, comments_count,blog_excert,blog_tags,blog_approved) SELECT blog_id, blog_title,blog_slug,blog_content, blog_category_id,created_on,last_updated,comment_enabled,comment_invite, comments_count,blog_excert,blog_tags,blog_approved FROM blog WHERE blog_deleted = 1; -- copy associated comment records INSERT INTO blog_comment_archive (comment_id,comment_text,comment_date, comment_parent_id,comment_approved,blog_id,name,email,web,ip) SELECT bc.comment_id,bc.comment_text,bc.comment_date,bc.comment_parent_id, bc.comment_approved,bc.blog_id,bc.name,bc.email,bc.web,bc.ip FROM blog_comment bc JOIN blog ON bc.blog_id = blog.blog_id WHERE blog.blog_deleted = 1; -- remove deleted comment entries and blogs DELETE blog_comment.* FROM blog_comment left join blog on blog_comment.blog_id = blog.blog_id where blog.blog_deleted = 1; DELETE FROM blog WHERE blog_deleted = 1; END */$$ DELIMITER ;
That’s all. Thanks for your reading.