在SQL Server数据库开发中，处理非结构化或半结构化数据一直是开发者面临的常见挑战。近日，一则关于“如何遍历类似JSON结构的字符串”的技术提问在开发者社区引发热议。该问题直指许多SQL Server用户在实际项目中遇到的痛点：当字符串虽不是标准JSON但具有键值对、嵌套列表等类JSON特征时，如何高效逐行处理数据？本文结合微软官方文档及社区最佳实践，系统梳理了多种解决方案。

问题背景：非标JSON处理需求激增

随着微服务架构和API接口通信的普及，SQL Server数据库常需要接收来自前端或第三方服务的类JSON字符串。这些字符串可能因为历史遗留、格式不规范（如缺失引号、使用逗号而非分号分隔等）而无法直接被内置JSON函数解析。典型场景包括：日志分析中提取关键字段、配置表存储多层级参数、数据迁移时清洗脏数据等。核心需求是：以循环方式遍历字符串中的每个“条目”，并针对每一条执行特定操作（如插入、更新或聚合计算）。

方案一：使用STRING_SPLIT结合CROSS APPLY（适用简单分隔符）

对于由固定分隔符（如逗号、分号）分隔的简单键值对列表，STRING_SPLIT是最简洁的利器。例如字符串 'key1:val1,key2:val2,key3:val3'，可先按逗号拆分为行，再按冒号分解为键值。代码示例：

DECLARE @str NVARCHAR(MAX) = 'key1:val1,key2:val2,key3:val3';
SELECT 
    LEFT(value, CHARINDEX(':', value)-1) AS [Key],
    SUBSTRING(value, CHARINDEX(':', value)+1, LEN(value)) AS [Value]
FROM STRING_SPLIT(@str, ',');

优势：性能优异，无需游标或循环；局限：无法处理嵌套结构或复杂分隔逻辑。

方案二：利用OPENJSON解析非标准格式（推荐）

SQL Server 2016起引入的OPENJSON函数默认支持标准JSON，但通过设定WITH子句及合理的路径表达式，可处理“类JSON”字符串。例如字符串 '[{a:1,b:2},{a:3,b:4}]'（缺少引号）可通过先手动添加引号或使用REPLACE预处理，再调用OPENJSON。进阶技巧是使用JSON_QUERY配合JSON_VALUE遍历数组元素。微软MVP李伟强调：“尽量将非标格式标准化后再使用JSON函数，可大幅提升代码可维护性，且可利用SQL Server的行集函数（如CROSS APPLY）自动生成循环逻辑，避免显式游标。”

方案三：WHILE循环 + 字符串索引（最灵活但耗性能）

当字符串结构极其不规则或需要动态控制遍历次数时，传统WHILE循环搭配CHARINDEX、SUBSTRING依然有效。例如，逐字符解析并构建临时表：

DECLARE @pos INT = 1, @delim CHAR(1) = ',', @len INT = LEN(@str)
WHILE @pos <= @len
BEGIN
    SELECT @end = CHARINDEX(@delim, @str, @pos)
    IF @end = 0 SET @end = @len+1
    -- 提取子串并处理
    SET @pos = @end + 1
END

注意：此法效率低，仅建议在无法使用上述函数时作为备选，且需谨慎处理索引越界。

方案四：游标（Cursor）——应尽量避免

虽然游标可逐行处理，但性能开销大，且嵌套游标极易导致死锁。资深DBA张强指出：“在SQL Server中，80%的游标使用都可以用基于集合的操作替代。如果必须使用游标，请确保开启FAST_FORWARD和READ_ONLY选项，并限定处理的数据量。”

性能对比与最佳实践

社区实测显示，对于万行级别的字符串，STRING_SPLIT加CROSS APPLY方案耗时不到100ms；OPENJSON预处理方案约150ms；而WHILE循环和游标则可能超过1秒。因此专家建议：

  1. 优先标准化数据：调用API或写入数据库前，要求上游输出合规JSON；若无法控制，则先使用REPLACE、PATINDEX等函数修复。
  2. 利用索引与内存优化：将解析结果存入临时表（#temp）或表变量，并建立必要索引，避免重复解析。
  3. 版本兼容性：SQL Server 2017以下版本无STRING_SPLIT，需用自定义分割函数（如基于XML或TVP）；2016及以上可用OPENJSON。
  4. 避免逐行处理：任何循环都意味着额外开销，务必评估数据量级。小规模（<1000条）可用循环，大规模务必转向集合思维。

未来趋势：更好支持半结构化数据

微软在SQL Server 2022中进一步增强了JSON支持，包括ISJSON严格模式、JSON_OBJECT/JSON_ARRAY构造函数等。业内预测，未来版本可能推出原生“类JSON解析器”或更宽松的格式兼容模式，届时这类遍历问题将迎刃而解。

总结：面对“类似JSON结构的字符串”遍历需求，SQL Server提供了从简单到复杂的多层次工具链。开发者应遵循“最简优先”原则，优先选择集合操作而非显式循环，并用标准化手段降低处理难度。对于复杂场景，结合函数特性与性能监控，方能写出高效、可维护的T-SQL代码。