JSON_EXTRACT路径错误静默返回NULL,需确认$开头、数组下标从0起、特殊key加双引号;用->>替代->获取去引号值;高频查询字段应建生成列+索引;嵌套数组用$[0].name,递归路径用$**.id(5.7.9+);务必验证IS NULL。

路径写错就静默返回 NULL,不是报错
这是最常踩的坑:JSON_EXTRACT 不会提示“路径不存在”或“语法错误”,只要路径不合法、字段名拼错、数组下标越界、JSON 格式损坏,一律返回 NULL。你查不到值,但看不出是数据真为空,还是路径写错了。
必须确认三件事:
-
$开头不能省——'$.name'✅,'name'或'$."name"'(除非 key 含空格)❌ - 数组下标从 0 开始——
'$[0].sku'✅,'$[1].sku'(想取第一个)❌ - key 名含点号、空格、连字符时必须加双引号——
'$."user id"'、'$."cpu.freq"'✅
字符串值带引号?用 ->> 替代 -> 或 JSON_EXTRACT
JSON_EXTRACT(json_col, '$.name') 和 json_col->'$.name' 返回的是 JSON 类型值,比如 "tom"(带双引号),在 WHERE 条件里直接 = 'tom' 会匹配失败。
改用 ->>:
-
json_col->>'$.name'等价于JSON_UNQUOTE(JSON_EXTRACT(json_col, '$.name')) - 返回 TEXT 类型,值为
tom(无引号),可直接用于WHERE status->>'$.state' = 'active' - 对数字、布尔值也去引号,语义不变;对不存在路径仍返回
NULL
WHERE 里用 JSON_EXTRACT 性能极差,别直接查大表
JSON_EXTRACT 是逐行计算函数,无法利用索引。执行计划里必现 type: ALL(全表扫描)。哪怕只查一个字段,10 万行就要扫全表。
高频查询字段(如 status、category、user_id)必须提前落库:
KeystoneJS 是一个功能强大的开源无头 CMS,适用于 Node.js,它根据您的模式定义自动生成 GraphQL API 和基于 React 的管理界面。由 Thinkmill 构建,支持 PostgreSQL 和 SQLite、自定义访问控制、钩子、虚拟字段和基于文档的富文本编辑。
- 建生成列:
ALTER TABLE orders ADD COLUMN order_status TEXT GENERATED ALWAYS AS (data->>'$.status') STORED; - 再加索引:
CREATE INDEX idx_order_status ON orders(order_status); - 之后查
WHERE order_status = 'paid'就能走索引
如果字段是 VARCHAR 而非 JSON 类型,更要先加 WHERE JSON_VALID(data) 过滤非法字符串,否则脏数据会让结果不可控。
嵌套数组和递归路径容易写错
提取数组第一个对象的字段,写法是 '$[0].name',不是 '$.0.name' 或 '$.[0].name';提取所有数组元素的某个字段,用 '$[*].sku'(返回 JSON 数组)。
递归搜索(如找任意层级的 id)可用 '$**.id',但注意:
- MySQL 5.7.9+ 才支持
$**语法 - 递归路径性能更差,尽量避免在大表 WHERE 中使用
- 若不确定结构深度,先用
JSON_CONTAINS_PATH(data, 'one', '$.items[*].id')判断路径是否存在
真正麻烦的从来不是语法记不住,而是 NULL 出来得悄无声息,又恰好被当成有效空值参与了业务逻辑。每次写完路径,先用 SELECT data->>'$.xxx' IS NULL 单独验证一列,比上线后排查三天强得多。










