MySQL JSON 数据类型操作指南

原创

Jimaks 2025-05-06 08:11:19 博主文章分类：数据库 ©著作权

文章标签 JSON 字段数据 文章分类 办公效率 yyds干货盘点

©著作权归作者所有：来自51CTO博客作者Jimaks的原创作品，请联系作者获取转载授权，否则将追究法律责任

一、问题：传统关系型数据存储的局限性

在传统的数据库设计中，我们通常将数据以结构化的方式存储到表中。然而，随着业务复杂度的增加，很多场景下需要处理半结构化或非结构化的数据。例如：

用户自定义字段（如用户设置、动态表单）；
日志类信息（如事件上下文）；
嵌套对象或数组的数据。

如果强行将这些数据映射为多个列或关联表，会导致：

表结构臃肿，难以维护；
查询效率低下；
扩展性差，新增字段需要频繁修改表结构。

技术痛点：如何在MySQL中高效地存储和查询这类灵活结构的数据？

二、方案：使用MySQL的JSON数据类型

从MySQL 5.7开始，官方引入了原生的JSON数据类型，支持存储格式正确的JSON文档，并提供了一系列函数用于查询和更新JSON中的内容。

1. 存储结构化与非结构化混合数据

我们可以将部分固定字段作为普通列，而将变化较多的部分以JSON格式存储在一个字段中。

示例：用户配置表

CREATE TABLE user_settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    settings JSON
);

插入一条记录：

INSERT INTO user_settings (username, settings)
VALUES ('alice', '{"theme": "dark", "notifications": {"email": true, "sms": false}, "preferences": ["sports", "technology"]}');

2. 查询JSON字段中的内容

MySQL提供了-> 和 ->> 操作符用于提取JSON字段值。

-> 返回JSON格式的结果；
->> 返回去引号后的字符串结果。

示例：查找用户是否开启邮件通知

sql
SELECT username, settings->'$.notifications.email' AS email_notify
FROM user_settings
WHERE JSON_UNQUOTE(settings->'$.notifications.email') = 'true';

或者更简洁地使用JSON_EXTRACT：

SELECT username
FROM user_settings
WHERE JSON_EXTRACT(settings, '$.notifications.email') = true;

3. 更新JSON字段中的部分内容

使用JSON_SET、JSON_REPLACE、JSON_REMOVE等函数可以精确修改JSON内部结构。

示例：修改用户的主题偏好

UPDATE user_settings
SET settings = JSON_SET(settings, '$.theme', 'light')
WHERE username = 'alice';

三、效果：提升灵活性与可维护性

通过引入JSON数据类型，我们能够实现以下目标：

✅ 1. 结构灵活扩展

无需每次新增字段都修改表结构，适合快速迭代的业务场景。

✅ 2. 查询性能优化

MySQL对JSON字段建立了虚拟列索引支持，可以通过创建生成列来加速查询。

创建索引示例：

ALTER TABLE user_settings
ADD COLUMN theme VARCHAR(20) GENERATED ALWAYS AS (JSON_UNQUOTE(settings->'$.theme')) STORED;

CREATE INDEX idx_theme ON user_settings(theme);