Move rows from TableA into 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 ex:

ID | 开发者_JS百科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?

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.

You may need to have a look into cron jobs if you want that script/query to be executed automatically.

If you are using cpanel have a look into http://www.siteground.com/tutorials/cpanel/cron_jobs.htm

Adding to the best answer (imo) by Tom Mac regarding the event scheduler - be aware that when backing up the schema, you have to specify that you want the events backed up with it via the --events=TRUE flag.

If you're exporting manually in the workbench, the latest version has a checkbox on the main 'Export To Disk' tab - older versions hide it away in the Advanced Export Options tab.

It is possible, MySQL will execute query automatically at specific time using MySQL Event Scheduler. Check this link for more details. https://dev.mysql.com/doc/refman/5.7/en/event-scheduler.html