MySQL中 JSON 数据类型应用

╰+哭是因爲堅強的太久メ 2023-10-01 15:44 24阅读 0赞

##### 前言 #####

今天接触到mysql中json数据类型，之前不知道有这个类型，今天学习一下。

JSON我相信大家都已经很熟悉了，但在 MySQL中，直至 5.7 版本中，才正式引入 JSON数据类型。在次之前，我们通常使varchar或text数据类型存储JSON格式数据。

##### 如何使用JSON？ #####

在这一章节，不再具体介绍JSON格式以及语法，我们来介绍一下，在MySQL中如何创建JSON数据列。 语法:

create table t_base_data(
    id bigint(20) not null primary key auto_increment, 
    content json
    )

新增数据:

insert into t_base_data (content)values('{"blog": "https://blog.csdn.net/qq_24549805", "account": "anzy"}');

**注意：**

1.  JSON列存储的必须是JSON格式数据，否则会报错。(\[SQL\]insert into t\_base\_data (content)values(’\{blog": “https://blog.csdn.net/qq\_24549805”, “account”: “anzy”\}’);  
    \[Err\] 3140 - Invalid JSON text: “Missing a name for object member.” at position 1 in value for column ‘t\_base\_data.content’.)
2.  JSON数据类型是没有默认值的。

更新数据:

UPDATE t_base_data SET `content` = '{"blog":"测试更新啦","account":"pine"}' WHERE `id` = 1;

查询数据:

SELECT * from t_base_data;

select JSON_EXTRACT(content,'$.blog'),JSON_EXTRACT(content,'$.account') from t_base_data;

返回结果如图：

![img][]![img][img 1]

可以看出JSON被解析拆分，但对于字符串会保留双引号，这种是利用函数方法进行JSON提取的，还可以利用虚列virtual

select JSON_EXTRACT(content,'$.blog'),JSON_EXTRACT(content,'$.account'),JSON_EXTRACT(content,'$.age') from t_base_data;

有了虚列后就可以直接用虚列作为条件查询

explain  select blog from t_base_data where blog = 'https://blog.csdn.net/qq_24549805'; 
    select blog from t_base_data where blog = '"https://blog.csdn.net/qq_24549805"'

通过执行计划可以看出是否走索引和where条件，对于虚列还可以增加索引，就像普通的列一样，不过更新时不需要对虚列再进行更新，直接更新JSON的内容后，虚列会同步更新，因为虚列其实就是个引用，不会冗余存储

增加虚列索引的SQL：

alter table t_base_data add index index_virtual (blog);

再用执行计划查看是否走索引

explain  select blog from t_base_data where blog = 'https://blog.csdn.net/qq_24549805';

![img][img 2]

从结果上看已经走索引了，优化效果达到。

大家尽量每一步sql都执行一下看看结果，有助于理解。

[img]: https://img-blog.csdn.net/20180528223857768?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0NTQ5ODA1/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70
[img 1]: https://img-blog.csdn.net/20180528223912332?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0NTQ5ODA1/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70
[img 2]: https://img-blog.csdn.net/2018052823000555?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0NTQ5ODA1/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70