MySQL 教程

MySQL JSON类型

🎉摘要：本文详细介绍如何在 MySQL 中操作 JSON 数据类型，涵盖从 MySQL 5.7.8 开始的原生支持及 8.0 的增强功能。内容包括创建 JSON 字段、插入数据、使用 -> 和 JSON_EXTRACT 提取值、条件查询、通过 JSON_SET 和 JSON_REMOVE 修改数据、添加新键、合并 JSON 以及创建虚拟列和函数索引。同时提供适合与不适合使用 JSON 类型的场景分析。

从 MySQL 5.7.8 开始支持原生的 JSON 数据类型，MySQL 8.0 版本对此做了很多增强。下面将介绍如何在 MySQL 中操作 JSON 数据类型：

基本使用

下面通过示例演示如何使用 JSON 数据类型，例如：

-- 创建数据表
-- 该表的 settings 字段采用 JSON 数据类型，保存用户的设置
mysql> CREATE TABLE json_demo (
    ->     id INT AUTO_INCREMENT PRIMARY KEY,
    ->     user_id INT NOT NULL,
    ->     settings JSON COMMENT '用户设置'
    -> );
Query OK, 0 rows affected (0.01 sec)

-- 插入JSON数据
-- 指定 JSON 数据类型字段的值时直接使用合法的JSON字符串即可
mysql> INSERT INTO json_demo (user_id, settings) VALUES
    -> (1, '{"theme": "dark", "language": "zh-CN", "notifications": true}'),
    -> (2, '{"theme": "light", "language": "en-US", "fontSize": 14}');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

-- 查询JSON字段
mysql> SELECT * FROM json_demo;
+----+---------+---------------------------------------------------------------+
| id | user_id | settings                                                      |
+----+---------+---------------------------------------------------------------+
|  1 |       1 | {"theme": "dark", "language": "zh-CN", "notifications": true} |
|  2 |       2 | {"theme": "light", "fontSize": 14, "language": "en-US"}       |
+----+---------+---------------------------------------------------------------+
2 rows in set (0.00 sec)

通过上面示例我们了解 JSON 数据类型最基本的用法，从查询出来的结果可以看出 JSON 类型存的是一个合法的JSON字符串，下面将介绍如何通过 MySQL 内置的函数如何提取JSON数据中的某个单一的值，或者修改某个JSON字段的值。

JSON操作函数

提取JSON值

使用“->”或者“->>”语法提取JSON类型字段中某个JSON字段的值，例如：

mysql> SELECT user_id,
    ->     JSON_EXTRACT(settings, '$.theme') AS theme,
    ->     settings->'$.language' AS language,  -- 简写形式
    ->     settings->>'$.notifications' AS notifications  -- 去除引号
    -> FROM json_demo;
+---------+---------+----------+---------------+
| user_id | theme   | language | notifications |
+---------+---------+----------+---------------+
|       1 | "dark"  | "zh-CN"  | true          |
|       2 | "light" | "en-US"  | NULL          |
+---------+---------+----------+---------------+
2 rows in set (0.00 sec)

上面示例，分别提取了 JSON 中 language 和 notifications 字段的值，其中第二行数据 notifications 没有该字段，因此值为 NULL。

条件查询

除了可以单独提取JSON中某个字段的值外，还可以将JSON中某个字段的值用作查询条件。例如：

-- 查询 JSON 中 theme 主题为 dark 的记录
mysql> SELECT * FROM json_demo WHERE settings->'$.theme' = 'dark';
+----+---------+---------------------------------------------------------------+
| id | user_id | settings                                                      |
+----+---------+---------------------------------------------------------------+
|  1 |       1 | {"theme": "dark", "language": "zh-CN", "notifications": true} |
+----+---------+---------------------------------------------------------------+
1 row in set (0.00 sec)

-- 查询JSON 字段 settings 中 fontSize 数值大于 12 的所有数据
mysql> SELECT * FROM json_demo WHERE JSON_EXTRACT(settings, '$.fontSize') > 12;
+----+---------+---------------------------------------------------------+
| id | user_id | settings                                                |
+----+---------+---------------------------------------------------------+
|  2 |       2 | {"theme": "light", "fontSize": 14, "language": "en-US"} |
+----+---------+---------------------------------------------------------+
1 row in set (0.00 sec)

