1. 为什么需要批量生成测试数据？

在数据库开发和测试过程中，我们经常需要大量数据来验证系统性能、测试查询效率或模拟真实业务场景。手动逐条插入数据不仅效率低下，而且难以保证数据的多样性和随机性。这就是为什么我们需要掌握批量生成测试数据的技巧。

以我最近参与的一个电商项目为例，我们需要测试订单系统在百万级数据量下的查询性能。手动创建100万条订单数据几乎不可能，而使用批量生成方法，我们可以在几分钟内完成这个任务。这不仅节省了时间，还能确保数据具有足够的随机性和真实性。

2. 三种主流数据库的批量数据生成方法

2.1 MySQL批量数据生成方案

MySQL提供了几种高效的批量数据生成方法，不需要依赖存储过程：

-- 方法1：使用INSERT多值语法 INSERT INTO test_table (id, name, age, create_time) VALUES (1, '张三', 25, NOW()), (2, '李四', 30, NOW()), (3, '王五', 28, NOW()); -- 可以一次性插入上千条记录 -- 方法2：使用UNION ALL生成序列 INSERT INTO test_table (id, name, age) SELECT n AS id, CONCAT('用户', n) AS name, FLOOR(18 + RAND() * 50) AS age FROM ( SELECT a.N + b.N * 10 + c.N * 100 + d.N * 1000 + e.N * 10000 AS n FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a CROSS JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b CROSS JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c CROSS JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d CROSS JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) e ) numbers WHERE n BETWEEN 1 AND 100000;

提示：MySQL单次事务处理的数据量有限制，建议分批提交，每批1万条左右。

2.2 Oracle批量数据生成方案

Oracle数据库虽然语法略有不同，但同样支持高效的批量数据生成：

-- 方法1：使用INSERT ALL语法 INSERT ALL INTO test_table (id, name, age) VALUES (1, '张三', 25) INTO test_table (id, name, age) VALUES (2, '李四', 30) INTO test_table (id, name, age) VALUES (3, '王五', 28) SELECT * FROM dual; -- 方法2：使用CONNECT BY生成序列 INSERT INTO test_table (id, name, age, create_date) SELECT LEVEL AS id, '用户' || LEVEL AS name, FLOOR(18 + DBMS_RANDOM.VALUE(0, 50)) AS age, SYSDATE - DBMS_RANDOM.VALUE(0, 365) AS create_date FROM dual CONNECT BY LEVEL <= 100000;

Oracle的CONNECT BY语法特别适合生成序列数据，结合DBMS_RANDOM包可以轻松创建随机数据。

2.3 PostgreSQL批量数据生成方案

PostgreSQL提供了generate_series函数，是生成测试数据的利器：

-- 方法1：使用generate_series函数 INSERT INTO test_table (id, name, age, created_at) SELECT n AS id, '用户' || n AS name, (18 + (random() * 50)::integer) AS age, now() - (random() * interval '365 days') AS created_at FROM generate_series(1, 100000) AS n; -- 方法2：使用WITH子句生成多表关联数据 WITH users AS ( SELECT n AS user_id, '用户' || n AS username, (18 + (random() * 50)::integer) AS age FROM generate_series(1, 10000) AS n ), orders AS ( SELECT n AS order_id, (random() * 10000)::integer % 10000 + 1 AS user_id, (random() * 1000)::numeric(10,2) AS amount, now() - (random() * interval '365 days') AS order_date FROM generate_series(1, 100000) AS n ) INSERT INTO order_details SELECT o.order_id, u.user_id, u.username, o.amount, o.order_date FROM orders o JOIN users u ON o.user_id = u.user_id;

PostgreSQL的random()函数和generate_series()组合使用可以创建非常真实的测试数据。

3. 高级数据生成技巧

3.1 生成更真实的数据

简单的序列和随机数往往不够真实，我们可以使用一些技巧生成更符合业务场景的数据：

