深入解析 MySQL JSON 函数：高效处理复杂数据的实战指南

2026-02-10 04:38:38 0条评论 2次阅读 0人点赞

在 Web 应用开发的世界里，数据结构的变化速度往往超过了我们的预期。你是否也曾遇到过这样的场景：一张表需要存储不同属性的配置信息，或者用户的画像数据千差万别？传统的固定列数关系型数据库设计在这些情况下显得有些力不从心。为了解决这个问题，JSON（JavaScript Object Notation）因其极高的灵活性和可读性，成为了现代应用开发中数据交换和存储的首选格式之一。

为了让我们在数据库层面就能高效地管理和查询这些灵活的数据，MySQL 从 5.7 版本开始引入了对 JSON 数据类型的原生支持，并提供了一系列强大的内置函数。这意味着，我们不再需要将整个 JSON 字符串加载到应用程序中进行解析，而是可以直接在 SQL 语句中像操作普通列一样操作 JSON 数据内部的字段。

在本文中，我们将作为实战派开发者，深入探索 MySQL JSON 函数 的强大功能。我们将通过详细的解析和丰富的示例，学习如何创建、查询、修改和优化 JSON 数据，帮助你在实际项目中写出更高效、更优雅的 SQL 语句。

1 什么是 MySQL JSON 函数？
2 为什么我们应该在 MySQL 中使用 JSON？
3 MySQL JSON 函数核心语法与实战
4 常见陷阱与性能优化建议
5 总结

什么是 MySQL JSON 函数？

简单来说，MySQL JSON 函数是一套内置的工具集，它赋予了我们在数据库内部直接创建、解析、搜索和修改 JSON 文档的能力。不同于普通的文本类型存储，MySQL 的 JSON 类型会自动验证传入的文本是否是合法的 JSON 格式，并且提供了优化的二进制存储格式。

这些函数允许我们在 SQL 查询中通过“路径”表达式来定位 JSON 深层嵌套的数据。这就像是给我们的 SQL 查询装上了“导航仪”，无论数据埋藏得多深，我们都能精准地提取出来。这使得我们在处理半结构化数据时，既能享受到关系型数据库的事务和完整性约束，又能拥有 NoSQL 数据库般的灵活性。

为什么我们应该在 MySQL 中使用 JSON？

在深入语法之前，让我们先探讨一下为什么在 MySQL 中使用 JSON 是一个明智的选择。这不仅仅是为了赶时髦，而是它切实解决了许多开发痛点。

1. 极大的灵活性与可扩展性

传统的表结构设计要求我们在建表时就确定好所有的列。但是，随着业务的发展，我们可能需要为不同的用户添加不同的属性（例如，VIP 用户可能有“会员等级”，而普通用户没有）。如果频繁地执行 ALTER TABLE 来添加列，在数据量大时是非常昂贵的操作。

使用 JSON 字段，我们可以在一个字段中存储无限扩展的键值对。这意味着应用架构的变更不再总是伴随着数据库 schema 的变更，极大地提升了系统的迭代速度。当业务逻辑发生变化时，我们可以直接在 JSON 对象中添加新的键，而无需破坏现有的表结构。

2. 卓越的互操作性

JSON 已经成为了 Web 开发的通用语言。无论是后端的 Python、Java、PHP，还是前端的 JavaScript，处理 JSON 都像呼吸一样自然。当我们在 MySQL 中直接存储 JSON 时，数据的序列化和反序列化过程变得异常简单。它消除了应用程序与数据库之间的格式转换壁垒，让数据在不同系统、不同语言之间的流动变得顺滑无阻。

3. 原生支持带来的性能提升

你可能会想：“我直接存 TEXT 然后在代码里解析不行吗？” 当然可以，但效率很低。MySQL 对 JSON 类型的字段进行了底层优化。它采用了一种类似于 MongoDB 的二进制存储格式，使得读取 JSON 文档中的特定值比从纯文本中解析要快得多。此外，我们还可以对 JSON 字段中的特定键创建索引（通过虚拟列），这在之前的 TEXT 存储方式中是无法想象的。

