基于存储过程实现的自动归档表数据逻辑

基本逻辑：

1. 定时任务触发函数；
2. 查询配置表，根据配置表中上一次归档的时间以及归档的时间间隔，时间单位 查询出需要进行归档的表 ； （函数逻辑）
3. 修改表名：将原表名修改为归档表名；（函数逻辑）
4. 创建表：创建原表； （函数逻辑）
5. 迁移数据：根据配置表中的数据进行迁移数据。（函数逻辑）

自动归档配置表：

CREATE TABLE `archive_table_config` (
  `id` int NOT NULL AUTO_INCREMENT,
  `archive_table_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '需要归档的表名',
  `interval_time` int NOT NULL COMMENT '需要归档的时间间隔,例如每1月归档一次',
  `interval_unit` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'SECOND,\r\nMINUTE,\r\nHOUR,\r\nDAY,\r\nWEEK,\r\nMONTH,\r\nYEAR',
  `last_archive_timestamp` timestamp NULL DEFAULT NULL,
  `migrate_data` tinyint NOT NULL COMMENT '是否迁移数据',
  `migrate_data_time` int DEFAULT NULL COMMENT '迁移数据的时间 (迁移多久的数据)',
  `migrate_data_unit` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '迁移数据的时间单位',
  `migrate_data_filed` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '迁移数据的时间字段(用于兼容有些是 created , 有些是 create_time)',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_inter` (`interval_time`,`interval_unit`,`last_archive_timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用于 自动归档表 的配置信息';

存储过程：

CREATE DEFINER=`root`@`%` PROCEDURE `ArchiveTables`()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE a,b,c CHAR(30);
    DECLARE d,e INT;
    DECLARE f,g CHAR(30);
    DECLARE cur CURSOR FOR 
--         1.查询归档配置信息
        SELECT archive_table_name, interval_time, interval_unit, migrate_data, migrate_data_time, migrate_data_unit, migrate_data_filed 
        FROM archive_table_config
        WHERE (interval_unit = 'SECOND' AND TIMESTAMPADD(SECOND, interval_time, last_archive_timestamp) <= NOW())
            OR (interval_unit = 'MINUTE' AND TIMESTAMPADD(MINUTE, interval_time, last_archive_timestamp) <= NOW())
            OR (interval_unit = 'HOUR' AND TIMESTAMPADD(HOUR, interval_time, last_archive_timestamp) <= NOW())
            OR (interval_unit = 'DAY' AND TIMESTAMPADD(DAY, interval_time, last_archive_timestamp) <= NOW())
            OR (interval_unit = 'WEEK' AND TIMESTAMPADD(WEEK, interval_time, last_archive_timestamp) <= NOW())
            OR (interval_unit = 'MONTH' AND TIMESTAMPADD(MONTH, interval_time, last_archive_timestamp) <= NOW())
            OR (interval_unit = 'YEAR' AND TIMESTAMPADD(YEAR, interval_time, last_archive_timestamp) <= NOW())
            OR last_archive_timestamp is null;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO a,b,c,d,e,f,g;
        IF done THEN
            LEAVE read_loop;
        END IF;

        SET @oldTableName = a;
        SET @newTableName = CONCAT(a, '_', DATE_FORMAT(NOW(), '%Y%m%d'), '_', FLOOR(RAND() * 999));
        
--        2.修改表名
         SET @renameTableSQL = CONCAT('RENAME TABLE ', @oldTableName, ' TO ', @newTableName);
        PREPARE stmt FROM @renameTableSQL;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
--        3.创建表
        SET @createTableSQL = CONCAT('CREATE TABLE ', @oldTableName, ' LIKE ', @newTableName);
        PREPARE stmt FROM @createTableSQL;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
--        4.迁移数据
        IF d = 1 THEN
            SET @migrateDataSQL = CONCAT(
                'INSERT INTO ', @oldTableName, 
                ' SELECT * FROM ', @newTableName, 
                ' WHERE ', g, ' >= DATE_SUB(NOW(), INTERVAL ', e, ' ', f, ')'
            );
            PREPARE stmt FROM @migrateDataSQL;
            EXECUTE stmt;
        END IF;
--        5.修改归档时间
        UPDATE dating_archive_table_config
        SET last_archive_timestamp = NOW() 
        WHERE archive_table_name = @oldTableName;
    END LOOP;

    CLOSE cur;
END

关于存储过程的使用：

个人认为武断的判断存储过程不能使用是不合理的 ，应该合理分析使用的场景来决定。
当前这个场景就非常适合使用存储过程来实现，如果不用存储过程，那么就需要使用Java程序把逻辑步骤一步步实现，一方面不够灵活，另一方面还会使用大量的${}。