SQL中查询返回XML或JSON格式字段内容的方法

在SQL Server里，用FOR XML生成XML结果时，字段值里的特殊字符会被自动转义——这是默认的安全机制。如果你需要拿回原始字符串，正确的做法是用FOR XML PATH(''), TYPE搭配.value()来提取。

SQL Server里用 FOR XML 生成XML结果时，字段值被转义怎么办？

默认情况下，FOR XML 会把字段里的特殊字符（比如 &、<、>）自动转义成 &、<、>，结果就是原始内容变了样。这不是bug，而是安全设计。但如果你明确知道字段内容可信、需要原样输出，那就要加 TYPE，或者改用 FOR XML RAW('row'), ELEMENTS XSINIL 配合 .value() 提取。

• 用 SELECT ... FOR XML RAW, TYPE 可以返回真正的XML数据类型，避免字符串级别的转义。
• 如果字段是文本且包含HTML或标签，先用 REPLACE 清洗再拼接，比依赖自动转义更可控。
• FOR XML EXPLICIT 灵活性高，但写法复杂。小批量数据建议绕开，容易漏掉 NULL 处理逻辑。

PostgreSQL中 row_to_json() 和 to_jsonb() 的行为差异

row_to_json() 接收一行记录（比如 SELECT row_to_json(t) FROM (SELECT 1 AS id, 'a' AS name) t），而 to_jsonb() 可以直接处理任意表达式或标量值，并且对 NULL、数字精度、时间戳时区更严格。JSONB 还支持索引和键路径查询，但序列化后会丢失字段顺序。

• 想保留字段定义顺序？用 row_to_json()。
• 要做 @> 包含查询或建 GIN 索引？必须用 to_jsonb()。
• 数值字段如果来自 float8，to_jsonb() 可能显示为科学计数法（比如 1e-5），而 row_to_json() 更倾向保留小数点格式。

MySQL 5.7+ 用 JSON_OBJECT() 拼接字段时，NULL值怎么控制？

JSON_OBJECT() 默认会跳过 NULL 键值对，不像传统 SQL 函数那样报错或留空。这在构造API响应时很实用，但也容易漏掉预期字段——比如你写了 JSON_OBJECT('status', status_col)，而 status_col 是 NULL，整个 "status" 键就消失了。

• 强制保留键：用 IFNULL(status_col, 'unknown') 或 COALESCE(status_col, '')。
• 嵌套对象要分层构建：JSON_OBJECT('user', JSON_OBJECT('id', u.id, 'name', u.name))，不能直接传子查询。
• 字段名含空格或特殊字符？必须用字符串字面量：JSON_OBJECT('full name', u.name)，不能写 JSON_OBJECT(full name, u.name)。

Oracle中 XMLAGG() 和 JSON_OBJECTAGG() 对重复键的处理

Oracle 12c+ 的 JSON_OBJECTAGG() 在遇到重复键时默认报错 ORA-40478: maximum number of JSON object members exceeded（其实是键冲突，不是数量超限），而 XMLAGG() 会无脑拼接，不校验唯一性。两者都不会自动去重。

• 去重必须前置：SELECT JSON_OBJECTAGG(k:v) FROM (SELECT DISTINCT key_col k, val_col v FROM t)。
• 想合并同键多值？用 JSON_ARRAYAGG() 套一层：JSON_OBJECTAGG(key_col VALUE JSON_ARRAYAGG(val_col))。
• XMLAGG(XMLELEMENT(...)) 如果源数据有换行符，生成的XML里会带 实体，解析端需注意是否自动还原。

实际用的时候，别只看函数名像不像“转JSON”，先确认你数据库版本是否真支持——比如 MySQL 5.6 就没有 JSON_OBJECT()，强行用会报 FUNCTION does not exist；Oracle 11g 也压根没 JSON_OBJECTAGG()。字段类型、NULL策略、编码边界——这些细节一错，返回的就是不可用的碎片数据。