使用MySQL创建数据归档表实现数据归档功能

CREATE TABLE main_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE archive_table (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    created_at TIMESTAMP
);

delimiter //

CREATE PROCEDURE archive_data()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE id INT;
    DECLARE name VARCHAR(100);
    DECLARE age INT;
    DECLARE cur CURSOR FOR SELECT id, name, age FROM main_table WHERE created_at < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO id, name, age;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        INSERT INTO archive_table (id, name, age, created_at) VALUES (id, name, age, NOW());
        DELETE FROM main_table WHERE id = id;
    END LOOP;
    
    CLOSE cur;
END //

delimiter ;

CREATE EVENT archive_data_event
ON SCHEDULE
    EVERY 1 DAY
    STARTS '2022-01-01 00:00:00'
DO
    CALL archive_data();

INSERT INTO main_table (name, age) VALUES ('John', 25);
INSERT INTO main_table (name, age) VALUES ('Emily', 30);

-- 等待一天

SELECT * FROM main_table; -- 返回空结果，数据已归档到历史表

SELECT * FROM archive_table; -- 返回归档的数据