mysql json数据查询 mysql的json数据类型

转载

码海探险先锋 2023-07-06 19:47:59

文章标签 mysql json数据查询 JSON MySQL 数据类型 文章分类 MySQL 数据库

聊聊MySQL中的数据类型JSON，简单分析下5.7.8前后存储JSON的异同点以及优缺点，做一个抛砖引玉

mysql json数据查询 mysql的json数据类型_JSON

今天介绍一个MySQL中的数据类型-JSON，相信大家对JSON都不陌生，在日常工作中使用到的频率也很高，话不多说，直接开始。

何谓JSON

看下RFC文档对于JSON的描述

mysql json数据查询 mysql的json数据类型_JSON_02

1.基于 JavaScript 语言的轻量级的数据交换格式

2.基于文本

3.语言无关

JSON应用场景

我大概使用过以下两类：

1.接口的数据交换，比如ajax请求时的application/json、rpc调用时的JSON序列化\反序列化；

2.以JSON格式存储数据，我接触过以下两种：

2.1 以Mongodb为代表的文档型数据库，很好的支持JSON格式的数据存储；

2.2 以MySQL为代表的关系型数据库，5.7.8之前没有JSON这种数据类型，只能以varchar或者text形式变相的支持JSON，存取键值极不方便；5.7.8开始有JSON数据类型，有专门语法支持键值的存取，易用性得到很大提升。

接下来重点聊聊MySQL中如何存取JSON以及存在的一些问题。

MySQL 存储JSON

熟悉关系型数据库的同学都知道，数据存储在表中，得先有表才能插数据，看一条普通的SQL insert语句

insert into user(id,name,age) values(1,'jack',10);

代表的语义是往user表中插入一条数据，这条数据有三个属性，分别是id、name、age，各自对应MySQL user表中的三个列，如果我们向user表中插入一个不存在的列salary，MySQL会报错

Error Code: 1054. Unknown column 'salary' in 'field list'

结论是要往MySQL表中插入数据，必须提前定义好表结构，表结构包括表名、表字符集、表包含的字段、字段名、字段类型等等。

有什么办法能不给表加物理字段就可以为数据增加属性呢？

mysql json数据查询 mysql的json数据类型_MySQL_03

给表预置一个扩展字段是一种解决思路，比如extdata，里面存储JSON形式的键值对，形如：

extdata

{"salary":1000,
"sex":'女',
"其他key":'其他值'
}

至于存哪些key完全由使用方决定，key的数量不限，value的类型也不限，是不是有很好的扩展性，不管业务怎么变，底层存储都是支持的。

这也就是为什么要在MySQL中存取JSON的目的，主要是为了追求扩展性。

具体到MySQL中怎么实现，前面提到MySQL 5.7.8之前是不支持JSON的，要支持JSON语义，只能以字符串形式来变相实现，比如要修改extdata中的salary为2000，是没有办法直接修改的，需要先在应用层将extdata读出然后反序列化为JSON对象，通过JSON对象的Api来修改salary的值，修改完以后将新的JSON对象序列化为新JSON串，最后整体修改user表中的extdata字段为新JSON串，用代码实现大体如下：

1.result = db.execute("select extdata from user where id = xxx");
2.JSONObj = JSONUtil.parse(result.get("extdata"));
3.JSONObj.put("salary",2000);
4.extdata_str = JSONObj.toJSONString();
5.db.execute("update user set extdata=extdata_str where id=xxx");

这一套更新操作繁琐且性能低，读取操作也存在类似问题，由于没有原生Api的支持，这一切感觉有点糟糕。

到了MySQL 5.7.8开始，MySQL开始支持JSON这种数据类型，看下官方文档的介绍：

mysql json数据查询 mysql的json数据类型_MySQL_04

MySQL新增加的原生JSON类型比在字符串列中存储 JSON 格式的字符串相比有两个优点：

1.自动的数据校验，对于JSON类型的列MySQL会校验其合法性；

2.提供了更方便的Api用于存取，避免了繁琐的应用层操作。

看下基于MySQL 5.7.8，如何优雅的存取JSON类型中的键值，依然以修改extdata中的salary为例：

update user set extdata = JSON_SET(user.extdata, '$.salary',2000) where id =1;

读取salary的值：

select JSON_EXTRACT(user.extdata, '$.salary') from user where id =1;

借助JSON_SET和JSON_EXTRACT这两个Api，极大的降低了存取的复杂度，想深入了解MySQL JSON用法的请参考文章最后的推荐阅读内容。

说到这儿，借助MySQL的原生JSON类型以及相关的Api存取扩展数据在易用性方面已经没什么问题了，接下来从性能角度思考下是否有待提升。

/*找出salary等于2000的user*/
select * from user where JSON_EXTRACT(user.extdata, '$.salary') =2000;

在我自己的pc机上，user表中共300万条数据，执行这条SQL花费接近3秒，不谈快慢，就论是否有优化空间，贴个执行计划出来

mysql json数据查询 mysql的json数据类型_JSON_05

面对大名鼎鼎的全表扫描如何优化呢？

优化JSON查询

按照过往的思路，我们只要设计合理的索引就能避免全表扫描，但这次面对JSON似乎有点黔驴技穷了，别担心，大名鼎鼎的MySQL早已帮你做了既生瑜又生亮的美事，看看官方怎么说。

mysql json数据查询 mysql的json数据类型_MySQL_06

JSON类型列无法直接索引；
可以基于JSON创建一个生成列，然后基于生成列创建索引，从而达到对JSON类型列加索引的效果。

接着看下何谓生成列

mysql json数据查询 mysql的json数据类型_mysql json数据查询_07

生成列的值在插入数据时不需要设置，MySQL会根据生成列关联的表达式自动计算填充，生成列的定义方式如下：

col_name data_type [GENERATED ALWAYS] AS (expr)
  [VIRTUAL | STORED] [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']

AS (expr)指示生成列并定义用于计算列值的表达式，可以在前面加上GENERATED ALWAYS明确的表示这是一个生成列。　　

回归到我们的场景中，分三步进行优化：

1.创建一个生成列v_salary，计算列值表达式为extdata->"$.salary"，代表提取extdata中的salary值
ALTER TABLE `user` ADD COLUMN `v_salary` DECIMAL(10,2) as (extdata->"$.salary") AFTER `extdata`;
2.针对v_salary创建索引
ALTER TABLE `user` ADD INDEX `idx_salary` (`v_salary`) ;
3.替换查询语句中JSON_EXTRACT(user.extdata, '$.salary')为v_salary;
select * from user where v_salary =2000

select * from user where v_salary =2000，执行耗时为0.047s，这个优化效果非常显著。

看下现在的执行计划已经使用了索引

mysql json数据查询 mysql的json数据类型_数据类型_08