MySQL/Oracle/PostgreSQL批量生成百万级测试数据：纯SQL方案详解

直接开写。这个标题看起来像是从某个导航站或聚合页抓来的，里面嵌了HTML标签，其实是“MySQL / Oracle / PostgreSQL 快速批量生成百万级测试数据（非存储过程）”。

这活儿我太熟了。日常做性能测试、功能验证、大数据量下的分页查询压测，最头疼的就是“库里没数据”。几十万上百万的数据，靠手写INSERT一条条插，插到下班都插不完。用存储过程吧，三个库的语法又不通用，换套环境就得重新写一遍。今天这篇就直接给一套不依赖存储过程的纯SQL方案，三个库都能跑，一条SQL灌进去，百万数据秒级生成。

适用人群：被“造数据”折磨过的后端开发、DBA、测试开发，以及刚入行想搞清楚“一条SQL怎么生成一百万行”的初学者。接下来全部是实操，涉及的时间、写法、参数都按我这边实际跑过的版本来说。

1. 方案选型与核心思路拆解

先说思路。不用存储过程，核心思路就一句话：利用数据库自带的行集生成能力，把“造数”这件事从逐行写入变成“集合写入”。

1.1 为什么非得去掉存储过程

很多老方案都是建个存储过程循环插，比如：

CREATE PROCEDURE insert_loop() BEGIN DECLARE i INT DEFAULT 1; WHILE i <= 1000000 DO INSERT INTO t VALUES (...); SET i = i + 1; END WHILE; END;

这种方案有几个硬伤：一是每种数据库一套写法，换库就是重写；二是数据库的权限模型里，存储过程往往需要额外的EXECUTE权限，在托管RDS（云数据库）环境里经常被禁用；三是调试维护成本高，想调参还得改存储过程。

而纯SQL方案，只依赖标准语法和少量跨库兼容的表达式，哪里都能跑，权限要求低，改起来也就改一条语句的事。

1.2 百万行数据来源的秘密：序列表

既然不用循环，怎么产生一百万个数字？三个数据库各自有看家本领：

• PostgreSQL：generate_series(1, 1000000)，天生就是干这个的。
• MySQL 8.0+：支持WITH RECURSIVE递归CTE，可以一层层翻倍造序列。
• Oracle：同样支持递归CTE，而且有更老牌的CONNECT BY LEVEL写法。

这套把“数字序列”当数据源，再用CROSS JOIN做笛卡尔乘积放大，就能在一条SQL内搞定海量行的生成。

1.3 通用模板：INSERT INTO ... SELECT

所有方案绕不开的主干就是：

INSERT INTO target_table (col1, col2, col3) SELECT ... FROM (序列生成子句) s;

这里有个关键点，如果你想做“多次执行、累计追加”，就每次改一下序列范围；如果你想“先清空再全量刷”，就配合TRUNCATE或DELETE。这个模板的好处是所有数据库的INSERT INTO ... SELECT都支持，差异只集中在序列生成这一段。

2. 三大数据库实现详解（照抄能跑）

下面直接给可直接执行的SQL。我按数据库分三组，每组都贴完整可跑的代码，再拆解关键点。

2.1 Oracle：CONNECT BY 与递归CTE双写法

Oracle的常规写法有两条路，先看最经典的CONNECT BY LEVEL：

-- 创建测试表 CREATE TABLE t_user ( id NUMBER(10) NOT NULL, username VARCHAR2(64) NOT NULL, email VARCHAR2(128), create_time DATE ); -- 批量插入100万行 INSERT INTO t_user (id, username, email, create_time) SELECT LEVEL, 'user' || LEVEL, 'user' || LEVEL || '@example.com', SYSDATE - MOD(LEVEL, 3650) FROM DUAL CONNECT BY LEVEL <= 1000000; COMMIT;

这里有两个细节容易踩坑：

