mysql数据存档_如何在MySQL中定期存档数据

您可以使用

MySQL事件调度程序来实现此目的：

我已经模拟了您的场景,创建了最初保留数据的主表.稍后,过期的数据将通过事件调度程序存档在名为archiveTable的表中.

>主表结构和数据：

-- ----------------------------

-- 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');

>归档表结构和数据：

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 GLOBAL event_scheduler = ON;

>创建活动：

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 ;

注意：查看事件开始时间设置为2016-05-02 00:00:00.之后,将每隔一分钟安排一次活动.您可以根据需要更改任何间隔单位的计划时间.

建议：

引用我的评论：

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;