MySQL `JSON_EXTRACT` 语法、示例与性能优化技巧 – wiki基地

---

深入解析 MySQL JSON_EXTRACT：语法、示例与性能优化

随着 Web 应用和数据源的日益复杂，非结构化和半结构化数据变得越来越普遍。JSON (JavaScript Object Notation) 以其轻量级、易读性和易于机器解析的特性，已成为数据交换和存储的流行格式。MySQL 从 5.7 版本开始引入了对原生 JSON 数据类型的支持，并提供了一系列强大的函数来操作 JSON 数据，其中 JSON_EXTRACT 是最核心和最常用的函数之一，用于从 JSON 文档中提取指定路径的数据。

本文将深入探讨 MySQL JSON_EXTRACT 函数，涵盖其详细语法、丰富的应用示例以及至关重要的性能优化技巧，旨在帮助开发者和数据库管理员更高效地利用 MySQL 处理 JSON 数据。

一、JSON 在 MySQL 中的重要性

在引入原生 JSON 类型之前，开发者通常将 JSON 数据存储为 TEXT 或 BLOB 类型。这种方式虽然可行，但存在诸多弊端：

1. 数据校验困难：数据库无法保证存入的是有效的 JSON 格式。
2. 查询效率低下：需要将整个文本取出，在应用层进行解析和查询，无法利用数据库索引。
3. 更新操作复杂：修改 JSON 内部某个值需要读取整个文本、解析、修改、序列化再写回，开销巨大。

MySQL 原生 JSON 类型的出现解决了这些问题：

1. 自动校验：在插入或更新时，MySQL 会自动校验 JSON 文档的有效性。
2. 优化存储：内部采用二进制格式存储，读取更高效。
3. 强大的 JSON 函数：提供 JSON_EXTRACT, JSON_SET, JSON_INSERT, JSON_REPLACE, JSON_REMOVE, JSON_CONTAINS, JSON_SEARCH 等一系列函数，可以直接在 SQL层面操作 JSON 数据。
4. 索引支持：虽然不能直接对 JSON 内部路径建立标准索引，但可以通过生成列 (Generated Columns) 或函数索引 (Functional Indexes, MySQL 8.0.13+) 实现对 JSON 特定路径的高效查询。

JSON_EXTRACT 正是这些函数中的基石，它使得我们能够精确地从复杂的 JSON 结构中检索所需信息。

二、JSON_EXTRACT 语法详解

JSON_EXTRACT 函数的基本语法如下：

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

参数说明:

1. json_doc:

   • 必需参数。
   • 表示要从中提取数据的 JSON 文档。
   • 这可以是一个存储 JSON 数据的列名、一个 JSON 字符串字面量，或者是一个返回 JSON 值的 SQL 变量或表达式。
   • 如果 json_doc 不是有效的 JSON 格式，函数会报错（除非在 SQL MODE 中设置了允许无效 JSON）。

2. path:

   • 必需参数，至少需要一个。
   • 表示要提取数据的 JSON 路径表达式 (JSON Path Expression)。
   • 可以提供一个或多个路径参数。
   • 如果路径表达式无效或在 json_doc 中找不到对应的元素，该路径将返回 SQL NULL。

JSON 路径表达式 (JSON Path Expression) 语法:

JSON 路径表达式用于定位 JSON 文档中的特定元素。其语法规则如下：

• $：表示文档的根元素。
• .key_name：表示对象的成员（键值对）。如果键名包含特殊字符（如空格、点号 . 等），需要用双引号括起来，例如 ."key with space"。
• [index]：表示数组的元素，索引从 0 开始。例如 [0] 表示第一个元素，[1] 表示第二个元素。
• .*：通配符，表示对象的所有成员的值。
• [*]：通配符，表示数组的所有元素。
• [N to M]：(较新版本支持) 表示数组的索引范围。
• **.key_name：(递归下降，部分场景或特定函数支持) 查找任意层级的名为 key_name 的键。JSON_EXTRACT 对 ** 的支持可能有限或行为与其他函数（如 JSON_SEARCH）不同，通常更推荐使用明确的路径。

返回值:

• 如果只提供一个 path 参数，JSON_EXTRACT 返回该路径对应的值。这个值本身仍然是 JSON 格式（例如，字符串会带双引号，数字不带，对象或数组会保持其结构）。
• 如果提供了多个 path 参数，JSON_EXTRACT 返回一个 JSON 数组，数组中的元素依次是每个 path 对应的值。
• 如果某个 path 在 json_doc 中不存在，则该路径返回 SQL NULL。当有多个路径时，对应的数组元素会是 JSON null。
• 如果 json_doc 参数本身是 SQL NULL，则函数返回 SQL NULL。

