简介《SQL自动生成JSON数据.docx》面向需要把关系数据库查询结果转换为JSON的开发人员和后端学习者围绕分页查询、动态拼接语句、结果集转JSON、写入数据表及前端接口调用等场景提供一份可对照实现的专题文档。资源包共含1个docx文件压缩后约30KB体量轻便适合本地留存和按章节检索。文档以SQL Server为例讲解变量声明、系统视图读取表结构、公用表表达式分页、行号排序、空值处理与动态执行等关键环节并给出JSON字符串落库和前端请求的示例能帮助读者理清从表结构读取、分页组装到JSON生成、入库调用的完整链路同时说明表名、分页首尾行、排序列等参数如何赋值便于在实际项目中调整复用。目前已有1146人学习下载适合希望补充SQL转JSON实战思路、对照细节并复用脚本模板的初中级开发者参考。1. SQL 拼 JSON为什么“能跑”的存储过程往往最脆弱一条EXEC(sql)执行下去一页数据就变成了一串{字段:值}前端直接JSON.parse就能用——很多人第一次看到这种写法都觉得挺巧妙不用后端序列化不用建视图换个表名照样能用。可真放进业务里问题很快就冒出来了字段里带个英文双引号整段 JSON 直接解析失败表里有个datetime字段转出来是数据库的默认格式分页页数一变total又对不上。这些反常现象背后藏着一个很典型的工程思路用元数据把“行”拼成“文档”再用分页把“文档”拼成“数组”。搞懂这条拼接链就知道它为什么能跑、又为什么容易崩。2. 元数据驱动字段名是怎么变成 JSON 键的整个机制最反直觉的一点是这条 SQL 里根本没有写任何字段名。Merchandise有多少列、叫什么、什么类型全靠运行时从系统视图里读。这是它“通用”的来源也是它全部脆弱性的来源。2.1 从 SYSCOLUMNS 读出表结构拼出 select 片段核心是这一段循环拼接原文的写法精简后select sql isnull(sql, with paging as ( select replace({ ) name : isnull(cast( name as varchar), ) , from SYS.SYSCOLUMNS WHERE ID OBJECT_ID(TableName)逐段拆开看它在做的事情是对目标表的每一列生成一小段字符串表达式{ 列名: isnull(cast(列名 as varchar),) ,sql初始为 NULLisnull(sql, with paging as ( select replace({ )这一手很关键第一次赋值时把with paging as ( select replace({这段前缀塞进去后续赋值时sql已非 NULLisnull返回原值于是select sql sql 新片段就变成了追加。这是 T-SQL 里做字符串累加的经典套路省掉了一个if sql is null的分支。这里有几个必须点破的细节OBJECT_ID(TableName)把表名解析成对象 IDSYS.SYSCOLUMNS里ID列就是它。表名不存在时OBJECT_ID返回 NULLWHERE匹配不到任何行sql保持 NULL后续拼接全部失效最后EXEC(NULL)什么都不发生——静默失败这是第一个坑。replace(..., { ..., )是为了处理拼接出来的尾部多余逗号。因为每列都会追加一个结尾逗号最后一列也会带一个必须在整体外面套一层replace把它去掉再补上}。这个replace的嵌套引号极难读原文里那串,)就是它在转义地狱里的样子。SYS.SYSCOLUMNS是兼容视图新版本更推荐sys.columnssys.types因为后者能拿到max_length、is_nullable、精度标度而SYSCOLUMNS只给列名和类型 ID做类型级处理时信息不够。2.2 拼接顺序与列的物理顺序select sql sql ...这种累加写法在 SQL Server 里不保证按你想象的顺序拼接。官方文档明确说过带变量赋值的SELECT语句赋值顺序是不确定的。绝大多数时候它按聚集索引或内部顺序返回看起来“正常”但一旦表上有并行计划、或者统计信息变化导致扫描顺序改变拼出来的 JSON 字段顺序就可能变。字段顺序对 JSON 语义没影响但对字符串比对、缓存 key、日志 diff有影响——线上排查时发现两次请求返回的 JSON 文本不一致追半天才发现是拼接顺序的问题这种血泪经验不少人都踩过。稳妥写法是用ORDER BY column_id显式定序select sql isnull(sql, with paging as ( select replace({ ) name : isnull(cast( name as varchar), ) , from sys.columns where object_id OBJECT_ID(TableName) order by column_id; -- 显式定序避免拼接顺序漂移column_id是列在表定义中的序号从 1 开始按建表顺序递增。加上它同一张表任何时候拼出来的前缀都完全一致可复现性就有了。3. 分页与 JSON 组装ROW_NUMBER 如何把行变成数组前缀拼完接下来是“行转数组”的部分。原文用 CTE 套ROW_NUMBER()做分页再在 CTE 外面把每行的 JSON 片段和rn、total拼在一起。3.1 WITH paging 与 ROW_NUMBER 的分工拆开看这段结构with paging as ( select 拼好的字段片段 as sourceTable, ROW_NUMBER() OVER (ORDER BY 排序列) as rn, (select count(排序列) from 表) as total from 表 ) select sourceTable rn: cast(rn as varchar) ,total: cast(total as varchar) }, from paging where rn between 起始行 and 结束行WITH paging AS (...)是公用表表达式作用是把“加行号”和“算总数”这两件事先做成语义清晰的中间结果外层只负责过滤和拼串。ROW_NUMBER() OVER (ORDER BY 排序列)给每行打上 1、2、3… 的序号WHERE rn BETWEEN 首行 AND 末行就把这段序号切成当前页。这是 SQL Server 2012 之前没有OFFSET/FETCH时的标准分页写法现在仍然常见于老系统。(select count(...) from 表)是个标量子查询每行都会算一次但优化器一般会把它提升成只算一次。这里有个隐患排序列为 NULL 时count(排序列)不计 NULL 行而ROW_NUMBER会把 NULL 行算进去于是total比实际行数少前端分页器永远翻不到最后一页。正确做法是count(*)或者换一个非空列做 count。3.2 每行 JSON 的收尾逗号与花括号的配对注意上面sourceTable结尾的},——每行拼出来是{...,rn:1,total:37},以逗号结尾。整页拼完是{..},{..},{..},最后多一个逗号整体再包一层[]才是合法数组。原文把这段收尾和replace散落在EXEC前后的拼接里逻辑上是对的但代码可读性极差改一处引号就全线崩。更清晰的写法是把“行内组装”和“数组收尾”分成两步-- 第一步生成行拼装表达式列部分 declare cols nvarchar(max); select cols isnull(cols ,, ) c.name : isnull(cast( quotename(c.name) as nvarchar(max)), ) from sys.columns c where c.object_id OBJECT_ID(TableName) order by c.column_id; -- 第二步拼最终语句数组用 STUFF 去尾部逗号 declare stmt nvarchar(max) N select [ string_agg(rowjson, ,) ] from ( select { cols N } as rowjson from quotename(TableName) N ) t;; exec sp_executesql stmt;quotename给列名加方括号防止列名是保留字或含空格时语法出错string_aggSQL Server 2017替代手工replace去尾逗号语义直接不用数引号。这就是“机制一样、实现换了更稳的骨架”。3.3 参数化为什么 EXEC(sql) 是注入的温床原文用EXEC(sql)把整条语句当字符串执行。问题在于TableName、OrderByColumn是直接拼进去的。如果这两个值来自前端比如“用户选表、选排序字段”就是标准的 SQL 注入面传个Merchandise; drop table x--进去整条链就完了。原文注释里写“你要查询的表”假设是程序内部硬编码这在老代码里很常见但一旦有人图省事开放成参数翻车是迟早的。正确姿势是sp_executesql带参数并做白名单校验-- 白名单校验表名与排序列杜绝任意拼接 if not exists (select 1 from sys.tables where name TableName) throw 50001, 非法表名, 1; if not exists (select 1 from sys.columns where object_id OBJECT_ID(TableName) and name OrderByColumn) throw 50002, 非法排序列, 1; declare stmt nvarchar(max) N... order by quotename(OrderByColumn) N ...; exec sp_executesql stmt, Np1 int, p2 int, p1 CurPageFirstRow, p2 CurPageLastRow;分页的起止行用参数传入表名和排序列先校验再quotename。这里的原则是能用参数的地方用参数不能参数化的标识符表名、列名必须走白名单。这条原则不是本文的原创但很多人写动态 SQL 时会忘掉直到被扫出漏洞。4. 转义与类型JSON 合法性藏在 cast 里这条链能跑通靠的是把每列cast as varchar再拼字符串的假设值里没有特殊字符。这个假设在真实业务数据面前基本撑不过一周。4.1 字符串里的双引号与反斜杠JSON 规定字符串里的必须写成\\必须写成\\换行符要写成\n。原文用cast(列名 as varchar)直接把原始值塞进引号字段值只要是他说你好拼出来就是字段:他说你好整个 JSON 立刻非法前端解析报错。这是最常见的一类翻车测试数据干净上线遇到用户填了个引号就爆。正确处理思路是拼接时对值做转义-- 转义双引号与反斜杠保证 JSON 字符串合法 replace(replace(replace(cast(列名 as nvarchar(max)), \, \\), , \), char(10), \n)三个replace分别处理反斜杠、双引号、换行。顺序有讲究必须先转反斜杠再转引号否则\里的\会被二次转义成\\。这个顺序坑我见过不止一个团队栽进去。4.2 类型映射datetime、bit、NULL 各自怎么表示cast(... as varchar)会按服务器默认格式转datetime出来是2024-01-05 13:20:00.000JSON 里没这个类型只能当字符串但前端new Date()在某些浏览器上解析这种带空格和毫秒的格式并不可靠。bit转成1/0你在 JSON 里写的是1带引号的字符串前端拿到的不是布尔值。NULL被isnull(..., )变成空字符串前端分不清“没有值”和“空串”。这三点如果不管接口字段类型就成了一锅粥。比较稳的映射策略列类型建议输出说明int、bigint、decimal数字不加引号cast(... as varchar)后两侧不加保留数值语义datetimeISO 8601 字符串用convert(varchar(23), 列, 126)形如2024-01-05T13:20:00bittrue/falsecase when 列1 then true else false endNULLnullisnull(..., null)且不加引号区分空串convert的样式码126就是 ISO 8601varchar(23)截到毫秒前端各语言解析器都认。这套映射如果一开始就写进拼接模板后面能省掉大量“前端说时间格式不对”的来回。4.3 长度陷阱varchar(3000) 装不下原文declare sql as varchar(3000)。表列一多每列拼一个列名:...再加上 CTE 和收尾列数超过二三十列或者表名较长时sql会被静默截断截断点正好落在语句中间EXEC直接报语法错误而且报错位置和你以为的位置对不上排查起来很折磨。这里应该用nvarchar(max)别省那点内存。5. 避坑与排查这类动态拼 JSON 最常见的五个翻车点5.1 现象EXEC 不报错但返回空结果原因TableName拼错或表不存在OBJECT_ID返回 NULLSYSCOLUMNS查不到行sql始终是 NULLEXEC(NULL)静默通过。解决执行前加一行断言把“静默”变“显式”if OBJECT_ID(TableName) is null throw 50003, 表不存在或拼写错误 TableName, 1;5.2 现象JSON 解析失败报 “Unexpected token”原因字段值里含未转义的双引号、反斜杠、换行。解决按 4.1 的replace链处理或者干脆放弃字符串拼接改用FOR JSON PATH让引擎负责转义SQL Server 2016这是最省心的替代方案。5.3 现象total 比实际行数少翻页翻不到底原因count(排序列)忽略了 NULL 行而ROW_NUMBER计入了。解决改成count(*)同时确认排序列有索引否则大表上每页都全表扫一次翻几页就卡死。5.4 现象改了表结构后接口字段突然多一个或漏一个原因拼接依赖SYSCOLUMNS返回的列集合加列、删列、改名都会立即影响输出前端如果按字段名严格解析就崩。解决把“要输出的列”做成白名单配置而不是全表列或者接受全列但在接口层做字段映射。这本质是“元数据驱动”带来的耦合得在设计上认账。5.5 现象并发下偶发返回重复或错乱的行原因分页依赖ORDER BY 排序列的唯一性。排序列有重复值时ROW_NUMBER的排序不稳定同一行可能出现在两页里也可能被跳过。解决排序列追加主键做决胜例如ORDER BY 排序列, 主键列保证全序。这一步不加分页在数据量上来后几乎必然出问题。6. 设计取舍什么时候该拼、什么时候该换这条 SQL 链路的价值在于通用一张表名进去一页 JSON 出来不用为每张表写序列化代码。在后台管理、内部工具、快速原型这种“表结构相对稳定、调用方是自己人”的场景里它确实省事。但它的每一项便利都对应一个代价元数据驱动带来字段耦合字符串拼接带来转义和注入风险手工分页带来排序稳定性问题。我的判断标准是看调用边界。如果 JSON 只在自己系统内部流转字段变了同步改前端那这套写法可以留着但至少要补上 4.1 的转义、5.1 的断言、5.5 的决胜列这三样成本很低收益是“不再半夜被解析错误叫醒”。如果 JSON 要出网、要给第三方、要长期演进那就不该在数据库层拼——FOR JSON PATH让引擎管转义和类型后端再包一层 DTO 管字段契约两层各司其职比在 T-SQL 里和引号搏斗靠谱得多。还有一个常被忽略的取舍分页在数据库做还是在应用做。数据库分页省网络、省内存但每次都要 count 一遍应用分页一次查一大段再切逻辑简单但大表上会拖垮内存。原文选了数据库分页方向没错问题只出在 count 的写法和排序稳定性上把这两点补上这个选择是站得住的。验证这套东西是否真的稳我会用一个很土的办法造一张包含引号、换行、NULL、datetime、超长文本的测试表跑三种页第一页、中间页、最后一页把返回的 JSON 丢给标准解析器再看total和实际条数是否一致。这一步能一次性暴露掉九成的转义和分页问题。从那以后我每次接手这种“拼字符串出 JSON”的老代码都强制先跑一遍这张脏数据表再谈优化。希望帮到你。本文还有配套的精品资源点击获取
