MySQL 如何存储和查询 JSON 数据：从基础用法到性能优化
本文将深入解析 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 类型的资料请关注本站其它相关文章！
免责声明：本文内容仅供参考，不代表本站观点。转载或引用请注明出处。
