MySQL数据迁移存储过程：动态分表与分批提交示例

迁移背景与准备条件

数据迁移往往涉及从旧结构向新结构的过渡，特别是当单表数据量过大需要进行水平拆分（Sharding）时。此案例假设存在以下环境：

• 源数据库/表：名为 s servant_591up 的库，源表为 ol_answerresult_56，包含大量答题记录。
• 目标表策略：数据需根据 UserId 分散存储在不同命名的目标表中（如 new_answerresult_0 到 new_answerresult_99），规则为 UserId % 100 决定目标表后缀。
• 权限要求：执行账号需拥有创建存储过程、读写相关表的权限。

核心操作顺序

以下是存储过程的关键步骤解析及完整代码。请注意，在实际运行前需确认目标表已存在，且字段结构与源表一致。

第1步：定义存储环境与变量

首先设置分隔符，避免存储过程中的 ; 导致提前结束。接着声明一系列局部变量，用于暂存从游标读取的每一行数据。这里使用了多种数据类型，包括 BIGINT、VARCHAR、TEXT 等，以适配可能的复杂数据结构。

DELIMITER $$
USE `servant_591up`$$
DROP PROCEDURE IF EXISTS `sp_move_data`$$
CREATE PROCEDURE `sp_move_data`()
BEGIN
-- 声明变量
DECLARE v_exit INT DEFAULT 0;
DECLARE v_id BIGINT;
-- ... (其他变量声明) ...
END$$
DELIMITER ;

第2步：配置游标查询源数据

定义游标 c_ids，指定需要迁移的具体字段。为了确保精确迁移，仅选择必要的业务字段而非使用 SELECT * ，这有助于避免冗余数据并提高解析效率。

DECLARE c_ids CURSOR FOR 
SELECT UniqueKey,TagCatalogId,RootCatalogId,UserId,QuestionId,
CorrectCount,ErrorCount,LastIsCorrect,LastAnswerXML,TotalCostTime,
Reviews,AnswerResultCategory,LastCostTime,LastAnswerTime,IsPublic,
SUBJECT,TotalCount,AnswerMode,ExerciseWeight 
FROM ol_answerresult_56;

第3步：构建动态 SQL 实现分表写入

这是最关键的一步。在循环中，程序首先获取当前记录的 UserId，通过 MOD(v_UserId, 100) 计算出对应的目标表索引 c_table。随后，使用 CONCAT 函数拼接 INSERT INTO 语句。

注意特殊字符处理：代码中对字符串类型的字段（如 Reviews、LastAnswerXML）增加了引号包裹，并对可能包含单引号的内容使用了 REPLACE 进行处理（尽管原始代码中的替换逻辑较简单，但在实际生产中建议预处理或使用参数绑定以避免 SQL 注入或语法错误）。

SELECT MOD(v_UserId,100) INTO c_table;
SET @SQL_CONTEXT = CONCAT(
'INSERT INTO new_answerresult_', c_table,
' (...) values(...)'
);
PREPARE STMT FROM @SQL_CONTEXT;
EXECUTE STMT ;
DEALLOCATE PREPARE STMT;

第4步：异常处理与关闭资源

使用 CONTINUE HANDLER FOR NOT FOUND 来优雅地终止循环，防止因数据读完而报错。循环结束后，务必关闭游标以释放内存。

完成判断与排错提示

完成状态：当程序执行完毕且无错误抛出，即视为迁移成功。可以通过对比源表和目标表的行数来验证数据的完整性。

常见风险提示：

1. 分批提交（Commit）：原文代码中注释掉了 #IF MOD(i,100)=0 THEN COMMIT;。对于超大数据量，逐条提交会导致磁盘 IO 频繁，极大降低速度；而不提交则可能导致事务日志（Binlog/Redo Log）过大撑爆磁盘。建议：** 对于数百万级数据，建议取消注释或调整阈值进行定期提交。
2. 死锁与锁表：在高并发环境下执行此类迁移可能会产生锁竞争。建议在低峰期执行，或在应用层做好暂停写入的准备。
3. SQL 注入风险：虽然此处是内部迁移脚本，但动态拼接 SQL 始终存在风险。确保源数据中的字符串字段不包含未转义的特殊符号。

简要总结

该存储过程提供了一种经典的基于游标的 ETL（抽取、转换、加载）方案。它清晰地展示了如何在 MySQL 原生环境中处理分表逻辑和动态表名切换。对于不需要引入外部中间件（如 Kettle、DataX）的小型迁移任务，这是一个轻量且可控的工具。 以上就是 MySQL 数据迁移存储过程分享：动态分表与分批提交示例的详细内容，更多关于 MySQL 数据处理优化的资料请关注本站其它相关文章！