MySQL 如何存储和查询 JSON 数据：从基础用法到性能优化

许南星

发布时间：2026-10-15 | 4人浏览过

原创

本文将深入解析 MySQL 中 JSON 数据的存储与高效查询技巧。涵盖 JSON 类型的基础操作、字段提取函数 JSON_UNQUOTE 与箭头操作符的用法，以及如何通过创建生成列（Generated Column）为 JSON 子字段建立索引来提升查询速度。针对包含数组标签的用户画像场景，还将介绍 MySQL 8.0.17+ 的多值索引特性。

为什么选择 JSON 格式存储动态数据

自 MySQL 5.7 版本起，数据库原生提供了 JSON 数据类型。相较于传统的字符串存储方式，JSON 类型的数据不再以纯文本形式保存，而是采用内部二进制格式。这种设计带来了三大优势：

快速访问：无需解析整个大字符串即可直接提取内部元素，提升读取效率。
自动验证：在插入或更新时，系统会自动校验 JSON 格式合法性，错误数据将抛出异常。
灵活性高：无需预先定义所有列，适合存储结构多变的数据，如用户登录方式、个性化标签等。

适用场景提示：JSON 更适合存储在业务中经常变动且无严格关联性的静态数据（如配置项、非结构化属性），不建议将明显的强关系型数据（如姓名、身份证号、余额）强行放入 JSON 中以规避 Schema 变更。

基础使用：创建表结构与插入数据

假设我们需要创建一个用户表，其中 login_info 字段用于存储多样化的登录联系方式（微信、手机号、QQ 等）。以下是一个典型的建表示例：

CREATE TABLE `t_user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户名',
  `age` int DEFAULT NULL COMMENT '年龄',
  `login_info` json DEFAULT NULL COMMENT '登录信息(JSON)',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

第1步：准备并插入测试数据

在表中插入不同组合的登录信息，展示 JSON 对象格式的灵活性：

INSERT INTO t_user VALUES (1,'tom', 25, '{"email": "1324@qq.com", "phone": "13200001111", "wechat": "147258369"}');
INSERT INTO t_user VALUES (2,'jack', 30, '{"phone": "13500001111"}');
INSERT INTO t_user VALUES (3,'lily', 18, '{"qq": "147258369", "phone": "13600001111"}');

核心操作：JSON 字段数据的查询方法

在业务开发中，最常见的动作是从 JSON 字段中提取特定值。以下是两种常用的提取方式：

第2步：使用标准函数提取单值

若需提取指定路径的值，可使用 JSON_EXTRACT。由于该函数返回结果通常带有引号（例如 "13200001111"），建议配合 JSON_UNQUOTE 去除引号。

SELECT name,
       JSON_EXTRACT(login_info, '$.phone') AS phone_raw,
       JSON_UNQUOTE(JSON_EXTRACT(login_info, '$.wechat')) AS wechat_clean
FROM t_user;

为了简化代码，MySQL 提供了更简洁的操作符写法，功能完全一致：

SELECT name,
       login_info ->> '$.phone' AS phone,
       login_info ->> '$.wechat' AS wechat
FROM t_user;

此处 ->> 表达式等同于 JSON_UNQUOTE(JSON_EXTRACT(...))。

第3步：基于 JSON 子字段的条件过滤

当需要根据 JSON 内部的值进行筛选时，直接将该表达式写在 WHERE 子句中即可：

SELECT name, login_info ->> '$.phone' AS phone
FROM t_user
WHERE login_info ->> '$.phone' = '13200001111';

进阶技巧：如何为 JSON 内容添加索引提升性能

JSON 数据通常不支持直接对内部子字段建立普通索引，这会导致全表扫描，严重影响大数据量下的查询性能。要实现高效检索，有两种策略：

第4步：利用“生成列 + 普通索引”优化对象查询

如果我们要频繁根据手机号查找用户，可以通过创建生成的虚拟列（Generated Column）来解决。步骤如下：

定义虚拟列：在现有 JSON 字段上计算出一个物理可见的列。
建立索引：对该虚拟列创建唯一索引或普通索引。

-- 1. 添加虚拟列
ALTER TABLE t_user 
ADD COLUMN phone varchar(11) AS (login_info ->> '$.phone');

-- 2. 在虚拟列上创建索引
ALTER TABLE t_user 
ADD UNIQUE INDEX idx_uq_phone(phone);

完成上述操作后，查询优化器能够识别并使用新创建的索引。我们可以使用 EXPLAIN 验证执行计划是否已命中索引：

EXPLAIN SELECT * FROM t_user WHERE phone = '13200001111';

查看表结构确认索引已经成功挂载：

高级应用：用户画像中的 JSON 数组与多值索引

在电商、音乐等推荐系统中，常需对用户打标签（Tagging）。例如，记录一个用户的标签ID列表 [8, 10, 12]。在这种场景下，传统的“虚拟列+索引”方法难以应对，因此推荐使用 MySQL 8.0.17 版本引入的多值索引（Multi-Valued Indexes）。

第5步：构建用户画像数据模型

首先建立标签定义表和中间表。中间表使用 JSON 数组来承载多个标签 ID：

-- 标签字典表
CREATE TABLE `t_tag` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tag_name` varchar(255) DEFAULT NULL COMMENT '标签名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO t_tag VALUES 
(NULL, '70后'), (NULL, '80后'), (NULL, '爱运动'), (NULL, '爱听歌'), 
(NULL, '爱看电影'), (NULL, '小资'), (NULL, '有车'), (NULL, '有小孩');

