mysql 归档,如何在MySQL中定期归档数据

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)

解决方案

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;