問(wèn)題描述

是否可以每周在 mysql 表中自動(dòng)將 3 天前的行移動(dòng)到另一個(gè)名為T(mén)able_Archive"的表中?

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

表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

當(dāng)新的輸入進(jìn)入tableA時(shí)，下一步的ID(PK)不會(huì)有任何問(wèn)題嗎?

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

我得到了什么:

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 天前的日期?

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

推薦答案

就個(gè)人而言，我會(huì)使用 MySQL 事件調(diào)度程序.這是一個(gè)內(nèi)置的事件調(diào)度器，類似于 Linux 中的 CRON.

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

您可以指定它以指定的時(shí)間間隔調(diào)用一個(gè)過(guò)程、過(guò)程或函數(shù)或運(yùn)行一些 SQL.

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

閱讀 MySQL 文檔，但一個(gè)例子是:

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

所以這是說(shuō)每周調(diào)用一次 clean_tables() 并在 10 分鐘后進(jìn)行第一次調(diào)用"

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

一個(gè)問(wèn)題是(我認(rèn)為)默認(rèn)情況下禁用了事件調(diào)度程序.要打開(kāi)它運(yùn)行:

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

SET GLOBAL event_scheduler = ON;

然后您可以運(yùn)行:

SHOW PROCESSLIST;

查看事件調(diào)度程序線程是否正在運(yùn)行.

To see whether the event scheduler thread is running.

至于保留您的表 A ID 列(如果必須).我會(huì)將 Table_Archive 上的 ID 保留為該表的唯一標(biāo)識(shí)，即使其成為主鍵 &auto_increment 然后有一個(gè) 'Original_TableA_ID' 列來(lái)存儲(chǔ) TableA ID.如果需要，您可以在其上放置唯一索引.

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.

所以 Table_Archive 應(yīng)該是這樣的:

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

似乎沒(méi)有人回答您最初的問(wèn)題如何將 _now 更改為 3 天前的日期?".您可以使用 INTERVAL 來(lái)做到這一點(diǎn):

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 ;

最后一點(diǎn)是，您應(yīng)該考慮在 TableA 的時(shí)間戳列上創(chuàng)建索引，以提高 clean_tables() 過(guò)程的性能.

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.

這篇關(guān)于將行從 TableA 移動(dòng)到 Table-Archive的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！