与 -> 和 ->> 操作符的关系:

MySQL 提供了两个便捷的内联路径操作符，它们是 JSON_EXTRACT 的语法糖：

• column -> path: 等价于 JSON_EXTRACT(column, path)。返回结果是 JSON 类型的值。
• column ->> path: 等价于 JSON_UNQUOTE(JSON_EXTRACT(column, path)) 或 JSON_UNQUOTE(column -> path)。它提取值后，还会去除结果两端的双引号（如果结果是 JSON 字符串），将其转换为 SQL 字符串类型。这在需要将提取的值用于字符串比较或显示时非常方便。

理解 -> 和 ->> 的区别至关重要：-> 保留 JSON 类型，->> 返回 SQL 字符串。例如，提取数字 123，两者都返回 123；提取字符串 "abc"，-> 返回 "abc" (带引号的 JSON 字符串)，而 ->> 返回 abc (不带引号的 SQL 字符串)。

三、JSON_EXTRACT 示例

假设我们有一个 products 表，其中包含一个名为 details 的 JSON 列，存储产品的详细信息：

“`sql
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
details JSON
);

INSERT INTO products (name, details) VALUES
(‘Laptop Pro’, ‘{
“brand”: “TechCorp”,
“specs”: {
“cpu”: “Intel i7”,
“ram”: 16,
“storage”: {
“type”: “SSD”,
“size_gb”: 512
},
“screen”: {
“size_inch”: 15.6,
“resolution”: “1920×1080”
}
},
“features”: [“Backlit Keyboard”, “Fingerprint Reader”, “USB-C”],
“stock”: {
“warehouse_a”: 50,
“warehouse_b”: 25
},
“colors_available”: [“Silver”, “Space Gray”]
}’),
(‘Wireless Mouse’, ‘{
“brand”: “ClickFast”,
“type”: “Wireless”,
“dpi”: [800, 1200, 1600],
“features”: [“Ergonomic Design”, “Rechargeable”],
“stock”: {
“warehouse_a”: 200,
“warehouse_c”: 150
},
“colors_available”: [“Black”, “White”]
}’),
(‘Keyboard’, ‘{
“brand”: “TypeWell”,
“type”: “Mechanical”,
“layout”: “US”,
“features”: [“RGB Backlight”, “Anti-Ghosting”],
“stock”: {
“warehouse_a”: 100
}
}’);

— 添加一个没有 details 的产品
INSERT INTO products (name, details) VALUES (‘Webcam’, NULL);
“`

现在，我们来看 JSON_EXTRACT 的各种用法：

1. 提取顶层对象的成员:

“`sql
— 提取 ‘Laptop Pro’ 的品牌 (brand)
SELECT JSON_EXTRACT(details, ‘$.brand’)
FROM products
WHERE name = ‘Laptop Pro’;
— 结果: “TechCorp” (JSON string)

— 使用 -> 操作符达到同样效果
SELECT details -> ‘$.brand’
FROM products
WHERE name = ‘Laptop Pro’;
— 结果: “TechCorp” (JSON string)

— 使用 ->> 操作符提取品牌为 SQL 字符串
SELECT details ->> ‘$.brand’
FROM products
WHERE name = ‘Laptop Pro’;
— 结果: TechCorp (SQL string)
“`

2. 提取嵌套对象的成员:

“`sql
— 提取 ‘Laptop Pro’ 的 CPU 型号
SELECT JSON_EXTRACT(details, ‘$.specs.cpu’)
FROM products
WHERE name = ‘Laptop Pro’;
— 结果: “Intel i7” (JSON string)

— 提取 ‘Laptop Pro’ 的存储大小 (GB)
SELECT JSON_EXTRACT(details, ‘$.specs.storage.size_gb’)
FROM products
WHERE name = ‘Laptop Pro’;
— 结果: 512 (JSON number)

— 使用 -> 操作符链式访问
SELECT details -> ‘$.specs’ -> ‘$.storage’ -> ‘$.size_gb’
FROM products
WHERE name = ‘Laptop Pro’;
— 结果: 512 (JSON number)
“`

3. 提取数组元素:

