mysql - 将行从 TableA 移动到 Table-Archive

Question

是否可以每周在 mysql 中自动将 3 天前的行移动到另一个名为“Table_Archive”的表中？

表 A 例如:

ID | stringvalue | Timestamp
1  | abc         | 2011-10-01
2  | abc2        | 2011-10-02
3  | abc3        | 2011-10-05
4  | abc4        | 2011-10-10
5  | abc5        | 2011-10-11

移动后

表A:

ID | stringvalue | Timestamp
4  | abc4        | 2011-10-10
5  | abc5        | 2011-10-11

表_存档:

ID | stringvalue | Timestamp
1  | abc         | 2011-10-01
2  | abc2        | 2011-10-02
3  | abc3        | 2011-10-05

当新输入进入表A时，下一步的ID(PK)不会有任何问题吗？

我得到了什么:

CREATE PROCEDURE clean_tables ()
BEGIN
    BEGIN TRANSACTION;

    DECLARE _now DATETIME;
    SET _now := NOW();

    INSERT
    INTO    Table_Archive
    SELECT  *
    FROM    TableA
    WHERE   timestamp < _now - 3;
    FOR UPDATE;

    DELETE
    FROM    TableA
    WHERE   timestamp < _now - 3;

    COMMIT;
END

如何将 _now 更改为 3 天前的日期？

Answer 1

就我个人而言，我会使用 MySQL Event Scheduler .这是一个内置的事件调度程序，类似于 Linux 中的 CRON。

您可以指定它来调用一个过程、过程或函数，或者以指定的时间间隔运行一些 SQL。

阅读 MySQL 文档，举个例子:

CREATE EVENT mydatabase.myevent
ON SCHEDULE EVERY 1 WEEK STARTS CURRENT_TIMESTAMP + INTERVAL 10 MINUTE
DO
 call clean_tables();

这就是说“每周调用一次 clean_tables() 并在 10 分钟内进行第一次调用”

一个问题是事件调度程序(我认为)默认情况下是禁用的。要打开它运行:

SET GLOBAL event_scheduler = ON;

然后你可以运行:

SHOW PROCESSLIST;

查看事件调度程序线程是否正在运行。

至于保留您的表 A ID 列(如果必须的话)。我会将 Table_Archive 上的 ID 保留为该表的唯一 ID，即将其设为主键和 auto_increment，然后有一个“Original_TableA_ID”列用于存储 TableA ID。如果需要，您可以在上面放置一个唯一索引。

所以 Table_Archive 会是这样的:

create table `Table_Archive` (
ID int unsigned primary key auto_increment, -- < primary key auto increment
tableAId unsigned int not null, -- < id column from TableA
stringValue varchar(100),
timestamp datetime,
UNIQUE KEY `archiveUidx1` (`tableAId`) -- < maintain uniqueness of TableA.ID column in Archive table
);

似乎没有人回答您最初的问题“如何将 _now 更改为 3 天前的日期？”。您可以使用 INTERVAL 执行此操作:

DELIMITER $

CREATE PROCEDURE clean_tables ()
BEGIN
BEGIN TRANSACTION;

DECLARE _now DATETIME;
SET _now := NOW();

INSERT
INTO    Table_Archive
SELECT  *
FROM    TableA
WHERE   timestamp < _now - interval 3 day;
FOR UPDATE;

DELETE
FROM    TableA
WHERE   timestamp < _now - interval 3 day;

COMMIT;
END$

DELIMITER ;

最后一点是您应该考虑在 TableA 的时间戳列上创建索引以提高 clean_tables() 过程的性能。