前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL里面的JSON特性

MySQL里面的JSON特性

作者头像
jeanron100
发布2018-08-08 11:20:09
1.1K0
发布2018-08-08 11:20:09
举报
文章被收录于专栏:杨建荣的学习笔记

在我们梳理的开发规范里面,明确规定对于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不适合做配置文件?

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2018-07-29,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 杨建荣的学习笔记 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 为什么说JSON不适合做配置文件?
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL(TencentDB for MySQL)为用户提供安全可靠,性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性,包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL,可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理,不仅经济实惠,而且稳定可靠,易于运维。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档