简介：这份资源面向数据库开发人员与后端工程师，聚焦用SQL语言自动生成JSON数据这一实用场景，帮助读者在SQL Server环境下将查询结果直接转换为JSON格式，供数据表存储或前端接口调用。包内共1个docx文档，约30KB，内容围绕动态SQL拼接、WITH子句分页、ROW_NUMBER()排序及SYS.SYSCOLUMNS系统视图取表结构等核心技巧展开，并给出INSERT INTO写入JSON字段与AJAX前端调用的示例代码。文档还完整呈现了声明@TableName、@sql、@CurPageFirstRow、@CurPageLastRow、@OrderByColumn等变量并配合EXEC执行动态语句的写法，便于读者理解分页与JSON组装如何结合。目前已有1141人学习下载，适合希望减少手写序列化逻辑、提升数据处理效率的中级开发者参考借鉴。
1. 从一份 SQL 直接生成 JSON 数据：为什么这件事比想象中更值得做
如果你正在做接口联调、前端 Mock、自动化测试或者数据迁移，大概率遇到过这种场景：后端表结构已经定好了，但接口返回的 JSON 样例还得手写。字段一多，嵌套一深，手写就容易漏字段、类型对不上、日期格式前后端打架。更麻烦的是，每次表结构一变，Mock 数据就得跟着重来一遍。SQL 自动生成 JSON 数据这件事，解决的就是这个断层——让数据库里的表结构和约束，直接变成一份结构正确、类型合理、可以直接喂给前端或测试框架的 JSON。
它适合三类人：一是后端还没写完接口、前端需要先跑起来的人；二是做自动化测试，需要批量造符合表约束的测试数据的人；三是做数据迁移或 ETL，需要把关系型数据转成文档型结构的人。核心思路不复杂：读表结构拿到字段名和类型，按类型生成对应值，再按你指定的嵌套关系拼成 JSON。但真正落地时，类型映射、空值处理、嵌套层级、批量生成的性能，每一项都有坑。下面按我实际做过的路径，从选型到跑通再到排错，一步步拆开讲。
2. 先想清楚生成路径：从表结构到 JSON 的三种常见做法
2.1 三种路径的适用边界
第一种是纯 SQL 拼接。用数据库自带的 JSON 函数，比如 MySQL 的JSON_OBJECT、JSON_ARRAYAGG，PostgreSQL 的json_build_object、json_agg，直接在查询里把结果拼成 JSON。优点是零外部依赖，数据库里跑完就能拿到结果；缺点是嵌套层级一深，SQL 会变得极长且难维护，而且生成的是真实数据，不是随机 Mock 数据。
第二种是脚本读取表结构后生成。用 Python 或 Node.js 连上数据库，查information_schema.columns拿到字段名、类型、是否可空，然后在脚本里按规则生成随机值并组装 JSON。优点是灵活，嵌套关系、字段过滤、批量条数都能控制；缺点是要写代码，且不同数据库的元数据查询语句有差异。
第三种是借助现成的数据生成工具，配置字段映射规则后导出 JSON。优点是上手快；缺点是嵌套结构和自定义类型映射往往受限，遇到复杂业务字段还是得改配置或写插件。
我一般会选第二种，因为可控性最高，而且一次写好脚本，后面换表只需要改配置。下面重点讲这条路径。
2.2 用 Python 读取表结构的核心查询
不管后面怎么生成，第一步都是拿到表的字段清单。以 MySQL 为例，查information_schema.columns是最稳的方式：
import pymysql def get_table_columns(host, user, password, database, table_name): conn = pymysql.connect( host=host, user=user, password=password, database=database, charset='utf8mb4' ) cursor = conn.cursor(pymysql.cursors.DictCursor) # 查询字段名、数据类型、是否可空、字符最大长度、数值精度 sql = """ SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE FROM information_schema.columns WHERE table_schema = %s AND table_name = %s ORDER BY ORDINAL_POSITION """ cursor.execute(sql, (database, table_name)) columns = cursor.fetchall() cursor.close() conn.close() return columns这段代码的关键在ORDER BY ORDINAL_POSITION，保证字段顺序和建表时一致，生成 JSON 时字段顺序不会乱。DATA_TYPE拿到的是数据库层面的类型名，比如varchar、int、datetime、decimal，后面做类型映射就靠它。IS_NULLABLE决定这个字段要不要按概率生成null。CHARACTER_MAXIMUM_LENGTH用来控制生成字符串的长度上限，避免生成超长字符串导致插入失败。
注意：PostgreSQL 的元数据在
information_schema.columns里同样存在，但类型名和 MySQL 有差异，比如character varying、timestamp without time zone，映射表要单独维护。
2.3 类型映射表怎么定
拿到字段类型后，需要一张映射表决定每种类型生成什么值。这张表是整个脚本的核心，我一般会写成字典：
import random import string from datetime import datetime, timedelta def gen_value(data_type, max_len=None, precision=None, scale=None): if data_type in ('int', 'bigint', 'smallint', 'tinyint'): return random.randint(1, 100000) if data_type in ('decimal', 'numeric', 'float', 'double'): p = precision or 10 s = scale or 2 return round(random.uniform(0, 10 ** (p - s)), s) if data_type in ('varchar', 'char', 'text'): length = min(max_len or 20, 50) return ''.join(random.choices(string.ascii_letters + string.digits, k=length)) if data_type in ('date',): return (datetime.now() - timedelta(days=random.randint(0, 365))).strftime('%Y-%m-%d') if data_type in ('datetime', 'timestamp'): return (datetime.now() - timedelta(days=random.randint(0, 365))).strftime('%Y-%m-%d %H:%M:%S') if data_type in ('boolean', 'tinyint(1)'): return random.choice([True, False]) return None这里有几个参数需要说明。max_len来自CHARACTER_MAXIMUM_LENGTH，但实际生成时我会再取一个上限，比如 50，避免生成几百个字符的字符串把 JSON 撑得太大。precision和scale来自NUMERIC_PRECISION和NUMERIC_SCALE，控制小数位数，不然decimal(10,2)生成一堆浮点尾数，前端拿到会很难看。日期类型统一格式化成字符串，因为 JSON 本身没有日期类型，直接塞datetime对象序列化会报错。
2.4 组装 JSON 与嵌套结构
单表生成是线性的，但实际业务里 JSON 往往有嵌套，比如一个订单下面挂多个商品。这时候有两种做法：一种是在 SQL 里用JSON_ARRAYAGG直接拼，另一种是在脚本里按主表和外键关系分别生成再组装。我倾向于后者，因为嵌套层级和字段过滤更直观。
def build_order_json(order_row, items): return { "orderId": order_row["id"], "orderNo": order_row["order_no"], "amount": order_row["amount"], "createdAt": order_row["created_at"], "items": [ { "itemId": it["id"], "productName": it["product_name"], "quantity": it["quantity"], "price": it["price"] } for it in items ] }这段代码里，order_row和items都是前面按表结构生成的字典。嵌套的关键是外键关联，生成items时把order_id固定成当前订单的id，这样组装出来的 JSON 在逻辑上是一致的。如果只是造 Mock 数据，外键一致性可以放宽，但如果是做数据迁移验证，外键必须对得上，否则下游校验会失败。
3. 把脚本跑起来：批量生成与输出格式控制
3.1 批量生成时的内存与速度权衡
单条生成很简单，但批量生成一千条、一万条时，如果一次性全塞进列表再json.dump，内存会涨得很快。我一般用生成器逐条产出，再流式写入文件：
import json def generate_batch(table_name, count, output_path): columns = get_table_columns(DB_HOST, DB_USER, DB_PASS, DB_NAME, table_name) with open(output_path, 'w', encoding='utf-8') as f: f.write('[\n') for i in range(count): row = {} for col in columns: val = gen_value( col['DATA_TYPE'], col['CHARACTER_MAXIMUM_LENGTH'], col['NUMERIC_PRECISION'], col['NUMERIC_SCALE'] ) if col['IS_NULLABLE'] == 'YES' and random.random() < 0.1: val = None row[col['COLUMN_NAME']] = val f.write(json.dumps(row, ensure_ascii=False)) if i < count - 1: f.write(',\n') else: f.write('\n') f.write(']\n')这里用ensure_ascii=False保证中文不被转义成\uXXXX，可读性好很多。空值概率我设成 10%，这个值可以调，如果表里NOT NULL字段多，生成null会导致后续插入失败，所以只在IS_NULLABLE为YES时才可能生成null。写入时手动控制逗号和换行，避免用json.dump一次性序列化整个列表。
3.2 输出格式的三种选择
生成 JSON 时，输出格式直接影响下游怎么用。常见有三种：数组格式、每行一个 JSON 对象（JSON Lines）、按主键分文件。数组格式适合直接喂给前端当 Mock 接口返回；JSON Lines 适合流式处理和大数据管道，每行独立解析，不会因为某一行格式错误导致整个文件报废；按主键分文件适合需要按 ID 查找的场景，但文件数量会很多。
我一般默认输出数组格式，如果条数超过一万，会改成 JSON Lines，因为数组格式在文件末尾才能闭合，中途解析不了。切换只需要改写入逻辑，把[和]去掉，每条后面加换行即可。
3.3 用命令行参数控制生成行为
脚本写好后，每次改代码去调参数不现实。我习惯用argparse把关键参数暴露出来：
import argparse parser = argparse.ArgumentParser() parser.add_argument('--table', required=True, help='表名') parser.add_argument('--count', type=int, default=100, help='生成条数') parser.add_argument('--output', default='output.json', help='输出文件路径') parser.add_argument('--format', choices=['array', 'lines'], default='array', help='输出格式') args = parser.parse_args()这样跑的时候直接python gen.py --table orders --count 500 --format lines，不用改代码。--count默认 100，避免不小心生成太多把磁盘写满。--format控制输出格式，和前面说的数组/JSON Lines 对应。
提示：如果表里有敏感字段，比如手机号、邮箱，生成时最好用固定前缀加随机数字，不要生成看起来像真实数据的值，避免被误用。
4. 避坑与排查：生成 JSON 时最容易翻车的五个点
4.1 字段类型映射错，前端拿到就报错
现象：生成的 JSON 里，本该是数字的字段变成了字符串，或者日期格式前端解析不了。原因：数据库类型名和脚本里的映射表对不上，比如 MySQL 的tinyint(1)被当成整数生成，但业务上它是布尔值。解决：在映射表里单独处理tinyint(1)，并且把映射关系写成配置，不要硬编码在函数里。每次换数据库或换表，先跑一条样例，人工检查字段类型。
4.2 嵌套层级太深，SQL 拼接直接失控
现象：用JSON_OBJECT嵌套三层以上，SQL 长度超过几千字符，改一个字段要翻半天。原因：SQL 的 JSON 函数适合浅层拼接，深层嵌套可读性急剧下降。解决：超过两层嵌套就改用脚本组装，SQL 只负责查平铺数据，嵌套关系在代码里用字典拼。这样改字段只需要改字典的 key，不用动 SQL。
4.3 批量生成时外键对不上，下游校验失败
现象：生成的订单和订单明细，order_id在明细里找不到对应订单。原因：主表和子表分别独立生成，没有共享主键。解决：先生成主表数据并保留主键列表，生成子表时从主键列表里随机取，保证外键有效。如果只是 Mock 数据，可以放宽这个约束，但要在文档里写清楚。
4.4 空值概率设太高，插入数据库时被拒
现象：生成的 JSON 导入数据库时，大量NOT NULL字段为null，插入失败。原因：空值生成逻辑没有区分IS_NULLABLE。解决：只在IS_NULLABLE为YES的字段上按概率生成null，NOT NULL字段永远生成有效值。如果业务上确实需要空值，先把表结构改掉，而不是在生成脚本里硬塞。
4.5 输出文件太大，编辑器直接卡死
现象：生成十万条 JSON 后，文件几百 MB，用编辑器打开就卡住。原因：数组格式把所有数据堆在一个文件里。解决：超过一万条改用 JSON Lines，或者按每五千条切分文件。切分时文件名带序号，比如output_001.json、output_002.json，方便后续按批处理。
5. 进阶技巧：让生成的 JSON 更贴近真实业务
5.1 用正则和枚举约束字段值
纯随机字符串看起来太假，前端联调时容易忽略边界情况。我一般会给关键字段加约束，比如手机号用1[3-9]\d{9}生成，状态字段从枚举列表里选，邮箱用固定域名加随机前缀。这样生成的 JSON 更接近真实接口返回，前端能提前发现格式问题。
import re def gen_phone(): return '1' + random.choice('3456789') + ''.join(random.choices(string.digits, k=9)) def gen_status(): return random.choice(['pending', 'paid', 'shipped', 'completed'])5.2 从真实数据采样而不是纯随机
如果数据库里已经有部分真实数据，可以直接采样几条，然后对敏感字段做替换，其余字段保留。这样生成的 JSON 在数据分布上更真实，比如金额范围、日期跨度、字符串长度都符合实际。做法是查SELECT * FROM table LIMIT 10，拿到样本后对指定字段做脱敏或随机替换，再批量复制并微调。
5.3 用 JSON Schema 反向校验生成结果
生成之后怎么确认结构没问题？可以写一份 JSON Schema，用jsonschema库校验每条生成的 JSON。Schema 里定义字段类型、是否必填、嵌套结构，校验不通过就打印具体路径。这样在批量生成后跑一遍校验，比人工抽查靠谱得多。
from jsonschema import validate schema = { "type": "object", "required": ["orderId", "amount", "items"], "properties": { "orderId": {"type": "integer"}, "amount": {"type": "number"}, "items": {"type": "array", "minItems": 1} } } validate(instance=generated_json, schema=schema)校验失败时jsonschema会抛出异常并指出哪个字段不符合，定位很快。我一般把 Schema 和生成脚本放在同一个目录，改表结构时同步改 Schema，两边保持一致。
5.4 一个我常犯的错误
早期做这个脚本时，我总想把所有配置都塞进一个文件，结果类型映射、嵌套关系、输出格式全混在一起，改一个字段要翻几百行。后来拆成三个文件：schema_reader.py负责读表结构，value_generator.py负责按类型生成值，json_builder.py负责组装和输出。每个文件职责单一，换数据库只需要改第一个，换输出格式只需要改第三个。这个习惯帮我省了很多返工时间。希望帮到你。
本文还有配套的精品资源，点击获取
矩形求和的工程实现)
