MySQL JSON类型全攻略：让数据库也能玩转“非结构化“数据

原创于 2025-07-03 08:21:34 发布 · 1k 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql #json

MySQL 专栏收录该内容

22 篇文章

订阅专栏

大家好，今天我们来聊聊MySQL中一个非常实用的功能——JSON数据类型。随着Web和移动应用的快速发展，传统的结构化数据存储方式有时难以满足灵活多变的需求，而MySQL从5.7.8版本开始引入的JSON类型，为我们提供了一种半结构化数据存储的完美解决方案。

🔍 为什么需要JSON类型？

在日常开发中，我们经常会遇到这样的情况：

产品属性千变万化，不同品类有完全不同规格参数
用户配置信息灵活多样，每个用户可能有不同设置项
系统需要存储一些不确定结构的日志或扩展数据

传统解决方案要么设计大量可能为空的字段，要么使用文本字段存储JSON字符串然后应用层解析。而MySQL的JSON类型提供了第三种更优雅的方式：

自动验证：确保存储的都是合法JSON文档
高效访问：专门的存储格式和访问方法
丰富操作：内置大量JSON处理函数

🛠️ JSON类型实战操作指南

1. 创建包含 JSON 列的表

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    details JSON,
    price DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. 插入 JSON 数据

-- 直接插入 JSON 字符串
INSERT INTO products (name, details, price) 
VALUES ('Laptop', '{"brand": "Dell", "specs": {"cpu": "i7", "ram": "16GB"}, "colors": ["black", "silver"]}', 1299.99);

-- 使用 JSON_OBJECT 函数构造 JSON
INSERT INTO products (name, details, price)
VALUES ('Smartphone', JSON_OBJECT("brand", "Apple", "model", "iPhone 13", "storage", "128GB"), 899.00);

-- 使用 JSON_ARRAY 函数构造 JSON 数组
INSERT INTO products (name, details, price)
VALUES ('Tablet', JSON_OBJECT("brand", "Samsung", "accessories", JSON_ARRAY("Pen", "Keyboard", "Case")), 499.99);

3. 查询 JSON 数据

-- 查询整个 JSON 列
SELECT name, details FROM products;

-- 使用 -> 操作符提取 JSON 对象的属性（返回 JSON 类型）
SELECT name, details->'$.brand' AS brand FROM products;

-- 使用 ->> 操作符提取 JSON 对象的属性（返回字符串类型）
SELECT name, details->>'$.brand' AS brand FROM products;

-- 提取嵌套属性
SELECT name, details->'$.specs.cpu' AS cpu FROM products;

-- 提取数组元素
SELECT name, details->'$.colors[0]' AS primary_color FROM products;

二、JSON 函数详解

1. 创建 JSON 的函数

JSON_OBJECT(): 创建 JSON 对象

SELECT JSON_OBJECT('name', 'John', 'age', 30, 'city', 'New York');

JSON_ARRAY(): 创建 JSON 数组

SELECT JSON_ARRAY(1, 'a', TRUE, NULL, JSON_OBJECT('key', 'value'));

JSON_MERGE_PRESERVE(): 合并多个 JSON 文档（保留所有值）

SELECT JSON_MERGE_PRESERVE('{"a":1,"b":2}', '{"a":3,"c":4}');
-- 结果: {"a": [1, 3], "b": 2, "c": 4}

2. 查询 JSON 的函数

JSON_EXTRACT(): 提取 JSON 文档中的值

SELECT JSON_EXTRACT(details, '$.brand') FROM products;

JSON_CONTAINS(): 检查 JSON 文档是否包含特定值

SELECT name FROM products WHERE JSON_CONTAINS(details, '"Dell"', '$.brand');

JSON_SEARCH(): 在 JSON 文档中查找值的路径

SELECT JSON_SEARCH(details, 'one', 'i7') FROM products;

3. 修改 JSON 的函数

JSON_SET(): 插入或更新值

UPDATE products 
SET details = JSON_SET(details, '$.warranty', '2 years', '$.specs.ssd', '512GB')
WHERE id = 1;

JSON_INSERT(): 只插入新值（不更新已有值）

UPDATE products
SET details = JSON_INSERT(details, '$.os', 'Windows 11', '$.specs.gpu', 'NVIDIA')
WHERE id = 1;

JSON_REPLACE(): 只替换已有值

UPDATE products
SET details = JSON_REPLACE(details, '$.price', 1199.99, '$.specs.ram', '32GB')
WHERE id = 1;

JSON_REMOVE(): 删除 JSON 文档中的数据

UPDATE products
SET details = JSON_REMOVE(details, '$.colors[1]')
WHERE id = 1;

4. 其他实用函数

JSON_TYPE(): 返回 JSON 值的类型

SELECT JSON_TYPE(details->'$.brand') FROM products;

JSON_LENGTH(): 返回 JSON 文档或数组的长度

SELECT JSON_LENGTH(details->'$.colors') FROM products;

JSON_KEYS(): 返回 JSON 对象的键

SELECT JSON_KEYS(details) FROM products;

🏆 三最佳实践与避坑指南

适度使用原则：不是所有场景都适合JSON类型，核心业务关系数据仍建议使用传统表结构
版本兼容性：
- MySQL 5.7：基本JSON支持
- MySQL 8.0：多值索引、JSON路径表达式等增强功能
性能优化：
- 对频繁查询的JSON属性建立生成列索引
- 避免在WHERE子句中使用JSON函数导致全表扫描
设计建议：
- 保持JSON文档结构一致性
- 为大型JSON文档考虑压缩选项

📚 学习资源推荐

MySQL 官方文档 - JSON 数据类型:
https://dev.mysql.com/doc/refman/8.0/en/json.html
MySQL 官方文档 - JSON 函数:
https://dev.mysql.com/doc/refman/8.0/en/json-function-reference.html
MySQL 8.0 新特性 - JSON 多值索引:
https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-multi-valued
《高性能MySQL》(第4版) - JSON 数据类型章节
《MySQL 8 Cookbook》 - 处理JSON数据章节
Percona 博客 - MySQL JSON 性能分析:
https://www.percona.com/blog/2016/03/07/json-document-fast-lookup-with-mysql-5-7/