SQL JSON 数据类型操作实战

mysql 5.7+ 存 json 应用生成列（stored）提取关键字段并建索引，如 config_status tinyint as (config->>’$.status’) stored，配合 idx_config_status 索引；禁用 virtual 列和 ->（需用 ->> 避免类型不匹配）。

MySQL 5.7+ 怎么存 JSON 并保证查询不慢

直接存 JSON 类型字段没问题，但默认不建索引、不走 B+ 树，WHERE JSON_CONTAINS() 或 -> 取值后过滤，基本等于全表扫描。

真正能加速的只有「生成列 + 普通索引」：用 GENERATED COLUMN 提取关键字段，再对它加索引。比如存用户配置：

ALTER TABLE users ADD config_status TINYINT AS (config->>'$.status') STORED; CREATE INDEX idx_config_status ON users(config_status);

• STORED 是必须的——VIRTUAL 列不能建索引
• 用 ->>（带自动类型转换）而不是 ->，避免索引失效（比如字符串 “1” 和数字 1 不等价）
• 别对整个 config 字段建索引——MySQL 不支持 JSON 全文索引，也没法建前缀索引

PostgreSQL 的 jsonb 怎么查数组里有没有某个值

用 @> 操作符最稳，不是 ? 或 CONTAINS。比如查 tags 数组是否含 'backend'：

SELECT * FROM posts WHERE tags @> '["backend"]';

• tags 必须是 jsonb 类型，json 类型不支持 @>
• 右边必须是合法 jsonb 字面量，不能是字符串变量——要写成 $1::jsonb 绑定参数
• 如果只是查单个字符串是否存在，tags ? 'backend' 更快，但它只判断 key 是否存在，不是数组元素匹配

SQL Server 2016+ 解析 JSON 失败常见报错

最常卡在 JSON_VALUE() 返回 NULL 却以为是数据问题——其实是路径写错了或类型不匹配。

• 路径必须用双引号，比如 '$.user.name'，写成 '$user.name' 或 "$.user.name"（外面双引号）都报错
• JSON_VALUE() 只返回标量，JSON_QUERY() 才返回对象 / 数组；混用会静默返回 NULL
• 输入字段为 NULL 或非 JSON 字符串时，两个函数都返回 NULL，不是报错——得先用 ISJSON() 过滤
• 中文字符如果源数据是 varchar 且没设 UTF-8 排序规则，可能解析成乱码，优先用 nvarchar

跨数据库写 JSON 查询要注意的兼容点

没有银弹语法。同一个需求，在 MySQL、PG、SQL Server 里写法完全不同，硬套会翻车。

• 提取字段：MySQL 用 col->'$.name'，PG 用 col->>'name'，SQL Server 用 JSON_VALUE(col, '$.name')
• 判断存在：MySQL 用 JSON_CONTAINS(col, '"val"', '$.tags')，PG 用 col @> '{"tags": ["val"]}'，SQL Server 得先 OPENJSON() 再 JOIN
• 性能陷阱：所有数据库对深层嵌套 JSON（如 $.a.b.c.d.e）的查询都慢，别依赖它做高频条件；提前扁平化到关系字段更可靠

JSON 类型省了序列化反序列化，但换来的是查询逻辑分散、索引难做、出错难定位——真要高频检索，宁可多几列，少嵌套一层。