详解MySQL中JSON数据类型用法及与传统JSON字符串对比_Javascript

前言

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

基本用法

-- 创建包含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"');

性能考虑：

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

性能对比总结

特性	json数据类型	传统json字符串
存储效率	高	低
查询性能	高	低
索引支持	支持	不支持
复杂查询能力	强	弱
嵌套对象访问性能	高	低
数组操作性能	中	低
数据验证	有	无