MySQL 5.7+ JSON_EXTRACT函数详解：从存储到查询的全流程

JSON_EXTRACT 函数基础语法

JSON_EXTRACT 允许通过指定的路径（path）从 JSON 文档中提取数据。其基本结构如下：

JSON_EXTRACT(json_doc, path[, path] ...)

• json_doc：需要处理的 JSON 字符串或列名。
• path：指定要提取的数据位置，必须以美元符号 $ 开头。

对于 JSON 对象，通常使用 $.字段名 表示法；对于 JSON 数组，则使用 $[索引] 结合点号访问属性。

环境准备与数据构建

第1步：创建测试表

虽然 MySQL 5.7+ 推荐使用 JSON 类型，但为了兼容旧版习惯或特定场景，许多用户仍使用 TEXT 或 VARCHAR 存储 JSON 字符串。以下创建一个包含主键和 JSON 字段的测试表：

CREATE TABLE `json_table` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `val` json DEFAULT NULL COMMENT 'json字符串',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

第2步：插入测试数据

插入包含不同结构的 JSON 数据，包括纯对象和包含数组的对象，以便后续演示不同提取方式：

INSERT INTO `json_table` VALUES 
(1, '{"name": "一灰灰blog", "age": 18}'),
(2, '{"name": "一灰灰blog", "site": "https://blog.hhui.top"}');

核心操作：提取与查询演示

第3步：简单对象字段提取

针对上述插入的两条记录，提取 name 字段的值。注意，函数返回的结果自带双引号，符合 JSON 字符串规范：

SELECT json_extract(`val`, '$.name') FROM `json_table`;

-- 输出结果：
| "一灰灰blog" | -- id=1
| "一灰灰blog" | -- id=2

第4步：处理缺失字段与多字段查询

当请求的 Key 不存在时，JSON_EXTRACT 不会抛出异常，而是返回 NULL。这一特性非常适合处理非结构化或半结构化的 JSON 数据：

SELECT 
  json_extract(`val`, '$.name') AS name,
  json_extract(`val`, '$.site') AS site 
FROM `json_table`;

-- 输出结果：
| name             | site                          |
| "一灰灰blog"     | NULL                          | -- id=1 (无site字段)
| "一灰灰blog"     | "https://blog.hhui.top"       | -- id=2

第5步：JSON 数组内的数据提取

若 JSON 值为数组结构，需结合数组索引访问。例如，向表中插入一条数组型 JSON 数据：

INSERT INTO `json_table` VALUES 
(3, '[{"name": "一灰灰", "site": "https://spring.hhui.top"}]');

提取该数组第一个元素的 name 属性，路径语法为 $[0].字段名：

SELECT json_extract(`val`, '$[0].name') 
FROM `json_table` 
WHERE id = 3;

-- 输出结果：
| "一灰灰" |
+----------+

第6步：在 WHERE 子句中使用 JSON 过滤

JSON_EXTRACT 不仅可用于 SELECT 返回结果，还可直接在条件判断中使用，实现类似关系型字段的精确查询：

SELECT * FROM `json_table` 
WHERE json_extract(`val`, '$.name') = '"一灰灰blog"';

-- 注意：由于返回的是带引号的JSON字符串，匹配时需包含双引号。
-- 更常见的做法是使用 JSON_VALUE 或在应用层处理，但在标准 SQL 中直接匹配需注意格式差异。

总结

通过 JSON_EXTRACT 函数，开发者可以在 MySQL 层面直接对存储在列中的 JSON 数据进行细粒度查询，避免了将大量数据取出至应用层再解析的性能开销。掌握 $.key 和 $[idx].key 的路径语法，是高效处理 NoSQL 风格数据存储的关键技能。

以上就是 MySQL 中 JSON_EXTRACT 函数处理 JSON 字段详情的全部内容，更多关于 MySQL 数据处理与优化资料请关注本站其它相关文章！