将行从TableA移到Table-Archive [英] Move rows from TableA into Table-Archive
问题描述
是否有可能每周3天自动在mysql数据库中自动将3天的行移动到另一个名为"Table_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?
tableA例如:
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:
Table_Archive:
ID | stringvalue | Timestamp
1 | abc | 2011-10-01
2 | abc2 | 2011-10-02
3 | abc3 | 2011-10-05
当新输入进入tableA时,下一步是否会出现ID(PK)的任何问题?
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?
推荐答案
Personally, I would make use of the MySQL Event Scheduler. This is a built in event scheduler rather like CRON in Linux.
您可以指定它以指定的时间间隔调用过程,过程或函数或运行一些SQL.
You can specify it to call a procedure, procedures or functions or run a bit of SQL at designated intervals.
阅读MySQL文档,但示例如下:
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();
这就是说每周呼叫一次clean_tables()并在10分钟内拨打电话"
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;
然后您可以运行:
SHOW PROCESSLIST;
查看事件调度程序线程是否正在运行.
To see whether the event scheduler thread is running.
关于保留表A ID"列(如果需要).我会将Table_Archive上的ID保留为该表唯一,即使其成为主键& auto_increment,然后有一个"Original_TableA_ID"列,用于存储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就像:
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
);
似乎没有人回答您最初的问题我如何将_now更改为3天前的日期?".您可以使用INTERVAL
:
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 ;
最后一点是,您应该考虑在TableA的timestamp列上创建索引,以提高clean_tables()过程的性能.
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.
这篇关于将行从TableA移到Table-Archive的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!