您可以使用
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;