json类型大小 mysql_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;

2. 插入两条数据

INSERT INTO test (content) VALUES ('{

"name": "baidu",

"host": "www.baidu.com"

}');

INSERT INTO test (content) VALUES ('{

"name": "alibaba",

"host": "www.alibaba.com"

}');

注意：

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;

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

JSON函数支持

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');

下面对虚拟列建立索引

alter table test add index virtual_index(v_name);

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

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

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

上表中字段信息解释：

字段名

解释

id

选择标识符

select_type

查询的类型

table

输出结果的表，也就是被查询的表

partions

表示匹配的分区

type

表示表的连接类型

possible_keys

表示查询时，可能使用的索引

key

表示实际使用的索引

key_len

表示字段的长度

ref

列与索引的比较

rows

扫描出的行数(估算的行数)

filtered

按查询条件过滤的行百分比

Extra

执行情况的描述和说明

对虚拟列的简介

在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.