简介：《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初始为 NULL，isnull(@sql, 'with paging as ( select replace(''{''+ ')这一手很关键：第一次赋值时，把with paging as ( select replace('{'+这段前缀塞进去；后续赋值时@sql已非 NULL，isnull返回原值，于是select @sql = @sql + 新片段就变成了追加。这是 T-SQL 里做字符串累加的经典套路，省掉了一个if @sql is null的分支。
这里有几个必须点破的细节：
OBJECT_ID(@TableName)把表名解析成对象 ID，SYS.SYSCOLUMNS里ID列就是它。表名不存在时OBJECT_ID返回 NULL，WHERE匹配不到任何行，@sql保持 NULL，后续拼接全部失效，最后EXEC(NULL)什么都不发生——静默失败，这是第一个坑。replace(..., '{'+ ..., '')是为了处理拼接出来的尾部多余逗号。因为每列都会追加一个结尾逗号，最后一列也会带一个，必须在整体外面套一层replace把它去掉，再补上}。这个replace的嵌套引号极难读，原文里那串''''''','''')就是它在转义地狱里的样子。SYS.SYSCOLUMNS是兼容视图，新版本更推荐sys.columns+sys.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_agg（SQL 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, N'@p1 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.000，JSON 里没这个类型，只能当字符串，但前端new Date()在某些浏览器上解析这种带空格和毫秒的格式并不可靠。bit转成1/0，你在 JSON 里写的是"1"（带引号的字符串），前端拿到的不是布尔值。NULL被isnull(..., '')变成空字符串，前端分不清“没有值”和“空串”。
这三点如果不管，接口字段类型就成了一锅粥。比较稳的映射策略：
| 列类型 | 建议输出 | 说明 |
|---|---|---|
| int、bigint、decimal | 数字不加引号 | cast(... as varchar)后两侧不加"，保留数值语义 |
| datetime | ISO 8601 字符串 | 用convert(varchar(23), 列, 126)，形如2024-01-05T13:20:00 |
| bit | true/false | case when 列=1 then 'true' else 'false' end |
| NULL | null | isnull(..., 'null')且不加引号，区分空串 |
convert的样式码126就是 ISO 8601，varchar(23)截到毫秒，前端各语言解析器都认。这套映射如果一开始就写进拼接模板，后面能省掉大量“前端说时间格式不对”的来回。
4.3 长度陷阱：varchar(3000) 装不下
原文declare @sql as varchar(3000)。表列一多，每列拼一个"列名":"..."，再加上 CTE 和收尾，列数超过二三十列或者表名较长时，@sql会被静默截断，截断点正好落在语句中间，EXEC直接报语法错误，而且报错位置和你以为的位置对不上，排查起来很折磨。这里应该用nvarchar(max)，别省那点内存。
5. 避坑与排查：这类动态拼 JSON 最常见的五个翻车点
5.1 现象：EXEC 不报错，但返回空结果
原因：@TableName拼错或表不存在，OBJECT_ID返回 NULL，SYSCOLUMNS查不到行，@sql始终是 NULL，EXEC(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”的老代码，都强制先跑一遍这张脏数据表，再谈优化。希望帮到你。
本文还有配套的精品资源，点击获取
