如何向SQL表随机插入指定数量重复记录用于批量加载去重测试

所有操作仅限测试环境执行，操作前必须全量备份原表数据；批量插入阶段临时关闭表的非必要二级索引、外键约束、会话级唯一键校验，避免IO/CPU打满拖垮测试库。

• 方案1：原表抽样自插（性能最优，优先选择）
  直接从表现有3亿条存量数据中随机抽样插入，业务判重字段完全复用存量值，生成的记录100%符合重复数据要求，不需要额外造数。
  注意不要用ORDER BY RAND()做随机，3亿级数据全表排序会直接卡死，用主键范围偏移抽样的写法，以MySQL为例，假设表自增主键为id，业务判重字段为biz_unique_key，表名为target_biz_table：

  -- 会话级调优，提升批量插入效率
  SET SESSION bulk_insert_buffer_size = 256 * 1024 * 1024;
  SET SESSION autocommit = 0;
  SET SESSION unique_checks = 0;
  SET SESSION foreign_key_checks = 0;
  
  -- 写分批插入存储过程，每批插1万条，累计插1亿条，避免大事务撑爆回滚段
  DELIMITER //
  CREATE PROCEDURE gen_dup_test_data()
  BEGIN
    DECLARE done_cnt INT DEFAULT 0;
    WHILE done_cnt < 10000 DO
      INSERT INTO target_biz_table (col1, col2, col3, biz_unique_key, create_time, update_time)
      SELECT col1, col2, col3, biz_unique_key, NOW(), NOW()
      FROM target_biz_table
      WHERE id >= FLOOR(RAND() * (SELECT MAX(id) FROM target_biz_table))
      LIMIT 10000;
      COMMIT;
      SET done_cnt = done_cnt + 1;
    END WHILE;
  END //
  DELIMITER ;
  
  -- 执行生成
  CALL gen_dup_test_data();
  -- 清理临时存储过程
  DROP PROCEDURE IF EXISTS gen_dup_test_data();
  -- 恢复参数
  SET SESSION unique_checks = 1;
  SET SESSION foreign_key_checks = 1;
  

  PostgreSQL、Oracle逻辑完全一致，只需要调整存储过程语法适配对应数据库即可，核心逻辑都是主键范围随机抽样+小批量提交。插入时不要带自增主键字段，让数据库自动生成主键值即可，不会和存量主键冲突。

• 方案2：导出导入法（适合需要留存测试数据集的场景）
  如果需要重复使用这批测试数据，或者要跨环境同步数据，可以用导出导入的方式：

  1. 用数据库自带导出工具从原表随机抽取1亿条存量数据，导出时排除自增主键列，以MySQL为例：

     mysqldump -u[用户名] -p[密码] [库名] target_biz_table --no-create-info --where="1=1 LIMIT 100000000" --skip-extended-insert=false > dup_data.sql
     

  2. 调整导出SQL文件的字段列表，剔除自增主键列，分批导入目标表即可，导入时同样开启会话级批量优化参数，速度可以达到每小时千万到亿级。

• 方案3：混合造数法（适合要同时验证漏判、误判率的场景）
  如果测试不仅要验证重复识别能力，还要校验正常数据会不会被误拦，可以在造数时按比例混合：70%的业务判重字段从存量值池随机抽取（生成重复数据），30%生成全新的唯一值（生成正常新数据），用JDBC批量写入的方式导入即可，更贴近真实批量加载的流量特征。

操作校验注意事项 #

• 单批次插入量控制在5000-20000条，不要单次提交百万级以上的大事务，避免锁表、回滚段空间不足。
• 数据插入完成后先执行SQL确认重复数据量达标，再启动业务校验流程：

  -- 统计重复记录总量，biz_unique_key替换为实际业务判重字段
  SELECT COUNT(*) - COUNT(DISTINCT biz_unique_key) AS total_dup_cnt FROM target_biz_table;
  

• 测试完成后回滚数据时，直接按插入时记录的create_time时间范围删除测试数据即可，不会影响原有3亿条存量记录。

内容的提问来源于stack exchange，提问作者user15132810