企业官网建设流程全解析

做数据支持的时候，经常能遇到一类需求：业务方在接口里塞了一个 JSON 字段，库里存的也是一整串 JSON 字符串，然后过了两天提数的人跑过来说，“帮我把这个字段里的某某值取出来，按它筛选一下数据”。以前我第一反应是拉出来用 Python 解析，后来发现这个动作在 MySQL 里就能做，而且做得还挺干净。这篇文章就把我实际用下来的一些函数、操作思路和踩过的坑整理一遍，主要围绕 JSON_EXTRACT、->、->> 和 JSON_TABLE 这几个东西展开，顺便把隐式转换和索引这两个最磨人的点也说清楚。

1. 非用 MySQL 来抽 JSON 不可吗？先算清楚这笔账

1.1 什么时候该用数据库抽，什么时候该用程序抽

很多人一听到“从 MySQL 提取 JSON 数据”，第一反应是：这种脏活不是应该在程序里做吗？确实，如果数据量小、一次性导出，放到 Python、Java 或者 Kettle 里面解析，反而更灵活。但实际工作中，至少有三类场景在 MySQL 里直接处理会舒服得多。

第一类是联表查询场景。你需要把 JSON 字段里的某个值作为关联条件，去 JOIN 另外一张表。这时候把数据拉出去解析再关联，等于要自己实现一遍 JOIN 逻辑，纯属脱裤子放屁。第二类是持续性的查询报表。比如运营后台每五分钟刷一次大屏，数据源直连 MySQL，你不可能让大屏先去调一段 Python 脚本再拿结果。第三类是增量数据处理。上游业务表每天写入带 JSON 字段的记录，下游要实时筛选，这种链路用 SQL 函数处理最顺。

反过来，如果一次要解析几百万行数据、每个 JSON 特别大、路径特别深，那 MySQL 的函数运算效率就不如程序端的内存解析了。所以我的判断标准很简单：能在一个 SQL 里完成的事，别拆成两个系统做；但涉及大数据量复杂 JSON 解析时，别硬扛。

1.2 MySQL 版本要求与函数全景

要在 MySQL 里操作 JSON，版本至少是 5.7，因为 JSON 类型和相关函数从 5.7 才开始正式支持。8.0 之后在此基础上加了 JSON_TABLE 这种把数组展开成行的函数，实用性进一步提升。生产环境如果还是 5.6，那就别折腾了，老实导出到程序里处理吧。

MySQL 提供的 JSON 处理函数不少，但我实际高频使用的主要就这些：

从这张表能看出，围绕“提取”这个动作有两条线：一条是直接取值，用的是 JSON_EXTRACT 家族；另一条是结构分析，用的 JSON_KEYS、JSON_CONTAINS 这些。后面实战部分我会把两条线都串起来讲。

2. 三个最常用的提取函数：JSON_EXTRACT、-> 和 ->> 的区别

2.1 从一条最简单的提取开始

先用一个最常见的例子“热身”。假设你有一张用户表user_log，里面有个字段extra，存的是 JSON 字符串，内容类似这样：

{"device": "iPhone 14", "os": "iOS 16.4", "location": {"city": "上海", "lat": 31.23}, "tags": ["vip", "new_user"]}

现在要取出device的值，SQL 可以这样写：

SELECT JSON_EXTRACT(extra, '$.device') AS device_json FROM user_log;

这里$.device是 JSON 路径语法，$代表整个 JSON 文档，.device表示取device这个 key 对应的值。执行结果是：

"iPhone 14"

注意，结果是带双引号的。因为 JSON_EXTRACT 的返回值仍然是 JSON 类型，JSON 中的字符串在返回时就是带引号的。这在某些客户端里会显示成"iPhone 14"，如果你拿这个值去和普通字符串比较，是比不上的，这一点最容易坑到人。

2.2 -> 和 ->> 的等价关系

为了避免上面那个问题，MySQL 给了两个运算符。->和JSON_EXTRACT完全等价，也就是返回的值仍然带引号。而->>是在提取之后自动做了一次JSON_UNQUOTE，也就是把外侧引号剥掉。所以下面这三句话效果是一样的：

SELECT extra->'$.device' FROM user_log; SELECT JSON_EXTRACT(extra, '$.device') FROM user_log; SELECT JSON_UNQUOTE(JSON_EXTRACT(extra, '$.device')) FROM user_log;

而这两句话效果一样，返回的都是不带引号的纯文本：

SELECT extra->>'$.device' FROM user_log; SELECT JSON_UNQUOTE(extra->'$.device') FROM user_log;

在实际使用时，我基本不用->，因为带引号的返回结果要么还得再包一层JSON_UNQUOTE，要么就是在比较的时候踩坑。直接养成本能用->>就用->>的习惯，能省掉很多麻烦。