“`sql
— 提取 ‘Laptop Pro’ 的第一个特性 (feature)
SELECT JSON_EXTRACT(details, ‘$.features[0]’)
FROM products
WHERE name = ‘Laptop Pro’;
— 结果: “Backlit Keyboard” (JSON string)

— 提取 ‘Wireless Mouse’ 的第二个 DPI 值
SELECT JSON_EXTRACT(details, ‘$.dpi[1]’)
FROM products
WHERE name = ‘Wireless Mouse’;
— 结果: 1200 (JSON number)

— 使用 -> 操作符
SELECT details -> ‘$.features[0]’
FROM products
WHERE name = ‘Laptop Pro’;
— 结果: “Backlit Keyboard” (JSON string)
“`

4. 提取整个子对象或子数组:

“`sql
— 提取 ‘Laptop Pro’ 的完整 ‘specs’ 对象
SELECT JSON_EXTRACT(details, ‘$.specs’)
FROM products
WHERE name = ‘Laptop Pro’;
— 结果: {“cpu”: “Intel i7”, “ram”: 16, “screen”: {“size_inch”: 15.6, “resolution”: “1920×1080”}, “storage”: {“type”: “SSD”, “size_gb”: 512}} (JSON object)

— 提取 ‘Wireless Mouse’ 的所有 DPI 值 (整个数组)
SELECT JSON_EXTRACT(details, ‘$.dpi’)
FROM products
WHERE name = ‘Wireless Mouse’;
— 结果: [800, 1200, 1600] (JSON array)
“`

5. 使用通配符:

“`sql
— 提取 ‘Laptop Pro’ 的 ‘stock’ 对象中所有仓库的库存量
SELECT JSON_EXTRACT(details, ‘$.stock.*’)
FROM products
WHERE name = ‘Laptop Pro’;
— 结果: [50, 25] (JSON array containing values)

— 提取 ‘Wireless Mouse’ 的所有特性 (features)
SELECT JSON_EXTRACT(details, ‘$.features[*]’)
FROM products
WHERE name = ‘Wireless Mouse’;
— 结果: [“Ergonomic Design”, “Rechargeable”] (JSON array, identical to extracting the array itself in this case, but useful if you wanted transformation later)
“`

6. 提取多个路径的值:

sql
-- 同时提取 'Laptop Pro' 的品牌和 RAM 大小
SELECT JSON_EXTRACT(details, '$.brand', '$.specs.ram')
FROM products
WHERE name = 'Laptop Pro';
-- 结果: ["TechCorp", 16] (JSON array)

7. 处理不存在的路径或 NULL JSON:

“`sql
— 尝试提取 ‘Keyboard’ 的屏幕分辨率 (不存在)
SELECT JSON_EXTRACT(details, ‘$.specs.screen.resolution’)
FROM products
WHERE name = ‘Keyboard’;
— 结果: NULL (SQL NULL)

— 尝试从 ‘Webcam’ (details 为 NULL) 提取数据
SELECT JSON_EXTRACT(details, ‘$.brand’)
FROM products
WHERE name = ‘Webcam’;
— 结果: NULL (SQL NULL)

— 提取多个路径，其中一个不存在
SELECT JSON_EXTRACT(details, ‘$.brand’, ‘$.nonexistent_path’)
FROM products
WHERE name = ‘Laptop Pro’;
— 结果: [“TechCorp”, null] (JSON array with JSON null for the missing path)
“`

8. 在 WHERE 子句中使用:

“`sql
— 查找所有品牌为 “TechCorp” 的产品
— 注意：这里比较的是 JSON 字符串 “TechCorp”
SELECT name
FROM products
WHERE JSON_EXTRACT(details, ‘$.brand’) = ‘”TechCorp”‘;
— 或者使用 -> 操作符
SELECT name
FROM products
WHERE details -> ‘$.brand’ = ‘”TechCorp”‘;

— 查找所有品牌为 “TechCorp” 的产品 (使用 ->> 更方便比较 SQL 字符串)
SELECT name
FROM products
WHERE details ->> ‘$.brand’ = ‘TechCorp’;

— 查找 RAM 大于 8GB 的产品
SELECT name, details ->> ‘$.specs.ram’ AS ram_gb
FROM products
WHERE JSON_EXTRACT(details, ‘$.specs.ram’) > 8;
— 注意：JSON_EXTRACT 返回的是 JSON number，可以直接进行数值比较

— 查找特性 (features) 数组包含 “Fingerprint Reader” 的产品
— 需要使用 JSON_CONTAINS 函数
SELECT name
FROM products
WHERE JSON_CONTAINS(details -> ‘$.features’, ‘”Fingerprint Reader”‘);
“`