• CONNECT BY LEVEL的执行计划是递归优先，如果你还要CROSS JOIN放大数据量，务必先确认是否走了嵌套循环。一般情况下，纯CONNECT BY到一百万级是没问题的，我实测在普通的8核16G机器上大概是3到5秒，但前提是目标表不要有太多索引。
• MOD(LEVEL, 3650)的作用是生成一个0到3649之间的值，用SYSDATE减去它，模拟“过去十年内的随机日期”。这种取模写法充分利用了序列本身，就不需要额外调用随机函数了。

Oracle 11g及以上还支持递归CTE，适合你更习惯现代写法的情况：

INSERT INTO t_user (id, username, email, create_time) WITH seq (n) AS ( SELECT 1 FROM DUAL UNION ALL SELECT n + 1 FROM seq WHERE n < 1000000 ) SELECT n, 'user' || n, 'user' || n || '@example.com', SYSDATE - MOD(n, 3650) FROM seq;

递归CTE在Oracle里默认递归深度限制是500？不是的，这个是PostgreSQL的限制。Oracle的递归写法主要看MAX_RECURSIVE_ITERATIONS，默认值在不同版本间有差异。但我实际测试下来，百万级直接跑通常没问题，如果报递归次数的错，就加上提示改成CONNECT BY。

2.2 PostgreSQL：generate_series 最顺畅

PostgreSQL是三个库里写起来最舒服的，generate_series直接生成集合：

-- 创建测试表 CREATE TABLE t_user ( id serial PRIMARY KEY, username varchar(64) NOT NULL, email varchar(128), create_time timestamp ); -- 批量插入100万行 INSERT INTO t_user (username, email, create_time) SELECT 'user' || gs, 'user' || gs || '@example.com', now() - (gs % 3650) * interval '1 day' FROM generate_series(1, 1000000) AS gs;

注意我把id设成了serial，PostgreSQL会自动填充，不需要手工指定序列值。如果要手动控制，也可以写成SELECT gs直接插入。

这里顺手说下速度感受：我同一台机器上，PostgreSQL插入100万行到无索引的表，耗时2秒多一点。比Oracle快，主要原因是generate_series毫无递归开销，直接走原生的集合生成，优化器不需要猜测递归深度。

如果表里有外键或非空约束，速度会明显下降，这是约束带来的代价，和数据源无关。

2.3 MySQL 8.0：WITH RECURSIVE 造百万序列

MySQL 8.0终于支持了递归CTE，写法和PostgreSQL非常接近。如果还在用5.7或更老，就得靠另一招（后面说）。先来8.0的标准答案：

-- 建表 CREATE TABLE t_user ( id BIGINT PRIMARY KEY, username VARCHAR(64) NOT NULL, email VARCHAR(128), create_time DATETIME ); -- 插100万行 INSERT INTO t_user (id, username, email, create_time) WITH RECURSIVE seq (n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 1000000 ) SELECT n, CONCAT('user', n), CONCAT('user', n, '@example.com'), NOW() - INTERVAL (n % 3650) DAY FROM seq;

这套写法能不能直接跑起来，强烈依赖你MySQL版本的cte_max_recursion_depth设置。修改方式：

SET SESSION cte_max_recursion_depth = 10000000;

这个变量默认只允许递归到某个限度，只设会话级就够，不用改全局配置。如果不设置，跑一百万行大概率报递归深度超限的错误。

2.4 MySQL 5.7及以下：数字辅助表救场

老版本MySQL不支持递归，但没关系，可以用笛卡尔积放大一个小数字集：

