在业务规模不断扩大的过程中，单表数据量过大会导致查询性能下降，分表成为常用的优化方案。分表后产生的历史数据需要定期归档到对应的归档表，避免占用主表存储空间，而分表的表名往往带有时间、业务标识等动态后缀，手动处理归档效率极低，使用MySQL存储过程可以自动化完成这一工作。
分表数据归档的核心需求
分表数据归档通常需要满足以下几个核心要求：
- 能够自动识别需要归档的分表，不需要手动指定每个表名
- 支持动态拼接表名，适配不同规则的分表命名
- 归档过程要保证数据一致性，避免数据丢失或重复归档
- 归档完成后可以自动清理原分表的过期数据
动态表名的处理方案
MySQL存储过程中无法直接把变量作为表名使用，需要借助PREPARE语句预处理动态SQL来实现。动态表名的拼接需要先明确分表的命名规则，比如按月份分表的命名格式为order_202405、order_202406，归档表命名为order_archive_202405。
首先可以通过查询information_schema.tables表获取所有符合规则的分表名，示例代码如下：
-- 声明变量存储分表名、动态SQL DECLARE done INT DEFAULT 0; DECLARE table_name VARCHAR(100); DECLARE cur CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = 'test_db' AND table_name LIKE 'order_%' AND table_name NOT LIKE 'order_archive_%'; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
上述代码通过游标遍历所有符合order_前缀且不是归档表的分表，后续可以逐个处理这些表的归档逻辑。
完整归档存储过程编写
以下是一个完整的分表数据归档存储过程示例，实现了动态表名拼接、数据插入归档表、清理原表过期数据的逻辑：
DELIMITER //
CREATE PROCEDURE archive_sharding_table()
BEGIN
-- 声明变量
DECLARE done INT DEFAULT 0;
DECLARE cur_table_name VARCHAR(100);
DECLARE archive_table_name VARCHAR(100);
DECLARE dynamic_insert_sql VARCHAR(1000);
DECLARE dynamic_delete_sql VARCHAR(1000);
-- 游标获取所有需要归档的分表
DECLARE table_cur CURSOR FOR
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'test_db'
AND table_name LIKE 'order_%'
AND table_name NOT LIKE 'order_archive_%'
-- 只处理3个月前的分表
AND SUBSTRING(table_name, 7) < DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 3 MONTH), '%Y%m');
-- 游标结束处理
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- 开启事务
START TRANSACTION;
OPEN table_cur;
read_loop: LOOP
FETCH table_cur INTO cur_table_name;
IF done THEN
LEAVE read_loop;
END IF;
-- 拼接归档表名，原表为order_202405，归档表为order_archive_202405
SET archive_table_name = CONCAT('order_archive_', SUBSTRING(cur_table_name, 7));
-- 检查归档表是否存在，不存在则创建
SET @check_table_sql = CONCAT('CREATE TABLE IF NOT EXISTS ', archive_table_name, ' LIKE ', cur_table_name);
PREPARE check_stmt FROM @check_table_sql;
EXECUTE check_stmt;
DEALLOCATE PREPARE check_stmt;
-- 拼接插入归档表的SQL，插入原表所有数据
SET @insert_sql = CONCAT('INSERT INTO ', archive_table_name, ' SELECT * FROM ', cur_table_name);
PREPARE insert_stmt FROM @insert_sql;
EXECUTE insert_stmt;
DEALLOCATE PREPARE insert_stmt;
-- 拼接删除原表过期数据的SQL，这里删除所有已归档的数据
SET @delete_sql = CONCAT('DELETE FROM ', cur_table_name);
PREPARE delete_stmt FROM @delete_sql;
EXECUTE delete_stmt;
DEALLOCATE PREPARE delete_stmt;
END LOOP;
CLOSE table_cur;
-- 提交事务
COMMIT;
END //
DELIMITER ;
存储过程使用注意事项
在使用上述存储过程时，需要注意以下几点：
- 动态SQL拼接时要严格校验表名规则，避免SQL注入风险，本示例通过
information_schema获取表名，已经做了规则过滤 - 归档操作涉及数据变更，建议先开启事务，出现异常时及时回滚，避免数据不一致
- 如果分表数据量极大，建议分批插入和删除，避免长事务锁表影响业务
- 可以配合MySQL事件调度器，定期自动执行该存储过程，实现归档自动化
归档效果验证
存储过程执行完成后，可以通过以下SQL验证归档结果：
-- 查看归档表数据量 SELECT table_name, table_rows FROM information_schema.tables WHERE table_name LIKE 'order_archive_%'; -- 查看原分表数据量，确认已清空 SELECT table_name, table_rows FROM information_schema.tables WHERE table_name LIKE 'order_%' AND table_name NOT LIKE 'order_archive_%';
通过存储过程实现分表数据归档，既可以减少手动操作的工作量，也能保证归档逻辑的稳定性和可复用性，适合需要定期处理分表历史数据的业务场景。