注意，JSON_EXTRACT(settings, '$.fontSize') 是 MySQL 内置 JSON 提取函数。第一个参数为 JSON 字段。第二个参数 JSON 路径 '$.fontSize'，$ 代表整个 JSON 根节点，.fontSize 取根下名为 fontSize 的属性值。等价简写 settings->'$.fontSize'。

修改JSON字段

使用 JSON_SET() 来修改JSON，JSON_SET(json列, json路径, 新值) 是 MySQL JSON 内置函数。第 1 个参数是原始 JSON 字段 settings。第二个参数是 JSON 路径，例如 '$.theme'，$ 代表 JSON 根对象，.theme 指向根下 theme 键。第 3 个参数 'auto' 要写入的新值。

JSON_SET() 的逻辑如下：

  • 如果 JSON 里已有 theme：覆盖原有值；

  • 如果 JSON 里没有 theme：自动新增这个键值对；

JSON 中其他字段保持不变。例如：

-- 修改 user_id 为 1 的 settings 配置，将 theme 设置为 auto
mysql> UPDATE json_demo
    -> SET settings = JSON_SET(settings, '$.theme', 'auto')
    -> WHERE user_id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 查看修改后的数据
mysql> select * from json_demo;
+----+---------+---------------------------------------------------------------+
| id | user_id | settings                                                      |
+----+---------+---------------------------------------------------------------+
|  1 |       1 | {"theme": "auto", "language": "zh-CN", "notifications": true} |
|  2 |       2 | {"theme": "light", "fontSize": 14, "language": "en-US"}       |
+----+---------+---------------------------------------------------------------+
2 rows in set (0.00 sec)

删除JSON中的某个键

删除 JSON 数据中的键使用 JSON_REMOVE() 完成，注意，还是 UPDATE 语句，因为是删除 JSON 字段中的某个字段，对于整行数据来说，还是修改操作。注意，JSON_REMOVE(json字段, JSON路径) 是 MySQL JSON 内置删除函数。第一个参数是原始 JSON 数据 settings。第二个参数 '$.fontSize' 是 JSON 路径，代表根节点下的 fontSize 属性。

执行逻辑如下：

  • 如果 JSON 存在 fontSize 键：直接移除该键值对；

  • 如果不存在 fontSize：JSON 内容完全不变，无报错。

示例：

-- 删除 user_id 为 2 的数据中 settings 的 fontize 配置
mysql> UPDATE json_demo
    -> SET settings = JSON_REMOVE(settings, '$.fontSize')
    -> WHERE user_id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 查询数据验证
mysql> select * from json_demo where user_id=2;
+----+---------+-----------------------------------------+
| id | user_id | settings                                |
+----+---------+-----------------------------------------+
|  2 |       2 | {"theme": "light", "language": "en-US"} |
+----+---------+-----------------------------------------+
1 row in set (0.00 sec)

添加新键

使用 JSON_INSERT() 函数实现如果键不存在，则新增新健。JSON_INSERT(json字段, 路径, 值) 是 MySQL JSON 函数，第 1 参数是原始 JSON 数据 settings，第二个参数是路径，如 '$.sidebar' 表示JSON 根节点下的 sidebar 属性，第三个参数是要插入的值，如 true。

该函数的核心特性：只新增，不覆盖

  • JSON 没有 sidebar → 自动添加 sidebar: true

  • JSON 已有 sidebar → 完全不修改，保留原来的值

例如：

-- 修改 json_demo 表中 user_id = 1 的记录，
-- 仅当 JSON 不存在sidebar字段时，才新增 "sidebar":true；
-- 若已有该字段，原有值保持不变，不会覆盖。
mysql> UPDATE json_demo
    -> SET settings = JSON_INSERT(settings, '$.sidebar', true)
    -> WHERE user_id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from json_demo where user_id=1;
+----+---------+--------------------------------------------------------------------------------+
| id | user_id | settings                                                                       |
+----+---------+--------------------------------------------------------------------------------+
|  1 |       1 | {"theme": "auto", "sidebar": true, "language": "zh-CN", "notifications": true} |
+----+---------+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