4. 关系型与 NoSQL 的完美融合

MySQL 支持 JSON 标志着它向“混合型”数据库的转变。这意味着我们不需要为了存储一些非结构化日志或配置信息而引入一套全新的 NoSQL 数据库（如 MongoDB）。我们可以继续利用 MySQL 强大的事务支持（ACID）、成熟的管理工具和丰富的生态系统，同时兼顾了 NoSQL 的灵活性。这种“两全其美”的方案降低了架构的复杂度和运维成本。

MySQL JSON 函数核心语法与实战

接下来，让我们通过实际代码来看看这些函数是如何工作的。为了方便理解，我们将在示例中穿插详细的解释。

1. 创建 JSON 数据：JSONOBJECT 和 JSONARRAY

在 MySQL 中，我们有两种主要的方式来构造 JSON 数据：构建对象和构建数组。

#### JSON_OBJECT：构建键值对对象

JSON_OBJECT 函数接受一系列键值对，并返回一个 JSON 对象。这对于在 SQL 查询中动态生成复杂的嵌套结构非常有用。

示例代码：

-- 创建一个包含用户基本信息的 JSON 对象
SELECT JSON_OBJECT(
    ‘name‘, ‘Alice‘, 
    ‘age‘, 25, 
    ‘city‘, ‘Wonderland‘,
    ‘skills‘, JSON_ARRAY(‘Java‘, ‘MySQL‘, ‘Kubernetes‘) -- 嵌套一个数组
) AS user_profile;

输出结果：

{
    "name": "Alice",
    "age": 25,
    "city": "Wonderland",
    "skills": ["Java", "MySQL", "Kubernetes"]
}

实战见解： 注意我们在 INLINECODEd282db8d 内部嵌套使用了 INLINECODE4698882e。这种组合让我们能够构建出非常复杂的数据结构，直接映射到我们代码中的对象模型。

#### JSON_ARRAY：构建列表

JSON_ARRAY 接受一个值列表，并将它们打包成一个 JSON 数组。

示例代码：

-- 创建一个简单的 ID 列表
SELECT JSON_ARRAY(101, 102, 103) AS order_ids;

输出结果：

[101, 102, 103]

2. 搜索与提取：JSON_EXTRACT 与 "->" 操作符

仅仅存储数据是不够的，更重要的是取出来。MySQL 提供了路径表达式来实现这一点。路径表达式以 INLINECODE63edd5e7 开头，表示根节点，然后通过 INLINECODE8ba8613b 或 [] 来访问子节点。

#### JSON_EXTRACT：精准提取

这是最标准的提取函数。它接受 JSON 文档和路径作为参数。

示例代码：

-- 从 JSON 字符串中提取 age 字段
SELECT JSON_EXTRACT(
    ‘{"name": "Alice", "age": 25, "city": "Wonderland"}‘, 
    ‘$.age‘
) AS user_age;

输出结果：

深入讲解： 这里的 INLINECODE69922c5f 告诉 MySQL：“从根节点开始，找名为 INLINECODE87e9c8f0 的键”。如果路径不存在，它会返回 NULL。这种容错机制在处理非结构化数据时非常重要。

#### 简写操作符：column->path

为了让我们写 SQL 更轻松，MySQL 提供了两个简写操作符：INLINECODEd26113da 和 INLINECODEdd50fcba。

INLINECODE9b6ed195 等同于 INLINECODEb488b885，返回的是 JSON 对象（带引号的字符串）。
INLINECODE481e4d29 等同于 INLINECODEe661f3aa，它会去掉引号，返回纯粹的字符串。

示例代码：

-- 假设我们有一个表 users，其中 data 字段是 JSON 类型
-- SELECT data->‘$.name‘ AS name_json, data->>‘$.name‘ AS name_raw 
-- FROM users WHERE id = 1;

-- 模拟数据演示
SET @json_data = ‘{"name": "Bob", "email": "[email protected]"}‘;

-- 使用 -> 提取 (结果带引号: "Bob")
SELECT @json_data->‘$.name‘ AS extracted_with_quotes;

