mysql - MySQL定时归档数据的方法

Question

我得到了这个场景:

我有 2 个特定的列，称为 cs_start_time(Type:Datetime)和 cs_time_length(Type:int (seconds))在我的 table 上。

现在我想在 cs_start_time + cs_time_length < NOW() (Current Datetime) 时自动删除这些行并将它们插入到另一个表中(当然应该在删除这些行之前)。

或者当有人对这些行进行选择查询时我会处理它(类似触发器)

这甚至可以用 mysql 实现吗？ (我是mysql新手)

Answer 1

为此，您可以使用 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
);

---

• 首先将事件调度程序设置为 ON

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;