Use MySQL to create data archiving tables to implement data archiving functions

Use MySQL to create a data archive table to implement the data archiving function

Data archiving refers to moving old data to a historical archive table to maintain the high performance of the main database table. Data archiving is an effective data management method when dealing with large amounts of data. MySQL provides many methods to implement data archiving, one of which is by creating data archiving tables. This article will introduce how to use MySQL to create a data archiving table to implement the data archiving function.

1. Create the main table
   First, we need to create a main table to store the current activity data. This main table is used for read and write operations so we can maintain high performance. The following is the main table structure of an example:

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

2. Creating a history table
   Next, we need to create a history table to store archived old data. This history table is read-only, so we can store it on slower storage media, such as cold storage or archive storage. The following is an example history table structure:

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

3. Create Archive Stored Procedure
   The next step is to create a stored procedure that will move data from the primary table to the history table. The following is an example archive stored procedure:

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 ;

This stored procedure queries the primary table for data from one year ago and inserts it into the history table. After the insertion is successful, the corresponding data is deleted from the main table.

4. Set a scheduled task for archiving
   In order to automate data archiving, we can set up a scheduled task to call the archive stored procedure regularly. The following is an example of a scheduled task setting:

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

This scheduled task will execute the archive stored procedure once a day.

5. Test the data archiving function
   Finally, we can test the data archiving function. Insert some data into the main table and wait for a day. The next day, the scheduled task will be triggered to move the data from one year ago to the history table.

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; -- 返回归档的数据

Through the above steps, we successfully created the data archiving table using MySQL and implemented the data archiving function. Data archiving can help us maintain high performance of primary tables and save cold data in historical tables for query and analysis needs.

The above is the detailed content of Use MySQL to create data archiving tables to implement data archiving functions. For more information, please follow other related articles on the PHP Chinese website!