MySQL JSON_EXTRACT 函数详解：JSON 查询与索引优化实战

1. 为什么 JSON_EXTRACT 是我学习 MySQL JSON 玩法的第一站1.1 我是在什么场景下不得不学会它的MySQL 的 JSON_EXTRACT 是我接手一个电商订单表时真正花力气研究过的函数。原因很现实业务方把收货信息、商品快照、优惠明细全部塞进一个 JSON 字段里而我要在不额外开发应用服务的前提下直接在数据库里把这些数据查出来做统计分析。当时表结构大概长这样CREATE TABLE user_orders ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, order_info JSON, created_at DATETIME DEFAULT CURRENT_TIMESTAMP );order_info里什么都有订单号、收货人、手机号、省市区、商品列表、优惠券信息。字段一直变今天加一个“是否赠品”明天加一个“预估时效”如果按传统关系型设计每次都要改表结构、改写入代码、改查询代码改到怀疑人生。而用 JSON 列业务代码只需要多传一个字段数据库层完全不用动。这种灵活性就是 JSON 类型存在最大的意义而 JSON_EXTRACT 则是把这种灵活性重新“收编”成结构化查询的关键工具。不过必须说明JSON 不是万能灵药。我在项目里见过反例有人把所有业务字段都塞进 JSON导致每个查询都要在全表里拆字段最后慢得没法看。JSON_EXTRACT 是给“有边界的灵活”用的不是让数据库变成 NoSQL 的。1.2 该用 JSON 列还是继续拆表我的取舍标准很多人学 JSON_EXTRACT 之前会问一个问题到底该不该把字段存成 JSON我的判断标准其实很朴素适合 JSON 的场景字段结构经常变内部字段大多整块读写查询时不需要频繁按内部字段关联排序数据属于“低频但必须能查”的类型。不适合 JSON 的场景某个内部字段要频繁参与 WHERE 过滤、GROUP BY、ORDER BY需要和其他表做 JOIN单表数据量到了千万级业务对这个字段的实时一致性要求很高。我用一个生活化类比JSON 字段像一个收纳箱你可以随手往里面塞各种形状的东西找东西时要翻一遍普通字段像抽屉里的固定格子放东西麻烦一点但拿东西的直接性无与伦比。JSON_EXTRACT 就是让你“翻收纳箱”时不至于把整个箱子倒出来它帮你精准定位。所以你首先要判断这个箱子到底该不该存在。1.3 MySQL 的 JSON 类型和 TEXT 存 JSON 文本根本不是一回事很多老项目是把 JSON 字符串直接塞进 TEXT 字段的。它们看起来像 JSON查询时靠应用层json_decode处理。但从 MySQL 5.7.8 开始有了真正的 JSON 类型之后再这么做就亏大了。区别主要体现在三方面合法性校验JSON 类型在写入时会强校验非法 JSON 直接报错不会让脏数据悄悄落地。存储格式JSON 类型内部用二进制格式存储读取速度比 TEXT 明文快而且 MySQL 8.0 在一定条件下支持 JSON 部分更新不会整列重写。数据库层函数JSON 类型可以直接配合 JSON_EXTRACT、JSON_SET、JSON_TABLE 等函数使用而 TEXT 字段必须先 CAST 成 JSON 才能用性能差一大截。我在实际维护中见过最崩溃的事TEXT 字段里存了半截 JSON因为写入代码有个 bug字符串被截断了。如果有 JSON 类型校验这类问题根本不会出现。这也是我后来坚持所有新增字段尽量用真正的 JSON 类型而不是 TEXT 的原因。2. JSON_EXTRACT 的语法与路径表达式从 $ 到通配符一次讲清2.1 最基础的提取操作一条 SQL 就够了先看最简洁的用法。JSON_EXTRACT 的语法是JSON_EXTRACT(json_doc, path[, path] ...)json_doc是 JSON 列或 JSON 字符串path是路径表达式。比如SELECT JSON_EXTRACT({name: 张三, age: 18}, $.name); -- 输出张三 SELECT JSON_EXTRACT({name: 张三, age: 18}, $.age); -- 输出18注意我上面的注释提取字符串时结果带双引号提取数字时不带。这个差异是第一道坑后面会专门讲。如果你只想取字段用column-$.path这种简写它等价于 JSON_EXTRACT(column, path)。很多刚接触 MySQL JSON 的人以为 JSON_EXTRACT 和 PHP 的json_decode、Java 的JSONObject.get差不多。实际上它更像是一个“JSON 路径查询器”你给它一段路径它把命中的 JSON 片段原样返回给你。如果你想要的是普通字符串需要额外处理。2.2 路径表达式到底支持哪些写法路径表达式是 JSON_EXTRACT 的核心写错了函数会直接报错。这里我把常用写法整理成一张表路径表达式含义示例$整个 JSON 文档JSON_EXTRACT(doc, $)原样返回$.name对象里的某个键$.name取根级 name$.a.b嵌套对象路径$.consignee.name取嵌套字段$.my-key键名带特殊字符时用双引号$.order-no$[0]数组第一个元素$.items[0]取商品第一项$[n]数组第 n1 个元素$.items[1]取第二项$[*]数组所有元素$.items[*]匹配全部商品$.*对象所有字段值$.*返回所有顶层字段$.items[*].name嵌套数组中每个元素的名字所有商品名$[last]数组最后一个元素MySQL 8.0 起支持从我的实践看日常业务里 90% 的查询只用得到$、$.key、$.key1.key2、$[n]、$.items[*].key这几种。[last]虽然方便但如果你还在用 MySQL 5.7它可能是无效路径建议先确认版本。2.3 返回值为什么带着双引号JSON 类型才是关键这是 JSON_EXTRACT 新手最容易懵的地方。看这个查询SELECT JSON_EXTRACT({name: 张三}, $.name) AS name;客户端返回的是张三不是张三。很多人第一反应是“MySQL 出 bug 了”。其实没有因为 JSON_EXTRACT 的返回类型是JSON 类型。在 JSON 语法里字符串必须用双引号包裹所以张三才是合法的 JSON 字符串文本。有人会问那提取数字$.age为什么没引号因为 JSON 数字本来就不需要引号。提取布尔值时你还会看到true/false而不是数字1/0。这意味着JSON_EXTRACT 的结果不能想当然地放进 SQL 条件里比较。如果你想要不带引号的原始字符串有两个办法-- 方式一JSON_UNQUOTE 包裹 SELECT JSON_UNQUOTE(JSON_EXTRACT({name: 张三}, $.name)); -- 方式二用 - 简写等价于上面那个 SELECT {name: 张三}-$.name;-是我个人最常用的因为它就是一个词json 列、右箭头、路径、完事。后面 2.4 会展开。2.4 - 和 - 简写写代码的人偷懒的正确姿势MySQL 从 5.7 开始支持两个非常顺手的运算符json_col - $.path等价于JSON_EXTRACT(json_col, $.path)而json_col - $.path等价于JSON_UNQUOTE(JSON_EXTRACT(json_col, $.path))我建议团队里统一约定展示和比较用-需要保留 JSON 类型做进一步 JSON 处理时用-。不要混着写否则后维护的人会疯。一个典型的对比SELECT order_info-$.order_no AS with_quote, order_info-$.order_no AS without_quote FROM user_orders;with_quote返回带双引号的ORD123without_quote返回ORD123。只要理解了这个差异后续的 WHERE 条件、GROUP BY 写法就不会踩到最坑的那一种。3. 从订单表实战把 JSON 字段拆成可聚合的数据3.1 先建一张带 JSON 字段的订单表理论讲再多都不如直接上手。我实际项目里经常会用类似这样的表结构CREATE TABLE user_orders ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, order_info JSON, created_at DATETIME DEFAULT CURRENT_TIMESTAMP );插入两条测试数据INSERT INTO user_orders (user_id, order_info) VALUES (1, { order_no: ORD20250101001, consignee: {name: 张三, phone: 13800001111, province: 浙江省}, items: [ {sku_id: 101, name: 手机, price: 2999, qty: 2}, {sku_id: 102, name: 耳机, price: 399, qty: 1} ], coupon: {type: 满减, amount: 100}, remark: null }), (2, { order_no: ORD20250101002, consignee: {name: 李四, phone: 13900002222, province: 广东省}, items: [ {sku_id: 103, name: 键盘, price: 499, qty: 1} ], coupon: null });这里的order_info同一列里有的订单有 coupon有的没有有的有多个 items有的只有一个。这正是 JSON 字段的典型形态结构不统一但不能因为这个就不查。3.2 用 JSON_EXTRACT 提取订单号和收货人如果我想看每个订单的订单号、收货人姓名和手机号最顺手的写法是SELECT id, order_info-$.order_no AS order_no, order_info-$.consignee.name AS consignee_name, order_info-$.consignee.phone AS phone FROM user_orders;为什么用-而不是-因为我要的是可以直接展示的普通字符串不想带着双引号去恶心前端。如果某些场景确实需要 JSON 片段比如把整个 consignee 对象拿出来再传给下游那就用order_info-$.consignee它返回的才是嵌套 JSON 对象。这里要特别强调-并不是“去掉引号”这么简单它背后做的是 JSON_UNQUOTE也就是把 JSON 字符串解码成真实字符串。遇到 JSON 内部的转义字符比如\、\n结果也是解码后的状态。这个细节在清洗日志类数据时非常有价值。3.3 数组场景商品明细怎么拆如果只想取出第一件商品的名称和价格路径写法是SELECT order_info-$.items[0].name AS first_item_name, order_info-$.items[0].price AS first_item_price FROM user_orders;但如果要把items数组里的每一行都展开成关系表里的一行比如统计每个 SKU 卖了多少就要用到 MySQL 8.0 的 JSON_TABLE。这是我强烈建议升级到 8.0 的一个重要原因5.7 里做这种展开简直是在受刑。SELECT o.id AS order_id, o.user_id, jt.sku_id, jt.name, jt.price, jt.qty FROM user_orders o, JSON_TABLE( o.order_info, $.items[*] COLUMNS ( sku_id INT PATH $.sku_id, name VARCHAR(50) PATH $.name, price DECIMAL(10,2) PATH $.price, qty INT PATH $.qty ) ) AS jt;这段 SQL 运行后每个订单的每个商品都会变成一行后续 JOIN、GROUP BY、ORDER BY 全部可以正常使用。JSON_TABLE 本质上把 JSON 数组“掰平”成了临时表它是 JSON_EXTRACT 在复杂查询场景下最好的搭档。3.4 聚合计优惠金额、首件商品价格聚合统计是 JSON 查询里最考验基本功的地方。比如我想统计所有订单的优惠总额但coupon字段可能存在也可能不存在。如果直接聚合会得到 NULL 干扰所以用COALESCE兜底SELECT COALESCE(SUM( CAST(order_info-$.coupon.amount AS DECIMAL(10,2)) ), 0) AS total_coupon_amount FROM user_orders;这里有个很容易被忽略的点order_info-$.coupon.amount返回的是字符串直接 SUM 会隐式转换但最好显式 CAST 成 DECIMAL性能和可读性都更好。再比如我要看每个订单“第一件商品”的总销售额SELECT id, CAST(order_info-$.items[0].price AS DECIMAL(10,2)) * CAST(order_info-$.items[0].qty AS DECIMAL(10,2)) AS first_item_revenue FROM user_orders;注意items[0]的意思是“数组第 0 个元素”也就是第一件。如果某项数据里的 items 数组为空-返回 NULL运算结果也是 NULL。这种时候同样要用 COALESCE 处理。3.5 MySQL 5.7 用户没有 JSON_TABLE怎么办如果你和我一样早年接手的项目还跑在 5.7 上JSON_TABLE 是不存在的。那数组展开就只能退而求其次在应用层把 JSON 取出来循环处理。能用但没法在 SQL 里聚合。用存储过程配合临时表。能用但维护成本极高不推荐。写一段 UNION ALL 的硬编码比如最多支持 5 个商品把items[0]、items[1]... 全列出来。性能一般但至少能跑。我的真实建议是如果业务里反复要展开 JSON 数组5.7 真的不够用早点规划升级到 8.0。这不是为了赶时髦而是 JSON_TABLE 能把 80% 的“JSON 拆行”需求变成标准 SQL大幅降低以后维护的人的精神损耗。MySQL 8.0 对 JSON_EXTRACT 的路径解析也更宽松部分 5.7 中表现奇怪的路径错误8.0 里都能给出更明确的提示。4. 四年 JSON 查询经验浓缩成的四个避坑点4.1 引号造成的等值比较失败这是我见过最多、也是最隐蔽的坑。很多人写SELECT COUNT(*) FROM user_orders WHERE order_info-$.consignee.name 张三;期望查出张三的订单结果一条都没有。原因很简单order_info-$.consignee.name返回的是 JSON 字符串张三它带着双引号和 SQL 里的张三根本不相等。正确的写法是WHERE order_info-$.consignee.name 张三;或者WHERE JSON_UNQUOTE(JSON_EXTRACT(order_info, $.consignee.name)) 张三;如果是数字类型情况又不一样WHERE order_info-$.items[0].price 1000;这个写法能正常工作因为 JSON 数字2999和 SQL 数字比较不需要去引号。所以很多人会产生“JSON_EXTRACT 比较没问题”的错觉直到碰上字符串字段才在半夜被线上事故叫醒。4.2 SQL NULL、JSON null、null 字符串别混为一谈JSON 里有一个非常反直觉的细节{remark: null}里的 null 不是 SQL 的 NULL它是 JSON 的 null 值。两者在 JSON_EXTRACT 里的表现完全不同-- 键不存在 SELECT JSON_EXTRACT({a:1}, $.b); -- 返回 SQL NULL -- 键存在值是 JSON null SELECT JSON_EXTRACT({a: null}, $.a); -- 返回 JSON null不是 SQL NULL在命令行或客户端里两者都可能显示为 NULL但底层语义不同。判断“键是否存在”最稳妥的方式是JSON_CONTAINS_PATH而不是依赖 IS NULLSELECT JSON_CONTAINS_PATH(order_info, one, $.remark) FROM user_orders;如果返回 1说明路径存在返回 0说明不存在。用-$.remark取到的 JSON null在很多 MySQL 驱动里会被转换成 Java 的null字符串或别的奇怪的表示这就是应用层脏数据的来源之一。我在清洗埋点数据时吃过这个亏后来处理规则统一改成先用 JSON_CONTAINS_PATH 判断存在性再取值。4.3 路径表达式报错别慌先检查这两处JSON_EXTRACT 使用中另一个高频问题是路径写错直接报Invalid JSON path expression。我总结了一下大部分报错逃不出两个原因数组下标写成了对象键比如要把数组第三个元素写成$.items[2]但有人会写成$.items.2这在路径表达式里是无效的。特殊键名没有加双引号比如键名是order-no直接写$.order-no会被解析成减法表达式必须写$.order-no。定位这类问题没什么捷径建议你在开发环境里先单独跑一条最简单的 SELECT把路径单独抽出来验证不要直接甩进几十行的复杂 SQL 里。路径表达式本质上是一门小语言出错信息不会像 Python 那么友好只能靠多写几遍形成肌肉记忆。4.4 别在 WHERE 里顺手写 JSON_EXTRACT全表扫描警告最要命的是性能问题。很多人学会了函数之后直接写成SELECT * FROM user_orders WHERE JSON_EXTRACT(order_info, $.consignee.province) 浙江省;功能没问题但 MySQL 无法对这个 JSON 字段直接建普通索引。结果就是这个查询会把表里每一行都翻出来执行一次表达式计算数据量稍微大一点秒级延迟就来了。我在一个百万级订单表上做过测试这种写法高峰期能拖垮一个连接池。更好的做法要么是建生成列加索引下一章详细讲要么干脆在设计表结构时就把高频过滤字段提成普通列。记住一个原则JSON 是用来存“不常过滤的结构化扩展信息”的不是用来给核心查询做筛选条件的。5. 性能提升方案生成列、函数索引与多值索引5.1 为什么 JSON 列本身建不了普通索引先说为什么普通索引救不了你。MySQL 的 B 树索引要求索引列是确定的、原子的值而 JSON 列内部是一个二进制结构化的文档可以包含数组、对象、嵌套字段。我们无法直接把这一整列塞进索引里所以一直有“JSON 列不能建索引”的说法。但这句话其实只说了一半。准确的说法是不能直接对 JSON 列建普通索引但可以通过生成列把 JSON 里的某个标量字段“提”出来再对这个生成列建索引。这也是我处理 JSON 查询性能问题的主流方案。5.2 生成列 普通索引是最稳的方案生成列是从 MySQL 5.7 开始支持的功能。它的思路是让 MySQL 根据既有字段自动计算出一个新列并把这个新列当作普通列来使用。比如我想快速按收货省份过滤ALTER TABLE user_orders ADD COLUMN province VARCHAR(50) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(order_info, $.consignee.province))) STORED; ALTER TABLE user_orders ADD INDEX idx_province (province);执行完之后我再查询时可以直接走 province 普通列SELECT * FROM user_orders WHERE province 浙江省;这条查询的速度和普通字段过滤没有任何区别。而且因为生成列是存储式的它会在插入和更新时自动维护查询时普通索引照常工作。要注意的一点是生成列的表达式中不能使用自定义存储函数但 JSON_EXTRACT 这类内置 JSON 函数是允许的。如果担心磁盘占用也可以用 VIRTUAL 生成列。VIRTUAL 列不实际存储数据在 InnoDB 里同样支持建二级索引适合那种“存储空间敏感但查询频率高”的场景。我个人更常用 STORED因为它在统计、临时表、复制等场景下更不容易出现意外。5.3 MySQL 8.0 的函数索引与多值索引如果你用的是 MySQL 8.0.13 及以上版本还有另一个选择函数索引。它允许直接在表达式上建立索引不需要显式声明生成列。例如对 JSON 中的手机号建索引语法大概是这样CREATE INDEX idx_phone ON user_orders ((CAST(order_info-$.consignee.phone AS CHAR(20))));注意函数索引的表达式外面必须套两层括号这是 MySQL 为了区分普通列名而设计的。但我在生产环境里还是更偏向生成列方案原因很简单函数索引的表达式如果写得太复杂优化器不一定能准确识别出该用它生成列则是一个明明白白的列连 EXPLAIN 都更好读。另外MySQL 8.0.17 之后还引入了多值索引专门用于 JSON 数组的搜索。比如你要查询 items 数组里有没有某个 sku用 JSON_CONTAINS 结合多值索引可以大幅提速。这个功能很强大但我建议单独研究官方文档再上手因为它的语法和普通索引差别较大容易翻车。5.4 什么时候应该把 JSON 字段回归普通列到这里我也想给正在设计表结构的同学一句真心话JSON_EXTRACT 和生成列能解决很多问题但它们不是让你把所有东西都塞进 JSON 的理由。我自己的判断标准是如果一个 JSON 字段里的某个 key在未来三个月内会被用在 WHERE、ORDER BY、GROUP BY 或 JOIN 里超过两次那就应该在设计阶段直接把它提升成普通列。实际操作中我甚至会先预留几个常见的生成列比如ALTER TABLE user_orders ADD COLUMN order_no VARCHAR(32) GENERATED ALWAYS AS (order_info-$.order_no) STORED, ADD COLUMN user_province VARCHAR(50) GENERATED ALWAYS AS (order_info-$.consignee.province) STORED;这样业务代码不需要改依然是写入 order_info但查询侧已经有了稳定的索引列。这是我目前觉得“鱼和熊掌兼得”的最优解写入侧保留 JSON 的灵活性查询侧拿到普通列的性能。最后再分享一个小技巧我在订单表上吃过几次全表扫描的亏之后养成了一个习惯——所有新增的 JSON 查询都会先跑一遍 EXPLAIN看看能不能命中索引。如果发现 type 是 ALL那就说明这次查询没有走任何索引就要警惕了。JSON_EXTRACT 本身只是个函数用得好它是瑞士军刀用得不好它就是全表扫描的放大器。希望这些经验能帮你少踩几个和我一样的坑。