SQL JSON 数据更新与修改技巧

mysql用json_set()安全更新json键，postgresql用jsonb_set()且第三参数须为jsonb类型；切勿拼接字符串，需防注入与解析失败。

UPDATE 里怎么安全地更新 JSON 字段的某个键

MySQL 5.7+ 和 PostgreSQL 都支持直接操作 JSON 字段，但语法和行为差异大，容易写错导致整字段被覆盖或静默失败。

• MySQL 用 JSON_SET() 或 JSON_REPLACE()：前者新增或替换，后者只替换已有键；误用 JSON_INSERT() 可能不生效（键已存在时跳过）
• PostgreSQL 用 jsonb_set()，注意第三个参数必须是 jsonb 类型，传字符串要包一层 ::jsonb，否则报错 function jsonb_set(jsonb, text[], jsonb) does not exist
• 别直接拼接 JSON 字符串再 UPDATE —— 缺少转义会注入或解析失败，比如值含双引号、反斜杠时

示例（MySQL）：

UPDATE users SET profile = JSON_SET(profile, '$.city', 'Shenzhen') WHERE id = 123;

WHERE 条件里查 JSON 字段的值总查不到

不是数据没存对，大概率是路径写错或类型不匹配。JSON 查询对大小写、空格、嵌套层级极其敏感。

• MySQL 中用 JSON_CONTAINS() 查数组项，用 ->>'$.key' 提取字符串值做比较（带 >> 才自动转类型，-> 返回带引号的 JSON 字符串）
• PostgreSQL 中用 data->>'status' 取文本，不能写成 data->'status' = 'active' —— 左边是 jsonb，右边是 text，类型不匹配永远为 false
• NULL 值陷阱：MySQL 的 JSON_EXTRACT(profile, '$.tags') 返回 NULL 时，= NULL 永远不成立，得用 IS NULL

批量更新 JSON 数组里的某类对象（比如日志列表中 status=processing 的项）

原生 SQL 不支持“遍历 JSON 数组并条件更新”，硬写容易变成全量重写字段，丢失并发修改。

• MySQL 没有内置数组元素级更新函数，得先用 JSON_EXTRACT() 拿出整个数组，应用层处理后再用 JSON_SET() 写回 —— 注意并发下可能覆盖他人改动
• PostgreSQL 可用 jsonb_array_elements() 配合 WITH ORDINALITY 定位索引，再用 jsonb_set() 更新指定下标，但语句复杂且性能随数组长度下降明显
• 更稳的做法：把这类结构拆成独立关联表，JSON 只存轻量元数据。数组更新频繁时，SQL 真的不是合适载体

PostgreSQL jsonb 和 MySQL JSON 的性能与迁移坑

看着都是“存 JSON”，实际存储结构、索引机制、查询开销完全不同，换数据库时最容易在这里卡住。

• PostgreSQL 的 jsonb 会解析后二进制存储，支持 gin 索引加速路径查询；MySQL 的 JSON 类型不建索引就只能全表扫描，且无法对子字段加传统 B-tree 索引
• MySQL 对超长 JSON（> 64KB）会截断或报错 Packet for query is too large，而 PostgreSQL 默认限制更松，但写入巨量 jsonb 可能触发 WAL 膨胀
• 迁移时注意：MySQL 的 JSON_VALID() 返回 0/1，PostgreSQL 用 jsonb_valid() 返回 Boolean，别在 WHERE 里混用逻辑判断

真正麻烦的不是语法转换，而是当业务开始依赖 JSON 字段做高频查询或更新时，才发现它早该是关系型结构了。