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

大家好，今天我们来聊聊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/

文章版权归作者所有，未经允许请勿转载。如内容涉嫌侵权，请在本页底部进入<联系我们>进行举报投诉!

THE END