合并JSON

使用 JSON_MERGE_PATCH() 函数实现合并 JSON，如果 JSON 中存在某个键则替换，不存在则添加。

函数 JSON_MERGE_PATCH(json1, json2) 核心规则

  • 同键名：后传入的 JSON2 覆盖 JSON1 的值；

  • JSON2 中值为 null 的键：会直接删除原 JSON 对应字段；

  • 不存在的新键：直接追加；

  • 只适用于 JSON 对象合并，数组合并逻辑和 JSON_MERGE_PRESERVE 完全不同。

例如：

-- 更新 json_demo 表 user_id=1 的记录，使用 JSON_MERGE_PATCH 合并两段 JSON 对象，
-- 把 {"beta": true} 里的键合并到原有 settings 中。
mysql> UPDATE json_demo
    -> SET settings = JSON_MERGE_PATCH(settings, '{"beta": true}')
    -> WHERE user_id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from json_demo where user_id=1;
+----+---------+----------------------------------------------------------------------------------------------+
| id | user_id | settings                                                                                     |
+----+---------+----------------------------------------------------------------------------------------------+
|  1 |       1 | {"beta": true, "theme": "auto", "sidebar": true, "language": "zh-CN", "notifications": true} |
+----+---------+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

JSON索引

MySQL 8.0 支持对 JSON 字段创建函数索引：

-- 创建虚拟列并建立索引
-- 给 json_demo 表新增虚拟计算列，从 JSON 字段 settings 自动提取 theme 值，
-- 再基于该虚拟列建立普通索引，实现 JSON 键高效查询。
mysql> ALTER TABLE json_demo
    -> ADD COLUMN theme VARCHAR(20) AS (settings->>'$.theme') VIRTUAL,
    -> ADD INDEX idx_theme (theme);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 或者直接创建函数索引（MySQL 8.0.13+）
-- 创建失败了
mysql> ALTER TABLE json_demo
    -> ADD INDEX idx_language ((settings->>'$.language'));
ERROR 3757 (HY000): Cannot create a functional index on an expression that returns a BLOB or TEXT. Please consider using CAST.

出错原因，settings->>'$.language' 提取 JSON 字段值后，返回的数据类型是 TEXT / 字符串大文本类型，MySQL 不允许直接基于 TEXT/BLOB 类型创建函数索引，所以抛出 ERROR 3757。

  • ->> 运算符等价 JSON_UNQUOTE(JSON_EXTRACT())，输出无引号字符串，默认类型为 TEXT；

  • 函数索引要求表达式结果必须是定长 / 短字符串、数字等可索引类型，不能是 TEXT。

修复方案：使用 CAST 强转固定长度字符串，把提取出的值强转为 CHAR(M) / VARCHAR(M)，限定长度，消除 TEXT 类型，例如：

-- 创建成功
mysql> ALTER TABLE json_demo
    -> ADD INDEX idx_language ( (CAST(settings->>'$.language' AS CHAR(20))) );
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

何时使用JSON类型

适合使用JSON的场景：

  • 配置信息、用户偏好设置

  • 日志数据、事件追踪信息

  • 数据模式经常变化的字段

  • 需要存储第三方API的原始响应

不适合使用JSON的场景：

  • 需要频繁作为查询条件的字段（索引支持有限）

  • 需要复杂关联查询的数据

  • 高度结构化的核心业务数据

 更多知识请阅读后续章节……谢谢！！！

  

上一章：MySQL 日期时间类型
说说我的看法
全部评论（）
没有评论
关于
本网站专注于 Java、数据库（MySQL、Oracle）、Linux、软件架构及大数据等多领域技术知识分享。涵盖丰富的原创与精选技术文章，助力技术传播与交流。无论是技术新手渴望入门，还是资深开发者寻求进阶，这里都能为您提供深度见解与实用经验，让复杂编码变得轻松易懂，携手共赴技术提升新高度。如有侵权，请来信告知：hxstrive@outlook.com
链接
其他应用
公众号
©2018-2020 hxstrive.com 版权所有 ICP证：蜀ICP备14031461号-2   川公网安备51010802032098