mysqljson怎么用

从入门到精通：MySQL 中 JSON 字段的实用指南

在现代 Web 开发和应用程序设计中，JSON（JavaScript Object Notation）已成为数据交换的事实标准，它灵活、轻量且易于人阅读和编写，为了更好地适应这种趋势，关系型数据库 MySQL 从 5.7 版本开始原生支持 JSON 数据类型，允许开发者直接在数据库中高效地存储和查询 JSON 文档。

如果你还在纠结 MySQL JSON 字段怎么用，或者想了解其强大功能，那么这篇文章就是为你准备的，我们将从基本操作到高级查询，全面解析 MySQL 中 JSON 字段的使用方法。

为什么要在 MySQL 中使用 JSON？

在讨论“怎么用”之前，我们先明白“为什么用”，在 MySQL 中使用 JSON 字段主要有以下优势：

1. 灵活性与模式演进：传统的关系型数据库要求表结构（Schema）是固定的，当应用需求变化时，修改表结构可能非常耗时，JSON 字段允许你存储结构不固定或可能频繁变化的数据，无需频繁修改表结构。
2. 简化应用层逻辑：某些场景下，数据本身就是一个复杂的对象（用户配置、商品的多维属性），将整个对象存储为一个 JSON 字段，可以避免在多个表中拆分和关联，简化应用代码。
3. 高性能的文档存储：对于“读多写少”且数据结构嵌套复杂的场景，使用 JSON 字段可以避免昂贵的多表 JOIN 操作，提高查询效率。

JSON 字段的创建与插入

创建带有 JSON 字段的表

使用 JSON 数据类型来定义一个列即可。

CREATE TABLE `products` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `attributes` JSON -- 定义一个名为 attributes 的 JSON 字段
);

插入 JSON 数据

插入 JSON 数据非常直观，你可以直接插入一个合法的 JSON 字符串。

-- 插入一个标准的 JSON 对象
INSERT INTO `products` (`name`, `attributes`)
VALUES ('智能手机', '{"color": "深空灰", "storage": "256GB", "network": ["5G", "4G"]}');

为了方便,MySQL 提供了 JSON_ARRAY() 和 JSON_OBJECT() 函数来构建 JSON 数据。

-- 使用 JSON_OBJECT 和 JSON_ARRAY 函数
INSERT INTO `products` (`name`, `attributes`)
VALUES ('笔记本电脑', JSON_OBJECT(
    'cpu': 'Intel i7',
    'memory': '16GB',
    'ports': JSON_ARRAY('USB-C', 'HDMI', 'USB-A')
));

重要提示：MySQL 要求插入的 JSON 字符串必须是合法且格式正确的，如果格式错误，数据库会拒绝插入并报错。

查询与提取 JSON 数据

这是 JSON 字段最强大的部分，MySQL 提供了一套丰富的函数来从 JSON 文档中精确地提取你需要的值。

使用 -> 和 ->> 操作符

这是最常用、最简洁的两种操作符：

• ->：提取 JSON 子元素，并以 JSON 类型（JSON）返回结果，如果结果是标量值（如字符串、数字），它会返回一个包含该值的 JSON 数组或对象。
• ->>：提取 JSON 子元素，并以原生 MySQL 数据类型（如 VARCHAR, INT）返回结果，通常我们更常用这个。

示例：

假设我们有一张 users 表：

CREATE TABLE `users` (
  `id` INT PRIMARY KEY,
  `name` VARCHAR(100),
  `profile` JSON
);
INSERT INTO `users` VALUES (1, '张三', '{"age": 30, "city": "北京", "skills": ["Java", "Python"]}');

现在进行查询：

-- 1. 提取单个属性（使用 ->> 获取原生类型）
SELECT name, profile->>'$.age' AS age, profile->>'$.city' AS city
FROM users
WHERE id = 1;
-- 结果: name='张三', age='30', city='北京'
-- 2. 提取 JSON 数组中的一个元素
SELECT name, profile->'$.skills' AS skills_json, profile->>'$.skills[0]' AS first_skill
FROM users
WHERE id = 1;
-- 结果: skills_json='["Java", "Python"]', first_skill='Java'
-- 3. 在 WHERE 条件中使用
-- 查找所有年龄大于 25 的用户
SELECT * FROM users WHERE profile->>'$.age' > 25;
-- 查找居住在北京的用户
SELECT * FROM users WHERE profile->>'$.city' = '北京';