-- 使用 ->> 提取 (结果无引号: Bob)
SELECT @json_data->>‘$.name‘ AS extracted_clean_string;

3. 修改数据：JSONSET, JSONINSERT, JSON_REPLACE

修改已有的 JSON 数据是常见的操作，但根据需求不同（是想覆盖、只想插入还是只想替换），MySQL 为我们提供了三个非常细致的函数。

#### JSON_SET：设置或替换（最常用）

如果路径存在，它就替换值；如果路径不存在，它就添加值。这是一个“全能选手”。

示例代码：

-- 修改 age，并添加一个新的键 is_active
SELECT JSON_SET(
    ‘{"name": "Alice", "age": 25}‘, 
    ‘$.age‘, 26,           -- 路径存在：更新值
    ‘$.is_active‘, true    -- 路径不存在：插入值
) AS updated_doc;

输出结果：

{
    "name": "Alice",
    "age": 26,
    "is_active": true
}

#### JSON_INSERT：仅插入不覆盖

这个函数非常谨慎，只有当路径完全不存在时，它才会插入值。如果路径已经存在，它什么也不做，保留旧值。

场景： 比如我们要给用户添加“备注”，但不想覆盖已有的备注。
示例代码：

-- 尝试插入 age (已存在，忽略)，插入 phone (不存在，成功)
SELECT JSON_INSERT(
    ‘{"name": "Alice", "age": 25}‘, 
    ‘$.age‘, 30,    -- 不会被更新，依然保持 25
    ‘$.phone‘, ‘123-4567‘ -- 新增字段
) AS inserted_doc;

#### JSON_REPLACE：仅替换不插入

与 JSON_INSERT 相反，这个函数只修改已存在的路径。如果你给的路径不存在，它就会忽略。

场景： 批量更新用户配置，但只更新已有的项，不添加额外配置。
示例代码：

-- 只修改 age，不新增 city
SELECT JSON_REPLACE(
    ‘{"name": "Alice", "age": 25}‘, 
    ‘$.age‘, 26,      -- 更新
    ‘$.country‘, ‘CN‘ -- 不会新增这个字段
) AS replaced_doc;

4. 实用辅助函数

除了 CRUD，MySQL 还提供了许多便捷的辅助函数。

#### JSON_KEYS：获取所有键

当我们不知道 JSON 对象里有什么属性时，这个函数非常有用。

示例：

SELECT JSON_KEYS(‘{"a": 1, "b": 2, "c": 3}‘);
-- 结果: ["a", "b", "c"]

#### JSONARRAYAPPEND 和 JSONARRAYINSERT

这两个函数专门用于处理数组。

示例代码 (数组追加)：

-- 在 scores 数组的末尾追加 95
SELECT JSON_ARRAY_APPEND(
    ‘{"name": "Alice", "scores": [80, 85]}‘, 
    ‘$.scores‘, 95
) AS updated_scores;

5. 表操作实战：在真实表中使用 JSON

让我们把上述知识串联起来，看一个更贴近实际开发的例子。假设我们有一张 INLINECODE61500953 表，其中 INLINECODE780c4570 字段存储了各种产品的动态属性。

建表与初始化：

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2),
    -- 使用 JSON 类型存储动态属性
    attributes JSON,
    -- 创建一个虚拟列并建立索引，用于高性能查询颜色
    color VARCHAR(20) AS (JSON_UNQUOTE(attributes->‘$.color‘)) STORED,
    INDEX ((color))
);

-- 插入数据：不同的产品有不同的属性
INSERT INTO products (name, price, attributes) VALUES 
(‘Laptop‘, 999.99, JSON_OBJECT(‘color‘, ‘Silver‘, ‘cpu‘, ‘Intel i7‘, ‘ram‘, ‘16GB‘)),
(‘T-Shirt‘, 19.99, JSON_OBJECT(‘color‘, ‘Blue‘, ‘size‘, ‘M‘, ‘material‘, ‘Cotton‘));

查询场景 1：找出所有颜色为 Blue 的产品

