MySQL function: Rotate old entries to archive table

Question

Hi, I'm looking for the function that will take rows older then X days and put it in archive table... Was thinking to make function so it will be easer to execute... something like

CREATE TABLE archive_NUMBER_OF_WEEK (...);
INSERT INTO archive_NUMBER_OF_WEEK SELECT * FROM content WHERE DATE < X days;
DELETE * FROM content WHERE DATE < X days

Will post if I manage to write it by myself :)

Answer 1

use triggers instead. They are meant for these.

Answer 2

If you want to name the archive table with a dynamic suffix (like I think you are implying with NUMBER_OF_WEEK in your example), then I would recommend writing a script in bash/perl/python to do this, rather than using a MySQL stored routine.

Also if you are using MySQL 5.1 you may want to consider partitioning the content table by date so you can easily drop the old partitions after they are archived.

Answer 3

Ok, I finality wrote it :) It tool me a while but here it is:

DELIMITER $$

DROP PROCEDURE IF EXISTS `procedure`.`rotate_events_pro`$$
CREATE DEFINER=`confiq`@`%` PROCEDURE `rotate_events_pro`(p_threshold_rotate_row int,p_days_to_keep int,OUT p_message VARCHAR(200))
BEGIN
    DECLARE v_id_to_move INT;
    #lets take ID that we need to move
    #we can't use variables in LIMIT, how smart MySQL!
    SET @v_sql = CONCAT('SELECT max(id) 
        INTO @v_id_to_move
        FROM events
        WHERE event_created < DATE_ADD(CURDATE(), INTERVAL - ',p_days_to_keep,' DAY) OR 
        id < (SELECT min(id) FROM (SELECT id FROM events ORDER BY id DESC LIMIT ?) as id)');
    PREPARE stmt1 FROM @v_sql;
    SET @param1 = p_threshold_rotate_row;
    EXECUTE stmt1 USING @param1;
        DEALLOCATE PREPARE stmt1;
    #we have IDs that need to to be moved to arhive
    IF @v_id_to_move > 0 THEN
        START TRANSACTION;
            INSERT INTO archived_events SELECT * FROM events WHERE id < @v_id_to_move;
            INSERT INTO archived_eventsinfo SELECT * FROM eventsinfo WHERE event_id < @v_id_to_move;
            DELETE FROM events WHERE id < @v_id_to_move;
            DELETE FROM eventsinfo WHERE event_id < @v_id_to_move;
        COMMIT;
        SET p_message = CONCAT('moved all events that id is < ',@v_id_to_move);
    ELSE
        SET p_message = 'Nothing to move';
    END IF;
END$$

DELIMITER ;

Feel free to feedback or edit it for your own use :)