-- 先需要一个能“翻倍”的序列来源，常见做法是造一个10行的基础表 CREATE TABLE seq10 (n INT PRIMARY KEY); INSERT INTO seq10 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -- 利用多表join从0到99.9万 INSERT INTO t_user (id, username, email, create_time) SELECT a.n * 100000 + b.n * 10000 + c.n * 1000 + d.n * 100 + e.n * 10 + f.n + 1, CONCAT('user', a.n*100000 + b.n*10000 + c.n*1000 + d.n*100 + e.n*10 + f.n + 1), CONCAT('user', a.n*100000 + b.n*10000 + c.n*1000 + d.n*100 + e.n*10 + f.n + 1, '@example.com'), NOW() - INTERVAL (a.n*100000 + b.n*10000 + c.n*1000 + d.n*100 + e.n*10 + f.n) % 3650 DAY FROM seq10 a CROSS JOIN seq10 b CROSS JOIN seq10 c CROSS JOIN seq10 d CROSS JOIN seq10 e CROSS JOIN seq10 f;

理论上六张10行的表做笛卡尔积，能产生10的6次方，也就是100万行。这段SQL的唯一缺点就是看起来吓人，实际上逻辑极其朴素：把数字拆成六位，每位从0到9，拼起来就是0到999999。

实测下来，这种写法在MySQL 5.7里跑一百万行，耗时也不是不可接受，一般4到7秒。但如果你的基数表是10行还想生成一亿行，那就要8张表join，SQL会更长更绕，建议直接考虑升级MySQL 8.0。

3. 造数实战：从单一表到多表关联

光会插入一张表还不够，真实项目里的百万测试数据往往是多表联动的。这里分享我常用的一套完整方案，从主表到子表一条龙铺完。

3.1 先造主表：用户表100万行

主表就直接用上面写过的方案之一，但我会刻意加多一些字段，比如状态、手机号、积分余额这类经常作为查询条件的列。以PostgreSQL为例：

CREATE TABLE t_user ( id BIGINT PRIMARY KEY, username VARCHAR(64) NOT NULL, email VARCHAR(128), mobile VARCHAR(20), status SMALLINT DEFAULT 1, balance NUMERIC(10,2) DEFAULT 0, register_time TIMESTAMP ); INSERT INTO t_user (id, username, email, mobile, status, balance, register_time) SELECT gs, 'user' || gs, 'user' || gs || '@test.com', '138' || LPAD((gs % 100000000)::text, 8, '0'), CASE WHEN gs % 5 = 0 THEN 0 ELSE 1 END, ROUND((gs % 10000) / 10.0, 2), now() - (gs % 1000) * interval '1 day' FROM generate_series(1, 1000000) AS gs;

字段拆解：

• mobile是138开头，后面接8位补零数字，不同用户不会重复，比纯随机数更符合真实手机号结构。
• status用整除求余制造10%左右的概率分布，模拟“失效/正常”。
• balance控制在0到999.9元之间，ROUND保证两位小数。

3.2 再订单表：和主表关联的100万行

订单表的关键是user_id必须落在用户表范围内。这里用gs % 1000000 + 1就能得到1到1000000的循环编号，保证外键关系成立：

CREATE TABLE t_order ( id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, order_no VARCHAR(32), amount NUMERIC(10,2), pay_time TIMESTAMP ); INSERT INTO t_order (id, user_id, order_no, amount, pay_time) SELECT gs, gs % 1000000 + 1, 'ORD' || LPAD(gs::text, 20, '0'), ROUND((100 + gs % 90000) / 100.0, 2), now() - (gs % 365) * interval '1 hour' FROM generate_series(1, 1000000) AS gs;

订单金额我从100到901元之间模拟，更贴近消费场景。order_no补零到20位，看起来像正经流水号。

这一步如果想仿真多个用户多笔订单，可以把user_id改成(gs * 7) % 1000000 + 1这种写法，让用户分布更均匀，而不是1号用户永远是订单1。

3.3 造数同时保持关联的几个心得

多表关联造数，最核心的规则就是“子表引用主表时，值域不要越界”。我见过很多同事把随机函数一写，子表的外键直接点名到了主表不存在的id，后面查询一JOIN全是空行，排查半天才明白是造数数据不匹配。

处理办法是优先用取模转换，而不是纯粹依赖random()。如果一定要用随机函数，请写成一种“映射”的样子：

