How to periodically archive data in MySQL

Question

I got this scenario :

I got 2 specific columns which are called cs_start_time(Type:Datetime) and cs_time_length(Type:int (seconds)) in my table.

Now I want to delete these rows automatically when cs_start_time + cs_time_length < NOW() (Current Datetime) and Insert them into another table (of course it should be before deleting these rows).

Or I handle it when someone do a select query on these rows (Trigger alike)

Is this even possible with mysql ? (Im a newbie in mysql)

• 1000111: is this relation correct? cs_start_time + cs_time_length >= NOW() . I thought it would be something like : cs_start_time + cs_time_length < NOW() if you want to check expiry.
• Ahmet K: Yep your correct. Its too early in the morning ^^
• 1000111: You can use mysql event scheduler for this purpose. But I would suggest you can filter out these entries in your select query. What's the big deal of deleting those entries whereas you can easily bypass these in your select query? You have to check every second through the scheduler whether they meet the condition to be deleted or not.
• Ahmet K: I wanted to let my table clean but in a way your right ...
• 1000111: Then I can suggest you can periodically archive those entries and delete them after archiving (through event scheduler). In this case you have to run the event scheduler once in a single day. So it's your choice.

Answer 1

You can use MySQL event scheduler for this purpose:

I've simulated your scenario creating the main table where data initially stay. Later the expired data are archived in a table named archiveTable through an event scheduler.

• Main table structure and data:

---

-- ----------------------------
-- Table structure for `maintable`
-- ----------------------------
DROP TABLE IF EXISTS `maintable`;
CREATE TABLE `maintable` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `cs_start_time` datetime NOT NULL,
 `cs_time_length` int(11) NOT NULL,
 PRIMARY KEY (`id`)
);
-- ----------------------------
-- Records of maintable
-- ----------------------------
INSERT INTO `maintable` VALUES ('1', '2016-05-01 12:00:00', '10');
INSERT INTO `maintable` VALUES ('2', '2016-05-02 12:00:00', '5');
INSERT INTO `maintable` VALUES ('3', '2016-05-03 12:00:00', '15');

---

• Archive table structure and data:

---

DROP TABLE IF EXISTS `archivetable`;
CREATE TABLE `archivetable` (
 `id` int(11) NOT NULL,
 `cs_start_time` datetime NOT NULL,
 `cs_time_length` int(11) NOT NULL
);

---

• Set event scheduler ON first

SET GLOBAL event_scheduler = ON;

• Create the event:

---

 DROP EVENT IF EXISTS `archiveEvent`;
 delimiter $$
 CREATE EVENT `archiveEvent` ON SCHEDULE EVERY 1 MINUTE STARTS '2016-05-02 00:00:00' ON COMPLETION PRESERVE ENABLE DO
 BEGIN
 INSERT INTO archivetable (
 id,
 cs_start_time,
 cs_time_length
 ) SELECT
 MT.id,
 MT.cs_start_time,
 MT.cs_time_length
 FROM
 maintable MT
 WHERE
 MT.cs_start_time + INTERVAL MT.cs_time_length SECOND < NOW() ; 
 DELETE
 FROM
 maintable
 WHERE
 cs_start_time + INTERVAL cs_time_length SECOND < NOW() ;
 END$$
 delimiter ;

Note: Look the event start time is set to 2016年05月02日 00:00:00. After that the event will be scheduled in every one minute interval. You can change the schedule time in any interval unit as you like.

Suggestion:

Quoting from my comment:

You can use mysql event scheduler for this purpose. But I would suggest you can filter out these entries in your select query. What's the big deal of deleting those entries whereas you can easily bypass these in your select query? You have to check every second through the scheduler whether they meet the condition to be deleted or not;

• Ahmet K: Will this schedeuler take a huge amount of performance when its running every minute in a huge table ? (~10 Million)
• 1000111: Consider a scenario: if the execution time of the event takes more than one minute then another schedule will get start running. So it will be a mess then I guess. My suggestion would be to choose a safe interval in this case.

Answer 2

What you are describing looks like a recurring process that runs on a clock - whenever the "now()" value reaches a certain point, you want to have some action executed.

One way to implement this is to write a CRON job. Cron is a built-in system in Unix/Linux that lets you run commands on a given schedule. For example, you could run a script every hour that executes your SQL query (select all records that should be moved over, move them, and delete them); and then output the results of the execution to the log file to monitor your process.