MySQL 中的 JSON 功能详解

MySQL 从 5.7.8 版本开始引入了对 JSON 数据类型的原生支持，提供了强大的 JSON 处理能力。以下是 MySQL 中 JSON 功能的全面介绍：

1. JSON 数据类型

MySQL 提供了专门的 JSON 数据类型：

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    details JSON,
    attributes JSON
);

2. 创建 JSON 值

有多种方式创建 JSON 值：

-- 直接插入 JSON 字符串
INSERT INTO products VALUES (NULL, 'Smartphone', 
    '{"color": "black", "storage": "128GB"}',
    '{"dimensions": {"width": 7.5, "height": 15.0}}');

-- 使用 JSON_OBJECT 函数
INSERT INTO products VALUES (NULL, 'Laptop',
    JSON_OBJECT("color", "silver", "ram", "16GB"),
    JSON_OBJECT("weight", "1.2kg"));

-- 使用 JSON_ARRAY 函数
UPDATE products SET details = JSON_SET(details, '$.tags', 
    JSON_ARRAY("portable", "high-performance")) WHERE id = 2;

3. 查询 JSON 数据

路径表达式

MySQL 使用 -> 和 ->> 操作符查询 JSON 数据：

-- 获取 JSON 对象的属性
SELECT name, details->'$.color' AS color FROM products;

-- 使用 ->> 去除引号
SELECT name, details->>'$.color' AS color FROM products;

-- 查询嵌套属性
SELECT name, attributes->'$.dimensions.width' AS width FROM products;

JSON 函数

MySQL 提供了丰富的 JSON 函数：

-- 提取值
SELECT JSON_EXTRACT(details, '$.color') FROM products;

-- 检查路径是否存在
SELECT name FROM products WHERE JSON_CONTAINS_PATH(details, 'one', '$.color');

-- 检查值是否存在
SELECT name FROM products WHERE JSON_CONTAINS(details, '"black"', '$.color');

-- 获取所有键
SELECT JSON_KEYS(details) FROM products;

4. 修改 JSON 数据

-- 更新整个 JSON 文档
UPDATE products SET details = '{"color": "blue"}' WHERE id = 1;

-- 部分更新
UPDATE products SET details = JSON_SET(details, '$.color', 'red') WHERE id = 1;

-- 添加元素
UPDATE products SET details = JSON_INSERT(details, '$.battery', '4000mAh') WHERE id = 1;

-- 删除元素
UPDATE products SET details = JSON_REMOVE(details, '$.storage') WHERE id = 1;

5. JSON 索引

从 MySQL 8.0.17 开始，可以对 JSON 列创建多值索引：

-- 创建函数索引
ALTER TABLE products ADD INDEX idx_color ((CAST(details->>'$.color' AS CHAR(20))));

-- 多值索引
ALTER TABLE products ADD INDEX idx_tags ((CAST(details->'$.tags' AS CHAR(50) ARRAY)));

6. 生成列与 JSON

可以基于 JSON 数据创建生成列：

ALTER TABLE products ADD color VARCHAR(20) 
    GENERATED ALWAYS AS (details->>'$.color') STORED;

7. 性能考虑

• JSON 列比结构化数据占用更多存储空间
• 复杂查询可能性能较差
• 考虑将频繁查询的属性提取为单独列
• 合理使用索引提高查询性能

8. 最佳实践

1. 仅在需要灵活数据结构时使用 JSON
2. 对频繁查询的字段考虑使用生成列
3. 避免过度嵌套 JSON 结构
4. 使用适当的索引优化查询性能
5. 考虑使用 JSON_SCHEMA_VALID() 函数验证数据完整性(MySQL 8.0.17+)

MySQL 的 JSON 功能为半结构化数据存储提供了强大支持，但在设计数据库时应根据具体需求权衡关系型和 JSON 数据模型的使用。