9. 在 ORDER BY 或 GROUP BY 中使用:

“`sql
— 按存储大小降序排列产品
SELECT name, details ->> ‘$.specs.storage.size_gb’ AS storage_size
FROM products
ORDER BY CAST(details ->> ‘$.specs.storage.size_gb’ AS UNSIGNED) DESC;
— 注意：->> 返回的是字符串，需要 CAST 转换为数字才能正确排序

— 按品牌统计产品数量
SELECT details ->> ‘$.brand’ AS brand, COUNT(*) AS count
FROM products
WHERE details IS NOT NULL AND details ->> ‘$.brand’ IS NOT NULL
GROUP BY brand;
“`

这些示例展示了 JSON_EXTRACT 及其相关操作符的灵活性和强大功能，使其成为处理 MySQL 中 JSON 数据的关键工具。

四、性能考量与优化技巧

虽然 JSON_EXTRACT 功能强大，但在处理大型表或复杂查询时，如果不进行优化，性能可能会成为瓶颈。主要原因在于，默认情况下，MySQL 需要读取整个 JSON 文档并解析它，才能提取所需路径的值。当 JSON_EXTRACT 出现在 WHERE 子句或 ORDER BY 子句中，并且没有合适的索引时，会导致全表扫描，对每一行的 JSON 数据进行解析，效率极低。

以下是一些关键的性能优化策略：

1. 使用生成列 (Generated Columns) + 索引 (MySQL 5.7+)

这是最常用也是非常有效的优化手段。可以创建一个生成列，其值是 JSON_EXTRACT 从 JSON 列中提取的结果，然后在这个生成列上创建标准索引。

• VIRTUAL Generated Column: 值不实际存储，在读取时计算。可以索引。节省存储空间，但读取时有计算开销。适用于写操作频繁或存储空间有限的情况。
• STORED Generated Column: 值被计算并实际存储在表中，占用额外存储空间。读取速度快，因为值是预先计算好的。适用于读操作远多于写操作的情况。

示例：为品牌 (brand) 创建 VIRTUAL 生成列并索引

sql
ALTER TABLE products
ADD COLUMN brand_virtual VARCHAR(100) AS (details ->> '$.brand') VIRTUAL,
ADD INDEX idx_brand_virtual (brand_virtual);

示例：为 RAM 大小创建 STORED 生成列并索引

sql
ALTER TABLE products
ADD COLUMN ram_stored INT AS (details ->> '$.specs.ram') STORED,
ADD INDEX idx_ram_stored (ram_stored);

查询优化:

现在，当你在 WHERE 子句中使用这些生成列时，MySQL 可以利用对应的索引，极大地提高查询速度：

“`sql
— 这个查询现在会使用 idx_brand_virtual 索引
SELECT name
FROM products
WHERE brand_virtual = ‘TechCorp’;

— 这个查询现在会使用 idx_ram_stored 索引
SELECT name
FROM products
WHERE ram_stored > 8;
“`

注意事项:

• 生成列的表达式必须是确定性的。JSON_EXTRACT 通常是确定性的。
• ->> 操作符通常更适合生成列，因为它返回标准的 SQL 数据类型 (如 VARCHAR, INT)，便于创建索引和进行比较。如果使用 ->，返回的是 JSON 类型，可能无法直接创建 B-Tree 索引或需要特殊处理。
• 选择 VIRTUAL 还是 STORED 取决于具体的读写负载和存储限制。对于经常用于过滤或排序的字段，STORED 可能提供更好的读取性能。

2. 使用函数索引 (Functional Indexes) (MySQL 8.0.13+)

MySQL 8.0.13 版本引入了对函数索引的支持，这是一个更直接的优化方法，无需创建额外的生成列。可以直接在包含 JSON_EXTRACT（或其他函数）的表达式上创建索引。

示例：直接在 JSON_EXTRACT 表达式上创建索引

