将表 A 中的行移动到表存档中

发布于 2024-12-09 02:55:18 字数 1054 浏览 0 评论 0原文

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

tableA ex:

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

移动之后

tableA:

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

Table_Archive:

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

当新输入进入tableA时，下一步移动中的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 天前的日期？

原文

Is it possible to move rows that are 3 days old into an other table called "Table_Archive" automatically in mysql ones a week?

tableA ex:

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

After the move

tableA:

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

Table_Archive:

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

And when new input comes into tableA it wont be any problems with ID (PK) in the next move?

What Ive got:

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

How do I change _now to be the date 3 days ago?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

永言不败 2024-12-16 02:55:18

就我个人而言，我会使用 MySQL 事件调度程序。这是一个内置的事件调度程序，类似于 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() 过程的性能。

Personally, I would make use of the MySQL Event Scheduler. This is a built in event scheduler rather like CRON in Linux.

You can specify it to call a procedure, procedures or functions or run a bit of SQL at designated intervals.

Read the MySQL docs but an example would be:

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

So this is saying "call clean_tables() once a week and make the first call in 10 minutes' time"

One gotcha is that the event scheduler is (I think) disabled by default. To turn it on run:

SET GLOBAL event_scheduler = ON;

You can then run:

SHOW PROCESSLIST;

To see whether the event scheduler thread is running.

As for preserving your Table A ID column (if you must). I would keep the ID on Table_Archive as unique to that table i.e make it the primary key & auto_increment and then have a 'Original_TableA_ID' column in which to store the TableA ID. You can put a unique index on this if you want.

So Table_Archive would be like:

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

Nobody seems to have answered your original question "How do I change _now to be the date 3 days ago?". You do that using 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 ;

One final point is that you should consider creating an index on the timestamp column on TableA to improve the performance of you clean_tables() procedure.

回复收藏 0 原文