MySQL JSON 数据类型用法及与传统JSON字符串的对比 JSON数据类型简介

文章目录

• 前言
• • 1. 基本用法
• JSON数据类型 vs 传统JSON字符串
• • 1. 存储方式
  • 2. 查询方式对比
  • 3. 索引支持
• JSON存储对象和数组的性能考虑
• • 1. 存储对象
  • 2. 存储数组
• 性能对比总结
• 最佳实践建议

前言

MySQL从 5.7 版本开始引入了 JSON 数据类型，专门用于存储 JSON 格式的数据。与传统的将 JSON 作为字符串存储在 VARCHAR 或 TEXT 字段中相比，JSON 数据类型提供了更好的存储效率和查询性能。

1. 基本用法

-- 创建包含JSON字段的表CREATE TABLE products ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), attributes JSON, json_string TEXT);-- 插入JSON数据INSERT INTO products (name, attributes, json_string) VALUES ( \'Laptop\', \'{\"brand\": \"Dell\", \"specs\": {\"cpu\": \"i7\", \"ram\": \"16GB\"}, \"tags\": [\"electronics\", \"computer\"]}\', \'{\"brand\": \"Dell\", \"specs\": {\"cpu\": \"i7\", \"ram\": \"16GB\"}, \"tags\": [\"electronics\", \"computer\"]}\');

JSON数据类型 vs 传统JSON字符串

1. 存储方式

• JSON数据类型：以优化的二进制格式存储，解析后的结构
• 传统JSON字符串：纯文本存储，需要每次使用时解析

2. 查询方式对比

使用LIKE查询传统JSON字符串

-- 查询品牌为Dell的产品（传统JSON字符串方式）SELECT * FROM products WHERE json_string LIKE \'%\"brand\": \"Dell\"%\';

这种方式的缺点：

• 无法使用索引
• 可能产生误匹配（如值中包含相同字符串）
• 性能差，需要全表扫描

使用JSON函数查询JSON数据类型

-- 查询品牌为Dell的产品（JSON数据类型方式）SELECT * FROM products WHERE JSON_EXTRACT(attributes, \'$.brand\') = \'Dell\';-- 或使用箭头语法（MySQL 8.0+）SELECT * FROM products WHERE attributes->\'$.brand\' = \'\"Dell\"\';

优点：

• 可以使用生成的列和索引
• 精确查询，不会误匹配
• 性能更好

3. 索引支持

JSON字段可以通过生成列添加索引：

-- 为JSON字段的brand属性创建索引ALTER TABLE products ADD COLUMN brand VARCHAR(50) GENERATED ALWAYS AS (JSON_UNQUOTE(attributes->\'$.brand\')) STORED;CREATE INDEX idx_brand ON products(brand);

JSON存储对象和数组的性能考虑

1. 存储对象

{ \"brand\": \"Dell\", \"specs\": { \"cpu\": \"i7\", \"ram\": \"16GB\" }}

• 查询性能：直接访问嵌套属性比传统JSON字符串解析快得多
• 索引：可以为嵌套属性创建索引（通过生成列）

2. 存储数组

{ \"tags\": [\"electronics\", \"computer\", \"laptop\"]}

• 查询包含特定元素的数组：

SELECT * FROM products WHERE JSON_CONTAINS(attributes->\'$.tags\', \'\"electronics\"\');

• 性能考虑：
  • 数组查询通常比简单属性查询慢
  • 大数组可能影响性能
  • 考虑将频繁查询的数组元素提取到单独的表中

性能对比总结

最佳实践建议

1. 对于需要频繁查询的JSON属性，考虑提取为单独的列并建立索引
2. 避免在JSON中存储过大的数组
3. MySQL 8.0+对JSON支持更好，优先使用新版本
4. 对于简单键值对，考虑使用传统的关系型设计而非JSON
5. 使用JSON_VALID()约束确保数据完整性

JSON数据类型在大多数场景下都比传统JSON字符串存储方式性能更好，特别是在查询和索引支持方面。但对于简单应用或不需要查询JSON内容的情况，传统字符串方式可能更简单。