mysql函数:将旧条目旋转到归档表

如果您想用动态后缀命名归档表(就像我认为您在示例中使用的是number_of_week),那么我建议使用bash/perl/python编写一个脚本,而不是使用mysql存储的例程。

另外,如果您使用的是mysql 5.1,那么您可能需要考虑按日期对内容表进行分区,以便在旧分区存档后可以轻松地删除它们。

改用触发器。它们是为这些而生的。

好吧,我最后写的:) 我花了一段时间,但在这里:

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 ;

请随时反馈或编辑以供自己使用:)