MySQL JSON数据类型支持与使用指南 | 详细解析与示例

MySQL从5.7版本开始正式支持JSON数据类型，提供了丰富的函数和操作符来处理JSON数据。以下是详细的解析：

1. 版本支持

• MySQL 5.7+：原生支持JSON数据类型，提供验证、存储优化及查询函数。
• MySQL 8.0+：增强JSON功能，如路径表达式->>简化查询、JSON聚合函数JSON_ARRAYAGG()/JSON_OBJECTAGG()，以及性能优化（如JSON文档二进制存储格式改为json_type）。

2. 创建JSON字段

在表中定义JSON列：

CREATE TABLE users (
    id INT PRIMARY KEY,
    profile JSON,
    preferences JSON
);

• 自动验证：插入或更新的数据必须是合法JSON，否则报错。

3. 插入JSON数据

直接插入字符串或使用函数生成：

-- 插入JSON对象
INSERT INTO users (id, profile) 
VALUES (1, '{"name": "Alice", "age": 30, "hobbies": ["reading", "music"]}');

-- 使用JSON函数
INSERT INTO users (id, preferences)
VALUES (2, JSON_OBJECT('theme', 'dark', 'notifications', TRUE));

4. 查询JSON数据

提取数据

• JSON_EXTRACT() 或 ->：返回指定路径的值（保留JSON格式）。

• ->>：提取并转换为字符串。

  SELECT profile->'$.name' AS name,  -- 结果: "Alice"
         profile->>'$.age' AS age   -- 结果: 30 (字符串类型)
  FROM users;
  

路径语法

• $.key：对象键。
• $[0]：数组索引。
• $.path.to.nested: 嵌套路径。

条件查询

-- 查找年龄大于25的用户
SELECT * FROM users 
WHERE JSON_EXTRACT(profile, '$.age') > 25;
-- 或使用简化语法
SELECT * FROM users WHERE profile->>'$.age' > 25;

5. 修改JSON数据

• JSON_SET()：更新或添加值。
• JSON_INSERT()：仅添加新键。
• JSON_REPLACE()：仅更新现有键。
• JSON_REMOVE()：删除键。

UPDATE users 
SET profile = JSON_SET(profile, '$.age', 31, '$.city', 'Paris')
WHERE id = 1;
-- 结果: {"name": "Alice", "age": 31, "hobbies": ["reading", "music"], "city": "Paris"}

6. 生成JSON

使用函数构建JSON结构：

-- 创建JSON对象和数组
SELECT JSON_OBJECT('id', id, 'name', profile->>'$.name') AS user_json
FROM users;

SELECT JSON_ARRAY('a', 1, TRUE, NULL);  -- 结果: ["a", 1, true, null]

7. 索引优化

• 虚拟列（Generated Columns）：提取JSON值并创建索引。

  ALTER TABLE users
  ADD COLUMN age INT 
  GENERATED ALWAYS AS (profile->>'$.age') VIRTUAL,
  ADD INDEX (age);
  

• MySQL 8.0+ 多值索引：对JSON数组创建索引，加速MEMBER OF、JSON_CONTAINS等查询。

8. 性能与应用场景

• 优势：灵活处理半结构化数据（如动态属性、日志字段）。
• 劣势：复杂查询性能低于传统关系型设计，需谨慎使用。
• 适用场景：配置项、动态表单数据、API响应存储。

9. 注意事项

• 存储限制：JSON列最大容量受max_allowed_packet限制（默认4MB）。
• 性能开销：频繁解析JSON可能导致CPU负载增加。
• 设计权衡：优先考虑关系模型，JSON适合补充动态需求。

10. 示例全流程

-- 创建表
CREATE TABLE products (
    id INT PRIMARY KEY,
    details JSON,
    price DECIMAL(10,2)
);

-- 插入数据
INSERT INTO products VALUES (1, '{"name": "Laptop", "specs": {"RAM": "16GB", "Storage": "512GB"}}', 1200.00);

-- 查询特定属性
SELECT details->>'$.name' AS product_name,
       details->'$.specs.RAM' AS ram
FROM products;

-- 更新JSON内容
UPDATE products
SET details = JSON_SET(details, '$.specs.Storage', '1TB')
WHERE id = 1;

-- 创建虚拟列索引
ALTER TABLE products
ADD COLUMN product_name VARCHAR(255) 
GENERATED ALWAYS AS (details->>'$.name') VIRTUAL,
ADD INDEX (product_name);

总结

MySQL的JSON支持平衡了灵活性与查询能力，适合处理半结构化数据，但在设计时需权衡关系模型与JSON的适用场景，合理使用索引优化查询性能。