企业官网建设流程全解析

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 类型之后，再这么做就亏大了。区别主要体现在三方面：

1. 合法性校验：JSON 类型在写入时会强校验，非法 JSON 直接报错，不会让脏数据悄悄落地。
2. 存储格式：JSON 类型内部用二进制格式存储，读取速度比 TEXT 明文快；而且 MySQL 8.0 在一定条件下支持 JSON 部分更新，不会整列重写。
3. 数据库层函数：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 的核心，写错了函数会直接报错。这里我把常用写法整理成一张表：

从我的实践看，日常业务里 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返回带双引号的"ORD123"，without_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 NULL：

SELECT 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 本身只是个函数，用得好它是瑞士军刀，用得不好它就是全表扫描的放大器。希望这些经验能帮你少踩几个和我一样的坑。