2.3 路径语法里容易被忽略的细节

JSON 路径看着简单，但有几个细节值得单独说一下。

数组下标从 0 开始。要取tags数组的第一个元素，路径写成$.tags[0]，而不是$.tags[1]。如果下标越界，MySQL 返回 NULL，不会报错。这就意味着你拿$['.tags[10]']去取数时，如果数组没那么多元素，Sql 不会提示，而是安静地返回 NULL，这需要你在代码里对结果做空值兜底。

key 名里带空格或特殊字符，路径要用双引号包住。比如 JSON 里有{"user name": "张三"}，取数路径就得写成$."user name"。这个细节在接第三方数据时特别常见，对方接口的 key 往往不规范。

嵌套路径可以一层一层往下写。extra->>'$.location.city'可以直接取到上海，不需要先取location再取city。对于多层嵌套的对象，路径可以一直往下点，这就很像在文件系统里找某个深层目录下的文件。

关于路径语法，我建议先用JSON_KEYS()看一个 JSON 对象里有哪些 key，再动手写路径，比自己瞎猜靠谱很多：

SELECT JSON_KEYS(extra) FROM user_log;

返回结果是一组 key 的 JSON 数组，比如["device", "os", "location", "tags"]。虽然很多场景下你知道接口文档里定义了哪些字段，但实际入库的数据偶尔会有出入，用JSON_KEYS快速核验一下，能避免路径写错导致整列数据全是 NULL。

3. 实战案例：把订单表的 JSON 字段拆成结构化查询

3.1 准备一张带 JSON 字段的业务表

为了把提取函数真正串起来用，我构造一个更接近实际业务的例子。假设电商系统里有一张订单表order_info，其中有几个核心字段：

CREATE TABLE order_info ( id INT PRIMARY KEY, order_no VARCHAR(32), user_id INT, order_time DATETIME, amount DECIMAL(10,2), extra JSON );

这里的extra字段类型是 JSON，里面存放了下单时的扩展信息。可能长这样：

{ "source": "app", "promotion": {"coupon_amount": 20, "type": "满减"}, "items": [ {"sku": "1001", "name": "手机壳", "price": 39.00, "qty": 2}, {"sku": "1002", "name": "钢化膜", "price": 19.00, "qty": 1} ], "address": {"province": "浙江省", "city": "杭州市", "detail": "xxx路xx号"} }

实际生产里，有些团队会把extra字段设计为 VARCHAR/TEXT 来存 JSON 字符串，由于 MySQL 本身有 JSON 类型，直接用 JSON 字段类型的查询性能和合法性检查都优于 VARCHAR，这一点后面的索引部分也会提到。

3.2 提取用户信息与订单金额

现在的需求是：从订单表里取订单号、来源渠道、优惠券金额和收货城市。直接在SELECT语句里用->>就能完成提取：

SELECT order_no, extra->>'$.source' AS source, extra->>'$.promotion.coupon_amount' AS coupon_amount, extra->>'$.address.city' AS city FROM order_info WHERE user_id = 1024;

执行结果大概是这样的：

这里的coupon_amount是从 JSON 里取出来的字符串，如果后面要参与金额计算，需要先确认数据类型。比如计算实际支付金额时，直接写：

SELECT order_no, amount - CAST(extra->>'$.promotion.coupon_amount' AS DECIMAL(10,2)) AS paid_amount FROM order_info WHERE user_id = 1024;

extra->>'$.promotion.coupon_amount'返回的是"20"这种带引号的 JSON 字符串，通过CAST(... AS DECIMAL)变回数值参与运算。这一步看起来简单，但其实是很重要的习惯，因为 JSON 提取出来的数据在 MySQL 内部按字符串路线走，遇到数值运算必须显式转换。

3.3 按 JSON 里的值做筛选和排序

提取出的值不光要出现在 SELECT 列表里，还经常要作为 WHERE 条件和 ORDER BY 条件。比如筛选所有来自 App 渠道且优惠券金额大于 10 元的订单：

SELECT order_no, extra->>'$.source' AS source, extra->>'$.promotion.coupon_amount' AS coupon_amount FROM order_info WHERE extra->>'$.source' = 'app' AND CAST(extra->>'$.promotion.coupon_amount' AS DECIMAL(10,2)) > 10;

这里有两个点值得注意。第一，extra->>'$.source'返回的是纯字符串，所以和'app'比较没问题，直接用，不比较就不知道刚才说的去引号有多重要。第二，coupon_amount在 JSON 里是一个数字，但->>取出来变成字符串之后，MySQL 在做比较的时候会尝试隐式转换，但为了避免不可控的转换规则，最好还是显式CAST到数值类型。