-- MySQL示例：生成更真实的姓名和地址 INSERT INTO customers (id, name, email, phone, address) SELECT n AS id, CONCAT( ELT(FLOOR(1 + RAND() * 100) % 10 + 1, '张', '王', '李', '赵', '刘', '陈', '杨', '黄', '吴', '周'), ELT(FLOOR(1 + RAND() * 100) % 10 + 1, '伟', '芳', '娜', '秀英', '敏', '静', '丽', '强', '磊', '军') ) AS name, CONCAT('user', n, '@example.com') AS email, CONCAT('1', FLOOR(3 + RAND() * 7), FLOOR(1000 + RAND() * 9000), FLOOR(1000 + RAND() * 9000)) AS phone, CONCAT( ELT(FLOOR(1 + RAND() * 100) % 30 + 1, '北京', '上海', '广州', '深圳', '杭州', '南京', '成都', '重庆', '武汉', '西安'), ELT(FLOOR(1 + RAND() * 100) % 10 + 1, '市朝阳区', '市海淀区', '市浦东新区', '市天河区', '市余杭区', '市鼓楼区', '市武侯区', '市渝中区', '市江岸区', '市雁塔区'), ELT(FLOOR(1 + RAND() * 100) % 10 + 1, '建国路', '中关村大街', '陆家嘴环路', '体育西路', '文一西路', '中山路', '人民南路', '解放碑', '江汉路', '小寨路'), FLOOR(1 + RAND() * 200), '号' ) AS address FROM ( SELECT a.N + b.N * 10 + c.N * 100 + d.N * 1000 AS n FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a CROSS JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b CROSS JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c CROSS JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d ) numbers WHERE n BETWEEN 1 AND 10000;

3.2 生成关联数据

实际业务中，数据往往存在复杂的关联关系。我们可以使用以下方法生成关联数据：

-- PostgreSQL示例：生成用户和订单关联数据 WITH users AS ( SELECT user_id, 'user_' || user_id AS username, (random() * 50 + 18)::integer AS age FROM generate_series(1, 10000) AS user_id ), products AS ( SELECT product_id, '产品_' || product_id AS product_name, (random() * 900 + 100)::numeric(10,2) AS price FROM generate_series(1, 100) AS product_id ) INSERT INTO orders (order_id, user_id, product_id, quantity, order_date) SELECT order_id, (random() * 9999)::integer + 1 AS user_id, (random() * 99)::integer + 1 AS product_id, (random() * 9)::integer + 1 AS quantity, now() - (random() * interval '365 days') AS order_date FROM generate_series(1, 100000) AS order_id;

3.3 性能优化技巧

生成百万级数据时，性能至关重要。以下是一些优化建议：

1. 禁用索引和约束：在插入数据前临时禁用索引、外键约束等，插入完成后再重建。

-- MySQL示例 ALTER TABLE test_table DISABLE KEYS; -- 执行批量插入 ALTER TABLE test_table ENABLE KEYS;

2. 使用事务分批提交：将大数据量分成多个事务提交，避免单个事务过大。

-- Oracle示例 BEGIN FOR i IN 1..100 LOOP INSERT INTO test_table SELECT ... FROM ... WHERE ...; IF MOD(i, 10) = 0 THEN COMMIT; END IF; END LOOP; COMMIT; END;

3. 调整数据库参数：临时增大内存缓冲区、日志缓冲区等参数。

4. 实际应用案例

4.1 电商平台测试数据生成

假设我们需要为电商平台生成测试数据，包括用户、商品、订单和评价：