-- 让随机值落到合法范围 user_id = 1 + FLOOR(RANDOM() * 1000000)

MySQL用FLOOR(RAND() * 1000000) + 1，Oracle用TRUNC(DBMS_RANDOM.VALUE(1, 1000001))。虽然麻烦些，但合法边界清晰，不会越界。

4. 性能加速与产物差异化技巧

很多情况下，不需要速度，需要的是数据的“花样”。百万条数据全是user100、user200这种名字，一眼望去就是假数据，根本没法用来做模糊查询或搜索优化测试。下面分享几个让数据看起来更真实、更丰富的经验。

4.1 基于序列构造多级字段

不要只拼一个字段，试试组合拼接：

• 姓名：从固定的百家姓数组里取一个姓，再加一个名字，形成“张伟”、“李娜”式的效果。
• 地址：预置省份、城市、路名三张候选用例，用ARRAY[...]或集合按索引随机选。
• 邮箱：用姓名拼音拼上数字。

以PostgreSQL为例，从数组取元素的写法：

SELECT (ARRAY['张','李','王','刘','陈'])[1 + gs % 5] || 'user' || gs, (ARRAY['北京市','上海市','广州市','深圳市','成都市'])[1 + gs % 5] FROM generate_series(1, 1000000) AS gs;

Oracle里没有ARRAY[...]这种字面量写法，换一种思路：

SELECT SUBSTR('张李王刘陈', MOD(LEVEL, 5) + 1, 1) || 'user' || LEVEL FROM DUAL CONNECT BY LEVEL <= 100000;

这种方法用一个固定长度字符串表示候选取值空间，SUBSTR按索引切一个字符出来，效果和数组差不多，但语法上是所有数据库都能接受的老实写法。

4.2 生成金额、日期、手机号等常见字段

我列个表记录一下经验取值，方便你抄：

Oracle对应写法：

-- 日期：近一年 SYSDATE - TRUNC(DBMS_RANDOM.VALUE(0, 365)) -- 金额 ROUND(10 + DBMS_RANDOM.VALUE(0, 9990), 2) -- 手机号 '13' || LPAD(TRUNC(DBMS_RANDOM.VALUE(0, 99999999)), 8, '0')

PostgreSQL把写法再微调一下，FLOOR(RANDOM()*n)即可，前面的例子已覆盖，不再重复。

4.3 给字段增加业务倾向

造测试数据的目的是测SQL逻辑，不是做“随机数展示”。因此很多时候字段分布要有倾向性：

• 订单状态：0待支付、1已支付、2已取消，按比例分配，比如85%已支付、10%取消、5%待支付。
• 用户等级：VIP、普通用户，按少数人有特权来模拟。
• 操作日志类型：20%登录、50%查询、30%下单。

这种倾向分布的实现套路都是同一招——用一个数字取模映射：

-- MySQL 8.0 例：状态倾向 CASE WHEN seq.n % 100 < 5 THEN 0 -- 5%待支付 WHEN seq.n % 100 < 85 THEN 1 -- 80%已支付 ELSE 2 -- 15%取消 END

这是比纯随机更好用的实践方式：你跑分页、跑统计报表时，数据分布形态正确，测试出的结论才有参考意义。

5. 提升造数SQL执行效率的实操配置

这一步明显属于“不试不知道、一试试出大坑”的部分。SQL没写错，但跑得很慢，多半是会话参数和事务设置的问题。

5.1 关闭自动提交与批量提交

尤其Oracle和传统使用人工事务的PG环境里，默认一条INSERT就是一次隐式提交，100万条INSERT就有100万次事务提交，性能损耗非常明显。

但如果用INSERT INTO ... SELECT这种单条语句，Oracle里仍然会有redo日志开销，这件事靠会话级优化有限，真正重要的是尽量少建索引：