“`sql
— 为品牌创建函数索引 (使用 ->> 提取为 SQL 字符串)
CREATE INDEX idx_func_brand ON products ((details ->> ‘$.brand’));

— 为 RAM 大小创建函数索引 (需要 CAST 确保类型一致性，或者确保路径总是返回数字)
— 如果 JSON 中 ram 可能不存在或为 null，索引可能需要更复杂的定义
— 假设 ram 总是数字或 null:
CREATE INDEX idx_func_ram ON products ((CAST(details ->> ‘$.specs.ram’ AS UNSIGNED)));
— 或者，如果确定总是数字：
— CREATE INDEX idx_func_ram ON products ((details -> ‘$.specs.ram’)); — 索引 JSON 数字
“`

查询优化:

MySQL 查询优化器能够识别出 WHERE 子句中的表达式与函数索引匹配，并使用该索引：

“`sql
— 这个查询现在可以使用 idx_func_brand 索引
SELECT name
FROM products
WHERE details ->> ‘$.brand’ = ‘TechCorp’;

— 这个查询现在可以使用 idx_func_ram 索引
SELECT name
FROM products
WHERE CAST(details ->> ‘$.specs.ram’ AS UNSIGNED) > 8;
“`

优势:

• 语法更简洁，不需要修改表结构添加生成列。
• 管理更方便，索引直接关联表达式。

对比生成列和函数索引:

• 易用性: 函数索引通常更简单。
• 版本要求: 函数索引需要 MySQL 8.0.13+。生成列在 5.7+ 就可用。
• 性能: 两者都能显著提升查询性能。STORED 生成列在读取时可能略快于函数索引（因为它避免了运行时的计算），但会消耗更多存储。VIRTUAL 生成列和函数索引在计算开销上类似。

3. 优化 JSON 结构和路径

• 保持结构相对扁平: 过于深层的嵌套会使路径表达式更长，解析可能更耗时。如果性能是关键，考虑适当减少嵌套层级。
• 使用明确的路径: 避免使用过于宽泛的通配符（如 .*, [*]）进行过滤，除非确实需要。精确的路径通常效率更高。
• 路径存在性检查: MySQL 提供了 JSON_CONTAINS_PATH 函数来检查路径是否存在，有时可以结合使用来优化逻辑。

4. 查询层面的优化

• 仅提取所需数据: 不要在 SELECT 列表中提取不必要的 JSON 字段。
• 尽早过滤: 利用已建立的索引（基于生成列或函数索引）在 WHERE 子句中尽早过滤掉大部分不匹配的行，减少后续需要处理的 JSON 数据量。
• 谨慎使用 ORDER BY 和 GROUP BY: 如果必须对 JSON 提取的值进行排序或分组，确保有对应的索引支持，否则会导致非常慢的文件排序 (filesort)。记得对 ->> 提取的文本表示的数字进行 CAST。

5. 考虑数据模型的权衡

虽然 MySQL 的 JSON 功能很强大，但并非所有场景都适合使用 JSON。如果数据的结构相对固定，查询模式复杂，并且对性能要求极高，传统的关系型范式（将字段拆分到不同的列或表中）可能仍然是更好的选择。评估使用 JSON 的便利性与潜在性能开销之间的平衡。对于高度结构化的数据，范式化设计通常能提供最佳的查询性能和数据完整性。

6. 监控与分析

使用 EXPLAIN 命令分析查询计划，确认 MySQL 是否正确地使用了为 JSON 路径创建的索引。监控慢查询日志，找出性能瓶颈。根据实际的查询负载和数据分布来调整索引策略。

五、总结

MySQL 的 JSON_EXTRACT 函数及其相关的 -> 和 ->> 操作符，是与数据库中 JSON 数据交互的基础。它们提供了灵活的方式来查询和检索嵌套在 JSON 文档中的信息。理解其语法、路径表达式规则以及各种应用场景至关重要。

然而，性能是使用 JSON 函数时必须重点关注的问题。未经优化的 JSON_EXTRACT 查询可能会导致严重的性能下降。通过巧妙运用生成列加索引（MySQL 5.7+）或更现代的函数索引（MySQL 8.0.13+），可以有效地解决性能瓶颈，使得在 WHERE, ORDER BY, GROUP BY 子句中基于 JSON 内部数据的操作也能高效执行。

最终，选择合适的优化策略、设计合理的 JSON 结构，并在必要时权衡 JSON 与传统关系模型的利弊，将帮助您在 MySQL 中充分利用 JSON 数据类型的优势，构建高性能、高灵活性的应用程序。掌握 JSON_EXTRACT 及其优化技巧，无疑是现代 MySQL 开发者和 DBA 的一项重要技能。

---