如何在MySQL存储过程中批量插入百万级测试数据-mysql教程-PHP中文网

mysql存储过程批量插入百万级数据唯一高效方案是关事务+拼接多值insert+分段控制；bulk insert不支持，游标和单条insert循环性能极差，必须禁用unique_checks、foreign_key_checks并调优底层参数。

如何在mysql存储过程中批量插入百万级测试数据

直接说结论：MySQL 存储过程里插百万级数据，BULK INSERT 不能用（那是 SQL Server 的），游标 DECLARE ... CURSOR 基本等于自废武功，单条循环 INSERT 更是慢到离谱——100 万行跑 20 分钟以上很常见。真正能压到 30 秒级的，只有「关事务 + 拼接多值 INSERT + 分段控制」这一条路。

为什么不能用游标或单条 INSERT 循环

游标在 MySQL 里不是“批量”工具，而是单行提取机制：每次 FETCH 都触发一次引擎层调用、一次日志写入、一次索引更新。实测插入 10 万行，比纯 WHILE 循环还慢 3–5 倍。更致命的是它隐式占用大量内存，容易撞上 max_heap_table_size 或锁等待超时。

INSERT INTO t VALUES (1),(2),(3) 是 MySQL 原生优化过的语法，parse/plan 可复用；而游标+单条 INSERT 每次都要重新解析
逐行插入无法跳过唯一约束检查开销，哪怕你只是造测试数据
每条语句都走完整事务流程（即使没显式 COMMIT），自动提交模式下 I/O 放大到不可接受

必须关闭的三个默认行为

不关它们，再好的拼接逻辑也白搭。这些不是“可选优化”，是硬性前提：

执行 SET UNIQUE_CHECKS = 0：插入前禁用唯一索引检查，结束后再设回 1
执行 SET FOREIGN_KEY_CHECKS = 0：避免外键约束反复校验（测试数据通常无关联依赖）
显式 START TRANSACTION + 批量后 COMMIT：绝不能依赖自动提交，否则每行都刷盘

注意：UNIQUE_CHECKS 和 FOREIGN_KEY_CHECKS 是会话级变量，只影响当前存储过程执行过程，无需担心污染其他连接。

拼接多值 INSERT 的安全边界

靠 CONCAT() 拼 SQL 字符串是可行的，但必须卡死长度和批次。MySQL 默认 max_allowed_packet = 4MB，按平均每行 100 字节算，10000 行就逼近上限；一旦截断，INSERT 会静默失败或报错 ERROR 1153 (08S01): Got a packet bigger than 'max_allowed_packet' bytes。

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

推荐每批严格控制在 2000 行左右：小了（如 500）仍频繁提交，大了（如 5000）易触发截断
不要用 PREPARE/EXECUTE 动态执行——它在循环里反而更慢，且无法复用解析结果
字符串拼接时，末尾记得补 ;，开头用 INSERT INTO t(col1,col2) VALUES 固定前缀，避免 VALUES 重复拼接

示例片段（插入 user_test 表）：

SET @sql = CONCAT('INSERT INTO user_test(name, mobile) VALUES ', 
  '(\'姓名1\', \'13800000001\'),(\'姓名2\', \'13800000002\')');
SET @sql = CONCAT(@sql, ',(\'姓名3\', \'13800000003\')'); -- 继续追加
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;