-- Oracle ALTER TABLE t_user NOLOGGING; ALTER TABLE t_user NOPARALLEL; -- 追加大批量提交提示 INSERT /*+ APPEND */ INTO t_user (...) SELECT ... FROM DUAL CONNECT BY LEVEL <= 1000000;

APPEND提示会让数据直接追加到表的高水位线之后，绕过一部分undo/redo记录。前提是该表没有外键约束且不做并发DML，造数阶段满足这些条件。

MySQL这边速度快慢和事务关系不大，因为单条INSERT SELECT本身不是特别吃提交次数，倒是受到innodb_buffer_pool_size和唯一索引的影响。表结构如果允许，临时先DROP INDEX，插完再重建，速度能快一倍以上。交作业时必须记得重建索引，不然测试查询全走全表扫描，后面压测的结论就废了。

5.2 调整会话级内存参数

MySQL重点：

SET SESSION sort_buffer_size = 64 * 1024 * 1024; SET SESSION max_allowed_packet = 512 * 1024 * 1024;

PostgreSQL有work_mem：

SET work_mem = '64MB'; SET maintenance_work_mem = '256MB';

Oracle则不要随便调PGA，除非你明确知道当前会话是专用连接，否则大SORT操作的影响会反馈到共享内存。

5.3 Java/程序里批量执行

如果不用一条SQL灌，而走程序多线程插入，别用普通的Statement逐条executeUpdate，务必用PreparedStatement的批量模式：

• JDBC URL里加rewriteBatchedStatements=true（MySQL）。
• 每500条执行一次executeBatch()。
• 手动控制事务，每1000条提交一次。

这种方式不是标题说的“纯SQL”，但如果你被安全策略限制到连递归CTE都不能用，这是个退而求其次的可靠方案。

6. 常见问题与排查技巧实录

六个最常见的坑，我全部踩过，依次写清楚现象、原因和解决思路。

6.1 MySQL递归超过限制

• 现象：报错Recursive query aborted after ... iterations。
• 原因：cte_max_recursion_depth默认值过小。
• 解决：

SET SESSION cte_max_recursion_depth = 10000000;

• 附加：如果设置完还报错，看看你的MySQL是不是8.0.1以前的版本，老版本索引优化欠佳，递归层数上去后开销是雷打不动的。

6.2 Oracle CONNECT BY 循环死循环

• 现象：CONNECT BY运行几十分钟不结束。
• 原因：一些版本的Oracle会先建一棵庞大的树再合并，如果LEVEL没被精确限制在100万，查询就失控了。
• 解决：检查是否误用了CONNECT BY LEVEL <= (SELECT COUNT(*) FROM ...)这种子查询；改成固定的CONNECT BY LEVEL <= 1000000。

6.3 PostgreSQL序列重置后主键冲突

• 现象：第二遍插入时，serial主键连续冲突。
• 原因：你手动指定了id，但序列没有更新到最新值。
• 解决：

SELECT setval('t_user_id_seq', max(id)) FROM t_user;

6.4 MySQL 5.7老版本笛卡尔积SQL太长

• 现象：代码review被人吐槽，SQL几十行一眼难维护。
• 原因：基础数字表方案本质就是这样，难以优雅。
• 解决：如果环境允许建议直接用存储过程备选；另一个办法是造一个seq_1000表，再用一次JOIN seq_10放大，SQL短不少。基础行的来源可以用程序一次性初始化，之后长期复用。

6.5 大量空值和NULL干扰测试结果

• 现象：用了随机函数，但某些字段大量为NULL，统计函数算出来的数据总是偏低。
• 原因：造数时没有用COALESCE或默认值兜底。
• 解决：对业务不允许为空的字段，统一在SELECT中加默认值包裹：

COALESCE(字段, 默认值)

6.6 生成的数据没有业务可读性

• 现象：用户名全是user123，邮箱全是重复模板，看着假。
• 原因：没利用预置数据数组。
• 解决：参考4.1节，用候选取值空间的思路，让数据具有语义覆盖能力。