再比如，按订单里商品数组的数量排序，可以用JSON_LENGTH()这个函数：

SELECT order_no, JSON_LENGTH(extra->'$.items') AS item_cnt FROM order_info ORDER BY item_cnt DESC;

JSON_LENGTH返回 JSON 数组的元素个数，是处理数组结构时一个相当顺手的工具。之前有同事一直不知道这个函数，用 Python 把数据拉出去数了一遍再导回来，属实绕了大远路。

4. 数组和动态 key 的处理：JSON_TABLE 与路径的边界

4.1 JSON_TABLE 的完整展开流程

前面的例子都是提取对象里的某个 key，或者对数组计算长度。但很多需求本质上是把 JSON 数组展开成多行。比如上面那张订单表，每个订单里有多个商品，要统计每个 SKU 的销量，最常见的做法是把数组展开，每个商品变成一行。在 MySQL 8.0 里，这个工作由JSON_TABLE完成。

直接看用法：

SELECT o.order_no, t.sku, t.name, t.price, t.qty FROM order_info o, JSON_TABLE( o.extra, '$.items[*]' COLUMNS ( sku VARCHAR(20) PATH '$.sku', name VARCHAR(50) PATH '$.name', price DECIMAL(10,2) PATH '$.price', qty INT PATH '$.qty' ) ) AS t;

说一下这段 SQL 的逻辑。$.items[*]表示遍历items数组里的每一个元素，相当于把数组“拍扁”。COLUMNS里面定义展开出的每一列数据从哪里取：sku取当前数组元素的$.sku路径，price取$.price等等。执行完之后，一行订单如果有两个商品，就会变成两行数据。这个输出就是标准的行列转换逻辑，比在程序里循环拼 SQL 再 UNION ALL 要干净太多。

4.2 数组过滤与存在性判断

除了展开整个数组，业务上经常还要判断数组里有没有某个元素。比如找出订单商品里包含 sku 为1001的所有订单，可以用JSON_CONTAINS：

SELECT order_no FROM order_info WHERE JSON_CONTAINS(extra->'$.items', JSON_OBJECT('sku', '1001'));

这里写法有一点点绕：JSON_CONTAINS的第二个参数是要查找的目标值，必须是 JSON 格式。所以不能直接写'1001'，要写JSON_OBJECT('sku', '1001')这个 JSON 片段。如果你只是想判断数组里是否包含某个字符串，同样可以简化成：

WHERE JSON_CONTAINS(extra->'$.tags', '"vip"')

注意"vip"这里的双引号不能丢，因为 JSON 字符串本身就带引号。判断包含关系时，最容易犯的错就是忘了加内层引号。建议写下标时要带上注释，过一个月自己回来看都能立刻明白这段 SQL 在查什么。

4.3 动态 key 的应对思路

还有一种需求更麻烦：JSON 里的 key 不是固定的，而是动态的。比如埋点日志里存了不同事件的属性：

{"event": "click", "props": {"button_id": "btn_1"}} {"event": "view", "props": {"page_name": "home"}}

props下面的 key 完全取决于事件类型。这时候用extra->>'$.props.button_id'只能适配点击事件，对查看事件就返回 NULL。应对这种场景，有两条路。

一条是用JSON_KEYS先捞出动态 key 列表，在程序层做二次处理。另一条是如果确实只需要某一类事件的某个固定字段，就配合WHERE条件把事件类型限定住，再去取那个 key。如果动态 key 的组合非常多且需要灵活查询，老实说 MySQL 并不是最好的工具，这时候把 JSON 交给搜索引擎或者专门的文档数据库更合理。在关系库里硬解动态 schema，性能和灵活性两头都不讨好，这个边界要拎清。

5. 性能与踩坑：隐式转换、空值和索引这三件事最磨人

5.1 最常见的隐式转换问题

从 JSON 里提取的值，经常是字符串形态，但你以为拿到的数字，所以直接和数值比较。比如：

SELECT * FROM order_info WHERE extra->>'$.promotion.coupon_amount' > 10;

MySQL 在这种情况下会尝试把extra->>'$.promotion.coupon_amount'的字符串值转为数字，再和 10 比较。大多数时候结果是对的，但这属于隐式转换，有两个风险：一是字符串转数字时如果 JSON 里存的是"20元"这种带单位的字符串，转换结果可能是 20，也可能变成 0，你根本想不到；二是转换会放弃索引，后面第五节的索引方案就完全失效。

我的原则是：凡是提取出来要参与计算和比较的字段，一律显式 CAST。虽然代码看着啰嗦，但能把行为锁死。比如：

WHERE CAST(extra->>'$.promotion.coupon_amount' AS DECIMAL(10,2)) > 10