现在，我们可以直接在 WHERE 子句中使用 JSON 路径表达式，或者利用我们创建的虚拟列 color。

-- 方法 A：直接使用路径表达式 (较灵活，但可能走全表扫描，除非有函数索引)
SELECT * FROM products 
WHERE attributes->‘$.color‘ = ‘Blue‘;

-- 方法 B：使用生成的虚拟列 (性能最佳，因为使用了索引)
SELECT * FROM products 
WHERE color = ‘Blue‘;

查询场景 2：更新特定产品的属性

假设我们要给 ID 为 1 的笔记本电脑增加一个新的属性 INLINECODEe6877902，并更新 INLINECODE182b3fea。

UPDATE products 
SET 
    price = 899.99,
    attributes = JSON_SET(attributes, ‘$.stock‘, 10)
WHERE id = 1;

常见陷阱与性能优化建议

虽然 JSON 很强大，但作为经验丰富的开发者，我们需要知道何时该用，何时不该用。

1. 不要滥用 JSON

如果数据的结构是固定的、所有行都有相同的字段（例如用户表的 ID、邮箱、密码），请使用传统的列。不要为了用 JSON 而用 JSON。传统列的查询速度、存储空间和约束检查都远优于 JSON。

2. 谨慎使用部分更新

在 MySQL 5.7+ 中，更新 JSON 列通常是原位更新的，效率很高。但如果你在应用程序中取出整个 JSON 字符串，修改后再写回去，那就变成了“全量更新”，这在并发高的情况下会导致锁竞争。尽量使用 JSON_SET 等 SQL 函数在数据库内部完成更新。

3. 必须建立索引

如果你经常需要查询 JSON 里面的某个字段（例如上面的 INLINECODEc8cc9f64），一定要记住使用“虚拟列 + 索引”的技巧。直接在 WHERE 子句中写 INLINECODE50e0bce3 在没有索引的情况下，数据库不得不进行全表扫描并逐行解析 JSON，这在数据量大时是性能杀手。

总结

MySQL 的 JSON 函数为我们提供了一个处理复杂数据的强大工具箱。通过 INLINECODEc4d99cf6 和 INLINECODEf3878c96，我们可以轻松构建数据；通过 INLINECODEcaf93f46 和简写操作符，我们可以精准地读取数据；而 INLINECODEeb5da0bc、INLINECODEa5a840ff 和 INLINECODE8f9c3ece 则让我们拥有了细粒度的数据修改能力。

最重要的，我们在技术选型时要保持清醒：JSON 是对关系型模型的补充，而不是替代。当你需要在保持事务完整性的同时存储灵活的配置、日志或多维属性时，MySQL 的 JSON 功能无疑是最佳选择。现在，你已经有足够的知识去优化你的数据库设计了，不妨尝试在下一个项目中合理运用这些技巧，看看它能为你节省多少代码量！

如果你对特定的函数细节或者更复杂的查询场景感兴趣，我们可以继续深入探讨。祝你编码愉快！

投稿给我们	如何建站？
vps是什么？	如何安装宝塔？
如何通过博客赚钱？	便宜wordpress托管方案
免费wordpress主题	这些都是免费方案

豆丁博客

深入解析 MySQL JSON 函数：高效处理复杂数据的实战指南

什么是 MySQL JSON 函数？

为什么我们应该在 MySQL 中使用 JSON？

1. 极大的灵活性与可扩展性

2. 卓越的互操作性

3. 原生支持带来的性能提升

4. 关系型与 NoSQL 的完美融合

MySQL JSON 函数核心语法与实战

1. 创建 JSON 数据：JSONOBJECT 和 JSONARRAY

2. 搜索与提取：JSON_EXTRACT 与 "->" 操作符

3. 修改数据：JSONSET, JSONINSERT, JSON_REPLACE

4. 实用辅助函数

5. 表操作实战：在真实表中使用 JSON

常见陷阱与性能优化建议

1. 不要滥用 JSON

2. 谨慎使用部分更新

3. 必须建立索引

总结

相关文章美国1G带宽/1T流量高速vps $17.99/年