如何在MySQL里通过存储过程快速生成百万级测试数据？

必须分批插入并显式事务控制，每1000–5000行COMMIT一次，避免锁表、日志爆炸和超时；禁用自动提交，插入前禁用二级索引，慎用MEMORY表和递归CTE。

存储过程单次循环插入百万行太慢，必须分批+事务控制

直接用 WHILE 循环插入 100 万次，每条 INSERT 都走一次磁盘刷写，实际耗时可能超 30 分钟甚至失败。关键不是“能不能”，而是“怎么避免锁表、日志爆炸和超时”。

• 必须用 START TRANSACTION 包裹整个循环，而不是每次 INSERT 都开事务
• 建议每 10000 行 COMMIT 一次（SET i = i + 1 后加判断），避免 undo log 膨胀导致 ERROR 1205 (HY000): Deadlock found
• 调用前先关掉自动提交：SET autocommit = 0，否则 COMMIT 无效
• 如果目标表有大量二级索引，插入前执行 ALTER TABLE t DISABLE KEYS，插完再 ENABLE KEYS

MySQL 5.7 和 8.0 的随机数据生成函数写法差异

RAND() 本身没问题，但拼接字符串、生成身份证号或手机号时，5.7 缺少 GENERATED COLUMN 和更严格的类型推断，容易在 CONCAT 中隐式转成科学计数法（比如 13800000000+i 到 100 万时变成 1.38e10）。

• 手机号务必用 LPAD(FLOOR(RAND()*10000000000), 11, '1') 或固定前缀 + FLOOR(RAND()*1000000) 拼接，别直接加整数
• MySQL 8.0 可用 UUID_SHORT() 替代自增主键做分布式 ID，但注意它不连续，不适合性能压测场景
• 身份证号不能纯随机——最后一位是校验码，简单模拟可用 CONCAT('110101', LPAD(FLOOR(RAND()*999999), 6, '0'), '0000', FLOOR(RAND()*10))，跳过校验逻辑但保证长度合法

内存表（MEMORY）加速写入的实操陷阱

把数据先写进 ENGINE=MEMORY 表再 INSERT INTO ... SELECT 到目标表，确实能提速 3–5 倍，但这个“快”是有代价的。

• max_heap_table_size 必须大于预估内存表总大小（行数 × 平均行长），否则插入中途报 ERROR 1114 (HY000): The table is full
• 修改该参数后必须重启 MySQL，且重启后所有 MEMORY 表数据丢失——别指望它持久化
• MEMORY 表不支持 BLOB/TEXT 字段，含长文本的表无法用此法加速
• 如果目标表有外键约束，先在内存表里建相同结构（不含外键），插入完成后再关外键检查：SET FOREIGN_KEY_CHECKS = 0

为什么递归 CTE 在 MySQL 8.0 中慎用于百万级生成

虽然 WITH RECURSIVE 看起来简洁，但它的执行模型是深度优先递归展开，内存占用随层级指数增长。生成 100 万行时，MySQL 常因 ERROR 1643 (HY000): Recursive query aborted after 1000 iterations 中断。

• 默认递归深度限制是 1000，可通过 SET cte_max_recursion_depth = 1000000 临时调高，但极易触发 OOM
• CTE 生成的数据无法被优化器重写为批量插入，仍是一行一行构造，实际速度不如带事务的存储过程
• 真正适合 CTE 的是生成有序序列（如日期范围、ID 区间），而非带随机计算的业务字段

真正卡点不在语法对不对，而在你有没有提前算过：单行平均占多少字节、内存表够不够、binlog 日志会不会撑爆磁盘、以及那条没加 DELIMITER $$ 就执行的存储过程，已经悄悄把你的会话搞崩了。