如何在MySQL中定期归档数据 [英] How to periodically archive data in MySQL

问题描述

我遇到了这种情况:

我在表中有2个特定的列，分别称为cs_start_time(Type:Datetime)和cs_time_length(Type:int (seconds)).

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

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

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)

使用mysql甚至可以吗? (我是mysql的新手)

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

推荐答案

您可以为此目的使用MySQL event scheduler:

我已经模拟了您的方案，该方案创建了最初保留数据的主表.后来，过期的数据通过event scheduler被存储在名为archiveTable的表中.

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.

• 主表结构和数据:

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

• 创建事件:

    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.之后，将每隔一分钟安排一次活动.您可以根据需要以任何间隔单位更改计划时间.

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.

建议:

引用我的评论

您可以为此目的使用mysql事件调度程序.但是我会 建议您可以在选择查询中过滤掉这些条目.什么是 大量删除这些条目，而您可以轻松绕过 这些在您选择的查询?您必须每秒检查一次 调度程序是否满足要删除的条件；

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;

这篇关于如何在MySQL中定期归档数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！