路径说明： 符号代表 JSON 文档的根。$.age 表示“根对象下的 age 键”，$.skills[0] 表示“根对象下 skills 数组的第一个元素”。

使用 JSON 函数

除了操作符,还有许多函数提供更复杂的功能：

• JSON_EXTRACT(json_doc, path)：功能与 -> 相同，提取 JSON 路径对应的值。
• JSON_UNQUOTE(json_val)：去除 JSON 值两侧的引号，将其转换为字符串，通常与 -> 结合使用，以达到 ->> 的效果。

  SELECT JSON_UNQUOTE(profile->'$.city') AS city FROM users WHERE id = 1;

• JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path ...])：检查 JSON 文档中是否存在指定的路径。

  -- 检查用户是否有 'city' 这个属性
  SELECT name FROM users WHERE JSON_CONTAINS_PATH(profile, 'one', '$.city');

• JSON_CONTAINS(json_doc, val[, path])：检查 JSON 文档中是否包含指定的值。

  -- 查找技能包含 'Python' 的用户
  SELECT name FROM users WHERE JSON_CONTAINS(profile->'$.skills', '"Python"');

• JSON_SEARCH(json_doc, one_or_all, search_str)：在 JSON 文档中搜索指定的字符串，并返回其路径。

  -- 在所有用户的 profile 中搜索 '北京'
  SELECT id, name, JSON_SEARCH(profile, 'one', '北京') AS found_path
  FROM users;

修改 JSON 数据

MySQL 提供了 JSON_SET(), JSON_INSERT(), JSON_REPLACE() 等函数来修改 JSON 文档中的数据。

• JSON_SET(json_doc, path, val[, path, val ...])：设置路径下的值，如果路径存在，则替换；如果不存在，则添加。
• JSON_INSERT(json_doc, path, val[, path, val ...])：插入值。仅当路径不存在时才插入。
• JSON_REPLACE(json_doc, path, val[, path, val ...])：替换值。仅当路径存在时才替换。

示例：

-- 假设 user_id=1 的 profile 为 '{"age": 30, "city": "北京"}'
-- 1. 使用 JSON_SET 添加一个新属性 'gender'，并修改 'city'
UPDATE users
SET profile = JSON_SET(profile, '$.gender', '男', '$.city', '上海')
WHERE id = 1;
-- profile 变为: '{"age": 30, "city": "上海", "gender": "男"}'
-- 2. 使用 JSON_INSERT 尝试添加已存在的 'age'，它不会生效
UPDATE users
SET profile = JSON_INSERT(profile, '$.age', 31)
WHERE id = 1;
-- profile 依然是: '{"age": 30, "city": "上海", "gender": "男"}'
-- 3. 使用 JSON_REPLACE 修改 'age'
UPDATE users
SET profile = JSON_REPLACE(profile, '$.age', 31)
WHERE id = 1;
-- profile 变为: '{"age": 31, "city": "上海", "gender": "男"}'

性能考量与最佳实践

JSON 字段非常灵活，但滥用会影响性能，请遵循以下建议：

1. 不要过度使用：JSON 字段适用于结构不固定或嵌套层级深的“半结构化”数据，对于结构固定、需要频繁查询和排序的字段（如用户名、邮箱），仍然使用传统的列类型（VARCHAR, INT）性能更佳。
2. 避免全表扫描：在 WHERE 子句中对 JSON 字段进行函数操作（如 WHERE JSON_EXTRACT(profile, '$.age') > 30）会导致索引失效，最佳实践是使用 ->> 操作符，它能让 MySQL 更好地利用索引。

   -- 推荐，可以利用索引（如果对 age 建立了生成列索引）
   WHERE profile->>'$.age