创建用户标签中间表，注意 tag_id 类型为 json，存储数组格式：

CREATE TABLE `t_user_tag` (
  `user_id` int NOT NULL COMMENT '用户ID',
  `tag_id` json NOT NULL COMMENT '标签ID列表',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入示例数据（对应上面插入的标签ID）：

INSERT INTO t_user_tag VALUES (1, '[2, 4, 6]'); -- 80后, 爱听歌, 有车
INSERT INTO t_user_tag VALUES (2, '[1, 3, 7]'); -- 70后, 爱运动, 有小孩
INSERT INTO t_user_tag VALUES (3, '[8, 10, 12]'); -- 爱看电影, 小资, 喜欢网购

第6步：创建并使用多值索引

要在 JSON 数组上实现高效检索，必须显式创建多值索引。注意语法要求将 JSON 元素转换为无符号整数数组：

ALTER TABLE t_user_tag
ADD INDEX idx_user_tags ((CAST((tag_id ->"$") AS UNSIGNED ARRAY)));

检查索引状态：

第7步：执行多维度的标签查询

场景一：精准查询拥有特定标签的用户
使用 MEMBER OF 运算符判断某个值是否存在于数组中：

-- 查询“爱看电影”(ID为8)的用户
SELECT * FROM t_user_tag
WHERE 8 MEMBER OF(tag_id -> '$');

场景二：组合查询拥有多个标签的用户
使用 JSON_CONTAINS 匹配数组中包含的一组特定值：

-- 查询既“爱看电影”(8)又“有小孩”(10)的用户
SELECT * FROM t_user_tag
WHERE JSON_CONTAINS(tag_id -> '$', '[8, 10]');

总结

在 MySQL 中处理 JSON 数据，核心在于权衡灵活性与查询性能。对于简单的键值对属性，推荐使用生成列（Generated Columns）建立虚拟列索引；对于复杂的动态标签或多值属性，强烈建议在 MySQL 8.0 环境下使用多值索引（Multi-Valued Indexes）。

需要注意的是，JSON 虽强大，但不应滥用。涉及高并发事务、极其频繁更新的金融类账务数据，仍应优先使用标准化的关系型字段以保证数据的一致性和完整性。

以上就是 MySQL 存储与查询 JSON 数据的详细内容，更多关于 MySQL 高效利用 JSON 类型的资料请关注本站其它相关文章！