MySQL里面的JSON特性

在我们梳理的开发规范里面,明确规定对于lob类型的使用原则只有一个,那就是尽量不要使用。但是很明显,开发同学走到了我们前面,如果你碰到开发同学使用JSON数据类型该怎么建议呢,至少在建议前我们也得了解下JSON类型的使用要领吧。

在说JSON类型之前,我们来说下在没有JSON数据类型之前我们是怎么处理一些复杂的数据映射的。

对于开发语言还是数据库技术来说,字符串处理总是很有魅力的一个特性,所以我会花更多的精力在这个上面。比如之前做了一个简单的测试。里面用到了一些看起来复杂的字符串处理函数find_in_set,substring_index等。

问题的背景是我们为一个表创建了两个列col1,col2,然后插入一些属性值。即col1里面的属性值和col2里面的属性值是对应的。或者换句话来说,col1里面存放的是key,col2存放的是value.

create table test1 ( col1 varchar(100),col2 varchar(100));

insert test1 select

'26,59,6', '1502.5,1690,2276.77' union all select

'59,33,6', '3502.1,1020,2276.77' union all select

'22,8,59', '1332.6,2900,1520.77';

写入数据之后,表里的数据分布是这样的:

mysql> select *from test1;

+---------+---------------------+

| col1 | col2 |

+---------+---------------------+

| 26,59,6 | 1502.5,1690,2276.77 |

| 59,33,6 | 3502.1,1020,2276.77 |

| 22,8,59 | 1332.6,2900,1520.77 |

+---------+---------------------+

3 rows in set (0.00 sec)

现在我们如果要做一个数据查询,把key是59的value值查出来,然后需要value值小于2000.

如果使用SQL,可能会是这样的解决方法。

mysql> select col1,col2

-> from (select *,find_in_set('59',col1) as rn from test1) k

-> where substring_index(concat(',',substring_index(col2,',',rn)),',',-1)

-> <'2000';

+---------+---------------------+

| col1 | col2 |

+---------+---------------------+

| 26,59,6 | 1502.5,1690,2276.77 |

| 22,8,59 | 1332.6,2900,1520.77 |

+---------+---------------------+

2 rows in set (0.00 sec)

当然可能你会有更好的解决方案,但是看起来似乎也不是一个很好的解决方法,比如这种设计中,如果要加一个字段,那简直就是灾难性的。

在这种模式下,使用JSON其实也是一种改进思路,当然这是在MySQL 5.7之后了。

我们创建的表为json_test,然后插入两行记录。

create table json_test ( uid int auto_increment,data json,primary key(uid))engine=innodb;

insert into json_test values (NULL,'{"name":"jeanron","mobile":"1500010002","location":"beijing"}');

insert into json_test values (NULL,'{"name":"jianrong","mobile":"15100020003","location":"gansu"}');

现在到了JSON发挥作用的时候了,如果要查询出数据,我们可以使用类似引用的语法"->"即可。所以我们可以把数据很方便的解析出来。

mysql> select data->"$.name" as name,(data->"$.location") from json_test group by name;

+------------+----------------------+

| name | (data->"$.location") |

+------------+----------------------+

| "jeanron" | "beijing" |

| "jianrong" | "gansu" |

+------------+----------------------+

2 rows in set (0.00 sec)

在这种模式下,上面的第一个难题其实就完全可以使用这种方式来解决了。

在这个基础上我们更近一步,在5.7里面还有辅助的特性虚拟列和相关的索引,可以提高我们查询的效率。我们添加一个虚拟列user_name.

ALTER TABLE json_test ADD user_name varchar(128) GENERATED ALWAYS AS(json_extract(data,'$.name')) VIRTUAL;

使用desc查看,其实可以看到user_name的属性是相对特殊的。

mysql> desc json_test;

+-----------+--------------+------+-----+---------+-------------------+

| Field | Type | Null | Key | Default | Extra |

+-----------+--------------+------+-----+---------+-------------------+

| uid | int(11) | NO | PRI | NULL | auto_increment |

| data | json | YES | | NULL | |

| user_name | varchar(128) | YES | MUL | NULL | VIRTUAL GENERATED |

+-----------+--------------+------+-----+---------+-------------------+

3 rows in set (0.00 sec)

然后在这个基础上添加一个索引。

alter table json_test add index idx_username(user_name);

使用show create table的方式查看建表DDL可以清晰的看到是有一个辅助索引。

