如何将JSON数据高效存入MySQL：从基础到实践
在当今数据驱动的应用开发中，JSON（JavaScript Object Notation）因轻量、灵活、易读的特性，已成为前后端数据交换的通用格式，而MySQL作为最流行的关系型数据库之一，从5.7版本开始原生支持JSON数据类型，为存储和查询JSON数据提供了高效解决方案，本文将详细介绍如何将JSON数据存入MySQL，从数据类型选择到具体操作步骤，再到优化技巧,助你轻松这一技能。
MySQL中的JSON数据类型：JSON vs. TEXT
在存储JSON数据前，首先需要明确MySQL提供的两种JSON存储方式：JSON数据类型和传统TEXT/VARCHAR类型。优先选择JSON数据类型,原因如下：
JSON数据类型的优势
- 有效性验证：MySQL会自动验证存入的数据是否符合JSON格式（如语法正确、结构合法），若存入非JSON格式（如
{"key": "value"}后缺少闭合括号），会直接报错,避免脏数据。 - 优化存储：
JSON类型采用二进制格式存储，比TEXT类型更节省空间（空JSON对象在JSON类型中仅占用1字节，而TEXT需占用2字节）。 - 支持JSON函数：MySQL提供了丰富的JSON操作函数（如
JSON_EXTRACT、JSON_SET、JSON_CONTAINS等），可直接对JSON字段进行查询、修改和解析,无需额外代码解析字符串。 - 路径查询优化：
JSON类型支持生成生成列（Generated Column）并创建函数索引,可大幅提升JSON字段的查询性能。
何时选择TEXT/VARCHAR？
若仅需存储简单的JSON字符串（如配置信息、日志数据），且不需要频繁查询JSON内部字段，或使用的MySQL版本低于5.7，可选择TEXT或VARCHAR类型，但需注意：存入前需自行验证JSON格式，查询时需通过代码解析字符串,性能较差。
将JSON数据存入MySQL的详细步骤
步骤1：创建表时定义JSON字段
在建表语句中，使用JSON类型定义字段即可，存储用户信息的表，其中profile字段存储用户的扩展JSON信息（如地址、偏好设置等）：
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
profile JSON, -- 定义JSON字段
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
步骤2：准备JSON数据
JSON数据可以是对象（）或数组（[]），
- 用户对象：
{"name": "张三", "age": 30, "address": {"city": "北京", "district": "朝阳区"}, "hobbies": ["阅读", "跑步"]} - 简单数组：
["苹果", "香蕉", "橙子"]
步骤3：插入JSON数据
MySQL支持多种方式插入JSON数据,以下为常用方法：
方法1：直接插入JSON字符串
将JSON数据作为字符串直接插入,MySQL会自动验证格式：
INSERT INTO users (username, email, profile)
VALUES ('张三', 'zhangsan@example.com', '{"name": "张三", "age": 30, "address": {"city": "北京", "district": "朝阳区"}, "hobbies": ["阅读", "跑步"]}');
方法2：使用JSON_ARRAY和JSON_OBJECT函数构建JSON
若数据来自多个字段，可通过MySQL内置函数动态构建JSON,避免手动拼接字符串：
INSERT INTO users (username, email, profile)
VALUES ('李四', 'lisi@example.com',
JSON_OBJECT('name', '李四', 'age', 25, 'address', JSON_OBJECT('city', '上海', 'district', '浦东新区'), 'hobbies', JSON_ARRAY('游泳', '旅行')));
JSON_OBJECT(key1, value1, key2, value2, ...)：生成JSON对象。JSON_ARRAY(value1, value2, ...)：生成JSON数组。
方法3：插入变量或应用程序传入的JSON
在应用程序（如Python、Java）中，可将JSON数据序列化为字符串后传入MySQL，或使用预处理语句（防止SQL注入），Python中使用pymysql：
import pymysql
import json
# 准备JSON数据（Python字典）
profile_data = {
"name": "王五",
"age": 28,
"address": {"city": "广州", "district": "天河区"},
"hobbies": ["篮球", "游戏"]
}
# 连接MySQL
conn = pymysql.connect(host='localhost', user='root', password='password', db='test_db')
cursor = conn.cursor()
# 方法1：直接传入字典（pymysql会自动序列化为JSON字符串）
cursor.execute(
"INSERT INTO users (username, email, profile) VALUES (%s, %s, %s)",
('王五', 'wangwu@example.com', profile_data)
)
# 方法2：手动序列化为JSON字符串（适用于其他驱动）
json_str = json.dumps(profile_data)
cursor.execute(
"INSERT INTO users (username, email, profile) VALUES (%s, %s, %s)",
('王五_2', 'wangwu2@example.com', json_str)
)
conn.commit()
cursor.close()
conn.close()
验证与查询JSON数据
验证JSON字段是否存储成功
使用SELECT语句查询JSON字段，MySQL会自动格式化输出（通过JSON_PRETTY函数美化显示）：
SELECT id, username, JSON_PRETTY(profile) AS profile FROM users WHERE username = '张三';
输出：
+----+----------+----------------------------------------------------------------------------------------------------------------+
| id | username | profile |
+----+----------+----------------------------------------------------------------------------------------------------------------+
| 1 | 张三 | { |
| | | "name": "张三", |
| | | "age": 30, |
| | | "address": { |
| | | "city": "北京", |
| | | "district": "朝阳区" |
| | | }, |
| | | "hobbies": [ |
| | | "阅读", |
| | | "跑步" |
| | | ] |
| | | } |
+----+----------+----------------------------------------------------------------------------------------------------------------+查询JSON内部字段
使用->（返回JSON对象）或->>（返回JSON字符串）操作符提取字段值：
-- 查询张三的年龄（返回整数） SELECT profile->'$.age' AS age FROM users WHERE username = '张三'; -- 查询张三的城市（返回字符串） SELECT profile->>'$.address.city' AS city FROM users WHERE username = '张三';
输出：
+------+
| age |
+------+
| 30 |
+------+
+--------+
| city |
+--------+
| 北京 |
+--------+修改JSON数据
使用JSON_SET（更新/添加字段）、JSON_REPLACE（替换字段）、JSON_REMOVE（删除字段）等函数：
-- 更新张三的年龄为31岁，添加手机号字段 UPDATE users SET profile = JSON_SET(profile, '$.age', 31, '$.phone', '13800138000') WHERE username = '张三'; -- 删除张三的hobbies字段 UPDATE users SET profile = JSON_REMOVE(profile, '$.hobbies') WHERE username = '张三';
优化JSON存储与查询性能
使用生成列（Generated Column）创建索引
JSON字段本身无法直接创建索引，但可以通过生成列提取JSON内部字段，并为生成列创建索引,大幅提升查询性能：
-- 添加一个生成列，提取JSON中的城市字段 ALTER TABLE users ADD COLUMN city_generated VARCHAR(50) GENERATED ALWAYS AS (profile->>'$.address.city') STORED; -- 为生成列创建索引 CREATE INDEX idx_city ON users(city_generated); -- 查询时使用生成列（性能等同于普通字段索引） SELECT * FROM users WHERE city_generated = '北京';
避免过度嵌套JSON
JSON虽灵活，但嵌套层级过深（如超过5层）会导致查询性能下降，建议将高频查询的顶层字段拆分为普通列,仅将低频查询或非结构化数据存入JSON字段。
控制JSON数据大小
JSON字段适合存储中小型数据（单条记录建议小于1MB），对于大型JSON（如日志文件），建议拆分为多个表或使用专门的文档数据库（如MongoDB）。
还没有评论，来说两句吧...