-- MySQL电商测试数据生成 -- 1. 生成10万用户 INSERT INTO users (user_id, username, email, register_date) SELECT n AS user_id, CONCAT('user', n) AS username, CONCAT('user', n, '@example.com') AS email, NOW() - INTERVAL FLOOR(RAND() * 365) DAY AS register_date FROM ( -- 生成1-100000的序列 SELECT a.N + b.N * 10 + c.N * 100 + d.N * 1000 AS n FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a CROSS JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b CROSS JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c CROSS JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d ) numbers WHERE n BETWEEN 1 AND 100000; -- 2. 生成1万种商品 INSERT INTO products (product_id, product_name, price, stock) SELECT n AS product_id, CONCAT('商品', n) AS product_name, ROUND(10 + RAND() * 990, 2) AS price, FLOOR(RAND() * 1000) AS stock FROM ( SELECT a.N + b.N * 10 + c.N * 100 AS n FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a CROSS JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b CROSS JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c ) numbers WHERE n BETWEEN 1 AND 10000; -- 3. 生成100万订单 INSERT INTO orders (order_id, user_id, product_id, quantity, order_date) SELECT n AS order_id, FLOOR(1 + RAND() * 100000) AS user_id, FLOOR(1 + RAND() * 10000) AS product_id, FLOOR(1 + RAND() * 10) AS quantity, NOW() - INTERVAL FLOOR(RAND() * 365) DAY AS order_date FROM ( SELECT a.N + b.N * 10 + c.N * 100 + d.N * 1000 + e.N * 10000 AS n FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a CROSS JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b CROSS JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c CROSS JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d CROSS JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) e ) numbers WHERE n BETWEEN 1 AND 1000000;

4.2 社交网络测试数据生成

对于社交网络应用，我们需要生成用户、好友关系和发帖数据：

-- PostgreSQL社交网络测试数据 -- 1. 生成用户数据 INSERT INTO users (user_id, username, join_date, last_login) SELECT user_id, 'user_' || user_id AS username, now() - (random() * interval '365 days') AS join_date, now() - (random() * interval '7 days') AS last_login FROM generate_series(1, 100000) AS user_id; -- 2. 生成好友关系（约500万条） INSERT INTO friendships (user_id, friend_id, friendship_date) SELECT (random() * 99999)::integer + 1 AS user_id, (random() * 99999)::integer + 1 AS friend_id, now() - (random() * interval '365 days') AS friendship_date FROM generate_series(1, 5000000) WHERE user_id != friend_id ON CONFLICT DO NOTHING; -- 3. 生成发帖数据（约200万条） INSERT INTO posts (post_id, user_id, content, post_time) SELECT post_id, (random() * 99999)::integer + 1 AS user_id, '这是用户' || ((random() * 99999)::integer + 1) || '的第' || post_id || '条帖子内容。' AS content, now() - (random() * interval '365 days') AS post_time FROM generate_series(1, 2000000) AS post_id;

5. 常见问题与解决方案

5.1 内存不足问题

生成大量数据时，可能会遇到内存不足的错误。解决方案包括：

1. 分批处理：将数据生成分成多个小批次
2. 调整数据库配置：增加sort_buffer_size、join_buffer_size等参数
3. 使用临时表：将中间结果存储在临时表中

5.2 主键冲突问题

随机生成数据时可能会出现主键冲突。解决方法：

-- MySQL使用IGNORE关键字忽略冲突 INSERT IGNORE INTO table_name ...; -- PostgreSQL使用ON CONFLICT DO NOTHING INSERT INTO table_name ... ON CONFLICT DO NOTHING; -- Oracle使用MERGE语句 MERGE INTO table_name t USING (SELECT ... FROM dual) s ON (t.id = s.id) WHEN NOT MATCHED THEN INSERT ...;

5.3 性能优化实测

在我的测试环境中，对不同的数据生成方法进行了性能比较：

实测表明，使用纯SQL的批量生成方法通常比存储过程更高效，特别是在MySQL和PostgreSQL中。

5.4 数据质量控制

为确保生成的测试数据质量，建议：

1. 添加数据校验：生成后检查数据分布是否符合预期
2. 使用更复杂的随机算法：如正态分布而非均匀分布
3. 保留生成脚本：便于重复生成和修改

-- 数据分布检查示例 SELECT COUNT(*) AS total, AVG(age) AS avg_age, MIN(age) AS min_age, MAX(age) AS max_age FROM users; -- 检查年龄分布 SELECT FLOOR(age/10)*10 AS age_range, COUNT(*) AS count FROM users GROUP BY FLOOR(age/10)*10 ORDER BY age_range;

在实际项目中，我发现合理设置随机数种子可以确保每次生成的数据相同，便于测试结果的可重复性。例如在PostgreSQL中：

-- 设置随机种子 SELECT setseed(0.123); -- 然后执行数据生成SQL

这种方法在需要重现特定测试场景时特别有用。