这样一旦数据格式不合预期，MySQL 会直接报错而不是悄悄返回一个让你查半天的问题数字。数据解析宁可报错，也别静默出错——这句话在做数据抽取时怎么强调都不过分。

5.2 空值判断和解析失败

JSON 提取还有一个高频问题：取出的值是 NULL，但到底是因为 key 不存在，还是因为整个 JSON 字段就是 NULL，还是因为 JSON 本身格式非法？这三种情况得区分清楚。

SELECT extra, extra->>'$.promotion.coupon_amount' AS coupon FROM order_info;

如果extra字段本身是 NULL，那结果就是 NULL。如果 extra 字段有值但是 JSON 里没有promotion这个 key，那个子路径解析结果也是 NULL。这两种情况在查询结果上几乎是不可区分的。那怎么办？如果业务上必须区分，可以改用显式的存在性判断：

SELECT extra, JSON_CONTAINS_PATH(extra, 'one', '$.promotion.coupon_amount') AS has_coupon FROM order_info;

JSON_CONTAINS_PATH返回 1 表示路径存在，返回 0 表示不存在。用这个函数可以先判断 key 是否存在，再做后续操作，比直接解析一个 NULL 回来再猜原因要精准得多。

另外，如果 JSON 字段类型是 VARCHAR 或者 TEXT，里面的字符串不是合法 JSON 格式，MySQL 会在执行 JSON 函数时报出Invalid JSON text错误。这种错误在数据量大的时候就是灾难。我的习惯是在写入层就保证 JSON 合法，但如果历史数据已经乱了，筛选的时候可以先给 JSON 列加入格式校验条件，比如在 MySQL 5.7+ 里直接把这个列改成 JSON 类型，MySQL 会自动验证并拒绝非法值。

5.3 生成列加索引的思路

最后一个大坑是性能。JSON 字段本身不能被传统索引直接优化，因为 B+ 树索引要求数据是有序可比较的值，而 JSON 是一个整体结构。所以如果你在 WHERE 里直接写extra->>'$.source' = 'app'，MySQL 只能把整个 extra 字段读出来解析一遍，这在百万行数据上就是全表扫描。

解决办法是用生成列（Generated Column）把 JSON 里的值“提”成一个普通列，然后在这个列上建索引。具体做法：

ALTER TABLE order_info ADD COLUMN source VARCHAR(20) GENERATED ALWAYS AS (extra->>'$.source') STORED; CREATE INDEX idx_source ON order_info(source);

这里把extra->>'$.source'定义为一个持久化的生成列。之后查询直接写WHERE source = 'app'，就能走普通索引了。

生成列有两种：STORED和VIRTUAL。STORED 会在插入数据时把提取结果持久化到磁盘，占空间但是索引效率高；VIRTUAL 不占物理存储，但建索引时 MySQL 支持在虚拟列上也建索引。以我个人的经验，如果这个字段查询频率很高，建议用 STORED，查询性能更稳定。

用上生成列之后，不仅查询快了，语义也更清晰。索引生效需要有意识地设计，如果你在一个需要天天查的 JSON 字段上用了->>做过滤，而不建生成列，数据库早晚会给你还以颜色。

还有一点需要提醒，生成列表达式中的函数必须是确定的。如果这个 extra 字段本身是 JSON 类型，生成列写法会更稳定；如果字段是 VARCHAR 存储的 JSON，也可以建生成列，但前提是原列里的所有值都能被解析成合法 JSON。

最后补几个实际体会

说句实在话，MySQL 处理 JSON 的能力，放在今天看来，不是让你把数据库当成万能 JSON 解析器用，而是解决那些“轻量级、偶发性”的提取需求。如果业务字段结构很固定，字段量又少，干脆拆成独立列存储，别包在一层 JSON 里；如果 JSON 结构深度超过三四层、数组嵌套复杂、查询频率又高，那 MySQL 里的函数操作就开始显得别扭了，这种场景下配合程序端解析，或者干脆换文档数据库，才是治本。

我在实际运维中体会最深的还是那句老话：查询之前先想清楚数据类型和路径是否存在。很多线上慢查询和数据异常不是 MySQL 做得不够好，而是写 SQL 的人在提取 JSON 后没处理隐式转换，没检查路径返回 NULL 的情况，也没想着加生成列索引。把这三样东西从潜意识里变成条件反射，再复杂的 JSON 提取需求，处理起来也会踏实很多。

万一哪天你接手一张全是 JSON 字符串的旧表，别慌，先从JSON_KEYS开始，逐步把常用的几个 key 用生成列提出来，配合->>和JSON_TABLE做完你手里的提数任务，你就会发现，这个看上去没头没尾的 JSON 字段，也就是一层窗户纸的事。