异常教程

mysql json（长文讲解）

更新时间:

💡一则或许对你有用的小广告

欢迎加入小哈的星球 ，你将获得：专属的项目实战 / 1v1 提问 / Java 学习路线 / 学习打卡 / 每月赠书 / 社群讨论

截止目前， 星球 内专栏累计输出 90w+ 字，讲解图 3441+ 张，还在持续爆肝中.. 后续还会上新更多项目，目标是将 Java 领域典型的项目都整一波，如秒杀系统, 在线商城, IM 即时通讯，权限管理，Spring Cloud Alibaba 微服务等等，已有 3100+ 小伙伴加入学习 ，欢迎点击围观

前言

在现代 Web 开发中，JSON（JavaScript Object Notation）作为一种轻量级的数据交换格式，因其灵活的结构和易读性，被广泛应用于前后端交互、配置存储和复杂数据模型的场景。而 MySQL 从 5.7 版本开始引入对 JSON 数据类型的支持，让开发者可以直接在数据库中存储和操作 JSON 格式的数据，极大提升了处理半结构化数据的效率。

本文将从零开始，系统讲解 MySQL JSON 的核心概念、操作方法及实际应用场景。无论是编程初学者还是有一定经验的开发者，都能通过本文掌握如何在 MySQL 中高效利用 JSON 数据类型，并解决实际开发中的常见问题。

JSON 数据类型的存储与基本操作

1. JSON 数据类型的创建与存储

在 MySQL 中，使用 JSON 数据类型可以声明一个字段来存储 JSON 格式的数据。其语法与普通字段定义类似：

CREATE TABLE users (  
    id INT PRIMARY KEY AUTO_INCREMENT,  
    profile JSON  
);

这里，profile 字段可以存储用户的各种信息，例如：

INSERT INTO users (profile)  
VALUES (  
    '{"name": "Alice", "age": 30, "hobbies": ["reading", "coding"]}'  
);

比喻说明：
可以将 JSON 字段想象成一个“多功能抽屉”，能够存放结构不固定但逻辑相关的数据。例如，用户的个人资料可能包含姓名、年龄、爱好，甚至动态扩展的字段（如地址、联系方式），而无需每次修改表结构。

2. JSON 的基本操作函数

MySQL 提供了丰富的内置函数来操作 JSON 数据。以下是一些常用函数：

函数功能描述示例代码
JSON_EXTRACT()提取 JSON 对象中的指定路径的值SELECT JSON_EXTRACT(profile, '$.name') FROM users;
JSON_SET()在 JSON 对象中设置或更新键值对UPDATE users SET profile = JSON_SET(profile, '$.age', 31);
JSON_REMOVE()从 JSON 对象中删除指定键UPDATE users SET profile = JSON_REMOVE(profile, '$.hobbies');
JSON_CONTAINS()检查 JSON 对象是否包含指定值或结构SELECT JSON_CONTAINS(profile, '"coding"', '$.hobbies');

操作示例：
假设用户 Alice 的 profile 字段包含以下数据：

{  
    "name": "Alice",  
    "age": 30,  
    "hobbies": ["reading", "coding"]  
}

  • 提取姓名：

    SELECT JSON_EXTRACT(profile, '$.name') AS user_name FROM users WHERE id = 1;  
-- 返回结果：{"name": "Alice"}

  • 更新年龄：

    UPDATE users SET profile = JSON_SET(profile, '$.age', 31) WHERE id = 1;

JSON 路径表达式与查询技巧

1. 路径表达式的语法

MySQL 使用类似 JSONPath 的语法来定位 JSON 对象中的具体字段。路径以 $ 开头，例如：

  • $.hobbies：直接访问 hobbies 数组
  • $.address.city：访问嵌套字段 address 对象中的 city
  • $.hobbies[0]：访问数组中的第一个元素

路径表达式示例：

-- 查询 hobby 数组中的第一个元素  
SELECT JSON_EXTRACT(profile, '$.hobbies[0]') FROM users WHERE id = 1;  
-- 返回结果：["reading"]

2. 条件查询与聚合函数

（1）基于 JSON 字段的条件查询

可以使用 ->> 运算符将 JSON 值转换为字符串后进行比较：

-- 查询 name 为 Alice 的用户  
SELECT * FROM users  
WHERE JSON_UNQUOTE(JSON_EXTRACT(profile, '$.name')) = 'Alice';  

-- 简化写法（MySQL 8.0+）  
SELECT * FROM users WHERE profile->>'$.name' = 'Alice';

（2）统计与聚合

通过结合 JSON_EXTRACT 和聚合函数，可以实现对 JSON 字段的统计：

-- 统计所有用户的平均年龄  
SELECT AVG(CAST(JSON_EXTRACT(profile, '$.age') AS UNSIGNED)) AS avg_age  
FROM users;

高级用法与性能优化

1. 复杂 JSON 结构的处理

（1）嵌套对象的提取

假设 profile 包含嵌套的 address 对象：

{  
    "name": "Bob",  
    "address": {  
        "city": "New York",  
        "zip": "10001"  
    }  
}

可以通过多级路径提取 city：

SELECT profile->>'$.address.city' AS city FROM users WHERE id = 2;  
-- 返回结果：New York

（2）数组元素的过滤

若需查询 hobby 包含 "coding" 的用户，可以使用 JSON_CONTAINS()：

SELECT * FROM users  
WHERE JSON_CONTAINS(profile->>'$.hobbies', '"coding"', '$');

2. 性能优化策略

（1）创建虚拟列与索引

对频繁查询的 JSON 字段，可以创建虚拟列并添加索引：

ALTER TABLE users  
ADD COLUMN user_name VARCHAR(255)  
    AS (JSON_UNQUOTE(JSON_EXTRACT(profile, '$.name')))  
    VIRTUAL;  

CREATE INDEX idx_user_name ON users(user_name);

（2）避免全表扫描

对于复杂查询，优先使用 FORCE INDEX 或优化条件表达式，例如：

SELECT * FROM users  
FORCE INDEX (idx_user_name)  
WHERE user_name = 'Alice';

实际案例：订单系统的 JSON 存储

1. 场景描述

某电商系统需要存储订单的扩展信息（如优惠券、物流详情），这些信息结构复杂且可能动态变化。使用 JSON 字段可以避免频繁修改表结构。

2. 表结构设计

CREATE TABLE orders (  
    order_id VARCHAR(50) PRIMARY KEY,  
    customer_info JSON,  
    products JSON,  
    delivery JSON  
);

3. 数据操作示例

  • 插入订单：

    INSERT INTO orders (order_id, customer_info, products, delivery)  
VALUES (  
  'ORD123',  
  '{"name": "John", "email": "john@example.com"}',  
  '[{"product_id": "P1", "quantity": 2}]',  
  '{"carrier": "UPS", "tracking_number": "XYZ789"}'  
);

  • 查询物流信息：

    SELECT delivery->>'$.carrier' AS carrier  
FROM orders  
WHERE order_id = 'ORD123';  
-- 返回结果：UPS

结论

MySQL JSON 数据类型为灵活存储和操作半结构化数据提供了强大的支持。通过掌握基本操作函数、路径表达式及性能优化技巧，开发者可以高效地应对复杂数据模型的挑战。无论是用户配置、订单扩展信息还是动态表单数据，JSON 字段都能显著提升开发效率。

在实际应用中，建议结合虚拟列和索引优化查询性能，并合理设计数据结构以平衡灵活性与查询效率。未来，随着 JSON 功能的持续完善，这一特性必将在更多场景中发挥重要作用。

（字数统计：约 1850 字）

最新发布

查看更多文章