简介这份资源面向数据库开发人员与后端工程师聚焦用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_ARRAYAGGPostgreSQL 的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( hosthost, useruser, passwordpassword, databasedatabase, charsetutf8mb4 ) 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_lenNone, precisionNone, scaleNone): 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, klength)) if data_type in (date,): return (datetime.now() - timedelta(daysrandom.randint(0, 365))).strftime(%Y-%m-%d) if data_type in (datetime, timestamp): return (datetime.now() - timedelta(daysrandom.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, encodingutf-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_asciiFalse)) if i count - 1: f.write(,\n) else: f.write(\n) f.write(]\n)这里用ensure_asciiFalse保证中文不被转义成\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, requiredTrue, help表名) parser.add_argument(--count, typeint, default100, help生成条数) parser.add_argument(--output, defaultoutput.json, help输出文件路径) parser.add_argument(--format, choices[array, lines], defaultarray, 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的字段上按概率生成nullNOT 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, k9)) 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(instancegenerated_json, schemaschema)校验失败时jsonschema会抛出异常并指出哪个字段不符合定位很快。我一般把 Schema 和生成脚本放在同一个目录改表结构时同步改 Schema两边保持一致。5.4 一个我常犯的错误早期做这个脚本时我总想把所有配置都塞进一个文件结果类型映射、嵌套关系、输出格式全混在一起改一个字段要翻几百行。后来拆成三个文件schema_reader.py负责读表结构value_generator.py负责按类型生成值json_builder.py负责组装和输出。每个文件职责单一换数据库只需要改第一个换输出格式只需要改第三个。这个习惯帮我省了很多返工时间。希望帮到你。本文还有配套的精品资源点击获取