重要提示：百万级造数不要在业务生产库直接跑，尽量在独立测试实例上操作。否则你的磁盘写放大、临时表空间增长，很可能把生产环境的数据库IO拖垮，这种事产生的负面影响是SQL本身好不好用都无法弥补的。

7. 造数SQL之后的常用校验手段

插完之后别直接就说“完事”，先做三层校验，确认数据量和数据形态都正确。

7.1 检查行数

SELECT COUNT(*) FROM t_user; SELECT COUNT(DISTINCT username) FROM t_user; SELECT MIN(id), MAX(id) FROM t_user;

百万行造数常见的低配错误是：用户数只有几十万，因为序列生成时用了< 1000000而不是<= 1000000，这种边界错误在SQL里太容易发生。拿MIN/MAX一查就现原形。

7.2 检查分布

抽查字段分布是否符合设定：

SELECT status, COUNT(*) FROM t_user GROUP BY status; SELECT EXTRACT(YEAR FROM create_time), COUNT(*) FROM t_user GROUP BY 1;

如果status期望10%失效，实际跑出来只有0.1%，那大概率是你随机表达式里运算符优先级不对。常见错误是gs % 5 = 0和gs % 10 = 0混用，导致比例偏差。

7.3 检查JOIN命中率

子表和主表关联，直接把两边JOIN一把，拿到空数就说明外键断裂了：

SELECT COUNT(*) FROM t_order o LEFT JOIN t_user u ON o.user_id = u.id WHERE u.id IS NULL;

结果必须为0。具体原因和写法见3.3节，这里再强调一次：这种问题如果发生，大概率不是运气问题，而是造数时子表数据范围超出了主表范围。

8. 非存储过程方案的一个变体：临时表+跨库通用写法

最后补充一个不是所有人都知道的技能：如果你需要在三种数据库之间共享同一套“逻辑”，但又受限于各自的CTE和递归差异，最省事的办法是“辅助数字表常驻”。

8.1 先一次性创建全域可用的数字辅助表

在每一个库里都建一个nums表，一次性灌入1到100万：

-- PostgreSQL 用 generate_series 灌 CREATE TABLE nums AS SELECT generate_series(1, 1000000) AS n; -- MySQL 8.0 用递归CTE灌 INSERT INTO nums (n) WITH RECURSIVE seq (n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 1000000 ) SELECT n FROM seq;

8.2 以后造数全走JOIN nums

往任何业务表造数，不管哪个库，都可以套同一个模式：

INSERT INTO t_user (id, username) SELECT n, 'user' || n FROM nums WHERE n <= 1000000;

这个模式的优势是：

• SQL结构在三个库之间完全一致，只是字符串拼接函数略有差异。
• 数据源是物理表，优化器可以用上统计信息，不用猜递归上限和内存开销。
• 如果要二次扩展数据量，只要CROSS JOIN nums一次就能指数级放大。

实际工作中，我在很多主从同步、读写分离的项目里都用这个方法替代存储过程，让DBA和运维团队减少很多负担。这也是我一直愿意推荐“非存储过程”路线的原因——它不挑环境，不给权限管理添乱，适合快速交付和临时演练。

我个人在实际操作中的体会是，造百万数据这件事，真正花时间的不是写SQL，而是理解你要测的业务场景。数据造得越贴近真实，测试结论越可信。所以面对任何一个新库，我都会先花十分钟把表和字段的约束、分布要求看清楚，再决定序列怎么造、随机怎么写。效率最高的一版，往往不是上来就写百万级，而是先跑一万行验证口径，再放大到百万。

最后再分享一个小技巧：把上面所有常用的造数SQL存成一套脚本集，每个表对应一个.sql文件，命名带上前缀如gen_，放到项目里一个固定的testdata目录。上线压测前直接按序执行，干净利落，比临时翻命令历史靠谱得多。后续如果有空，我还会整理一份“百万测试数据下的索引优化与慢查询分析”出来，把造数之后的事情也讲一讲。