MySQL JSON数据类型

简介

在MySQL5.7.8之后开始支持一种可高效获取JSON文本中数据的原生JSON类型,该类型具有以下优点:

  • JSON数据有效性检查:BLOB类型无法在数据库层做这样的约束性检查
  • 查询性能的提升:查询不需要遍历所有字符串才能找到数据
  • 支持索引:通过虚拟列的功能可以对JSON中的部分数据进行索引

另外,系统对JSON格式做了一些限制:

  • JSON文本的最大长度取决有系统常量:max_allowed_packet。该值仅在服务器进行存储的时候进行限制,在内存中进行计算的时候是允许超过该值的。
  • JSON列不可有默认值
  • JSON列与其他二进制类型列一样是无法创建索引。但是可以从JSON列中所存储的文本中某些表列值进行创建索引。MySQL最优控制器同样在通过JSON表达创建的索引中进行查询。

如何使用

1. 创建表
create table `test`(
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `content` JSON
) CHARSET = utf8;

1577027-20200304143838231-1124764465.png

2. 插入两条数据
INSERT INTO test (content) VALUES ('{
    "name": "baidu",
    "host": "www.baidu.com"
}');
INSERT INTO test (content) VALUES ('{
    "name": "alibaba",
    "host": "www.alibaba.com"
}');

1577027-20200304143900298-336895982.png

注意:

JSON列存储的必须是JSON格式数据,否则会报错。ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 0 in value for column 'test.content'.

3. 查询

这里主要将关于JSON的查询。

对于表中JSON数据的查询,可以根据JSON中的key值进行查询,看下面SQL语句

select JSON_EXTRACT(content,'$.name'),JSON_EXTRACT(content,'$.host') from test;

1577027-20200304143928394-1605872849.png

select JSON_EXTRACT(content,'$.name'),JSON_EXTRACT(content,'$.host') from test where JSON_EXTRACT(content,'$.name') = "baidu";

1577027-20200304144335586-730778721.png

JSON函数支持

https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html

JSON中虚拟列的使用

对于索引,JSON字段无法对其中的一个key值进行索引,但是虚拟列可以,我们可以建立一个虚拟列和JSON中key值建立联系。

1. 增加虚拟列v_name,v_host

注意:养成加前缀的好习惯, 例如这里使用"v_"来标记该字段是一个虚拟字段,在团队开发时,共同遵守一个约定, 相互配合起来会非常顺利。

ALTER TABLE test ADD COLUMN v_name CHAR(10) AS (content->'$.name');
ALTER TABLE test ADD COLUMN v_host CHAR(30) AS (content->'$.host');

1577027-20200304144526869-89913290.png

下面对虚拟列建立索引

alter table test add index virtual_index(v_name);

然后查看基于v_name的查找的执行计划:

explain select content from test where v_name = "baidu";

1577027-20200304144402173-789233343.png

从结果来看,查找已经走索引了。

上表中字段信息解释:

字段名解释
id选择标识符
select_type查询的类型
table输出结果的表,也就是被查询的表
partions表示匹配的分区
type表示表的连接类型
possible_keys表示查询时,可能使用的索引
key表示实际使用的索引
key_len表示字段的长度
ref列与索引的比较
rows扫描出的行数(估算的行数)
filtered按查询条件过滤的行百分比
Extra执行情况的描述和说明

详细的说明参考这篇博客:https://www.cnblogs.com/tufujie/p/9413852.html

对虚拟列的简介

在MySQL 5.7中,支持两种Generated Column,即Virtual Generated Column和Stored Generated Column,前者只将Generated Column保存在数据字典中(表的元数据),并不会将这一列数据持久化到磁盘上;后者会将Generated Column持久化到磁盘上,而不是每次读取的时候计算所得。很明显,后者存放了可以通过已有数据计算而得的数据,需要更多的磁盘空间,与Virtual Column相比并没有优势,因此,MySQL 5.7中,不指定Generated Column的类型,默认是Virtual Column。

注意:

在更新和插入数据时,不要给虚拟列设定值,否则会引发错误ERROR 3105 (HY000): The value specified for generated column 'v_name' in table 'test' is not allowed.

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值