CREATE TABLE `json_test` (

`uid` int(11) NOT NULL AUTO_INCREMENT,

`data` json DEFAULT NULL,

`user_name` varchar(128) GENERATED ALWAYS AS (json_extract(`data`,'$.name')) VIRTUAL,

PRIMARY KEY (`uid`),

KEY `idx_username` (`user_name`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |

然后我们再次查询,注意在这里的user_name使用了双引号单引号混合的方式。

mysql> select user_name,(data->"$.location") from json_test where user_name = '"jianrong"';

+------------+----------------------+

| user_name | (data->"$.location") |

+------------+----------------------+

| "jianrong" | "gansu" |

+------------+----------------------+

1 row in set (0.00 sec)

如果带有疑惑,我们只有单引号是否可以,答案会让你失望。

mysql> select user_name,(data->"$.location") from json_test where user_name = 'jianrong';

Empty set (0.00 sec)

所以不是严格意义上100%的兼容性,至少在各式统一上我们还是需要一些额外的工作。

然后来看下执行计划的情况,可以看到语句明显使用到了索引,对于后期的数据分析和处理还是大有帮助的。

mysql> explain select user_name,(data->"$.location")from json_test where user_name = '"jeanron"';

+----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+

| 1 | SIMPLE | json_test | NULL | ref | idx_username | idx_username | 387 | const | 1 | 100.00 | NULL |

+----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+

1 row in set, 1 warning (0.00 sec)

在这个基础上如果做更多的分析,其实explain format=json也是一种改进方式,对于执行计划,我们可以得到属性值,通过解析的方式能够把执行计划做得更好。

JSON的新特性对于MySQL来说确实是一个不错的特性,如果数据量巨大,还是需要考虑通过空间换时间的思路来改进。如果大家了解Oracle,PostgreSQL等数据库,其实这些特性也是有的,Oracle 12c里面明确有这个特性,postgreSQL也有这个特性,还区分为json和jsonb,对于NoSQL来说,那更是它们擅长的,所以MySQL实现这个是一种辅助,绝对不是做了颠覆性的改进。

历史的车轮要前进,我们的方案只能是折中之后的方案。就好比早年的时候Oracle全面支持XML,结果到现在这类需求明显有些凉了。

JSON也不是万金油,推荐大家参考这一篇。

为什么说JSON不适合做配置文件?

本文分享自微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2018-07-29

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏吴裕超

Protocol Buffers 在前端项目中的使用

公司后端使用的是go语言,想尝试用pb和前端进行交互,于是便有了这一次尝试,共计花了一星期时间,网上能查到的文档几乎都看了一遍,但大多都是教在node环境下如何...

1K40
来自专栏技术记录

通讯协议序列化解读(一) Protobuf详解教程

前言:说到JSON可能大家很熟悉,是目前应用最广泛的一种序列化格式,它使用起来简单方便,而且拥有超高的可读性。但是在越来越多的应用场景里,JSON冗长的缺点导致...

12040
来自专栏JadePeng的技术博客

知识图谱学习笔记(1)

RDF(Resource Description Framework),即资源描述框架,其本质是一个数据模型(Data Model)。它提供了一个统一的标准,用...

22900
来自专栏前端新视界

构建具有用户身份认证的 React + Flux 应用程序

React 的生态系统很大,为了解决 React 中比较困难的问题,你可以选择多种模块。大多数实际的 React 应用程序都有一些共同的需求,这些需求主要包括状...

15900
来自专栏owent

针对Java JIT的优化(转表工具:xresloader)

之前做了一个转Excel表到lua/二进制/json/xml的工具-xresloader。目的一方面是方便策划。另一方面是统一客户端和服务器的转表模式,并且要灵...

9120
来自专栏程序猿DD

一文告诉你,Intellij IDEA神器隐藏的11种实用小技巧!

我们可以使用【Presentation Mode】,将IDEA弄到最大,可以让你只关注一个类里面的代码,进行毫无干扰的coding。

19730
来自专栏IT派

我用 Python 爬取微信好友,最后发现一个大秘密

你身处的环境是什么样,你就会成为什么样的人。现在人们日常生活基本上离不开微信,但微信不单单是一个即时通讯软件,微信更像是虚拟的现实世界。你所处的朋友圈是怎么样,...

22340
来自专栏老马寒门IT

jQuery的ajax详解

jQuery内部也封装了对原生ajax请求的方法,可以很方便我们的对后台异步请求处理。

18000
来自专栏程序猿DD

JSON Web Token - 在Web应用间安全地传递信息

JSON Web Token(JWT)是一个非常轻巧的规范。这个规范允许我们使用JWT在用户和服务器之间传递安全可靠的信息。

10420
来自专栏带你撸出一手好代码

神奇的Java

使用java开发一个socket服务器,使用protocol buffer作为和客户端通信的数据格式。然后,问题就来了,这个socket服务器要如何调试?换句话...

13620

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励