MySQL5.7 新增两种字段类型:Json 和 Generated,Generated 型的产生和 Json 的关系密不可分,如果没有Generated 类型,Json 类型在强大,生产中可能也无法使用,因为 Json 不支持索引,但是如果要查询 Json 里的数据,没有索引就是全表扫描,在执行效率上肯定是不能用于生产环境的,但是有了 Generated 类型就不同了,Generated 类型简单地说是一个虚拟字段,值是不可更新的,值来源其他字段或者字段间计算或是转化而来的,这种类型是可以创建索引,利用 Generated 的特性,就可以间接的给 Json 类型中的 key 创建索引,解决 Json 不能创建索引的问题。简而言之, Generated 类型的产生,为 Json 类型在索引方面的问题提供了支持。JSON 的值包含单个值、数组、元组、标注的 Json 格式等几种格式。
客观地说,Json类型的出现,为研发提供了便利,提供了类似NoSQL的支持,但是走DBA运维的角度来看,也许是一个麻烦,这个麻烦和text等大字段的麻烦是一致的,json可能在实际的生产中会成为text字段的变种,望DBA在以后5.7的使用中密切关注这个类型。
CREATE TABLE json_test (
id int(11) NOT NULL AUTO_INCREMENT,
group_name varchar(20) DEFAULT NULL,
user_info json DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE json_test ADD COLUMN user_info JSON DEFAULT NULL;
1、查询全部结果
2、JSON_EXTRACT 查询
JSON_EXTRACT 这个函数主要用作精确匹配,如查找 user_info中age=21 的记录或者查询 name=’wangwei’的记录,或者 name like ‘%wangwei%’模糊查询:
SELECT * FROM json_test WHERE JSON_EXTRACT(user_info,'$.age')=21;
在值为数组的时候,数组的起始位置值为0,同时,MySQL 在5.5.9的版本增加了->,提供了等价于 JSON_EXTRACT另外一种写:
SELECT * FROM json_test WHERE user_info->"$.age"=21;
3、JSON_SEARCH 查询
JSON_SEARCH 字符串查找函数,查找 json 所有 key 中值为字符串’wangwei’的记录,为精确匹配;加上%的话有点 like 的意思:
SELECT * FROM json_test WHERE JSON_SEARCH(user_info,'all','wangwei') IS NOT NULL;
SELECT * FROM json_test WHERE JSON_SEARCH(user_info,'all','wangwei%') IS NOT NULL;
SELECT * FROM json_test WHERE JSON_SEARCH(user_info,'all','%wangwei%') IS NOT NULL;
4、JSON_CONTAINS 判断 key 是否包含指定的值
JSON_CONTAINS 判断 key 是否包含指定的值,返回值为0或1或者是 null,对应的情况为不包含、包含、和指定的key 不存在集中情况:
5、JSON_CONTAINS_PATH 判断 key 在字段中是否存在
JSON_CONTAINS_PATH 判断 key 在字段中是否存在,返回值为0和1,格式为 JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...),如果为 one 的时候,代表 path 中只要有一个在就为1,反之为0;如果为 all 的话必须全部包含,返回为1,反之为0:
6、JSON_LENGTH 长度查询
JSON_LENGTH 是用来计算 JSON 字段的长度:
7、JSON_DEPTH 层级查询
JSON_DEPTH 这个主要是用来查询 Json 字段的层级,空的为1,所以正常情况下 Json 字段类型不为空,这个值的查询出的结果应该大于等于2:
8、JSON_TYPE 查询 Json 字段中 key 值的数据类型
JSON_TYPE 主要用于查询 Json 字段类型中 key 值的数据类型,显示的类型按照分类如下:
Purely JSON types:
OBJECT: JSON objects;ARRAY: JSON arrays;BOOLEAN: The JSON true and false literals;NULL: The JSON null literal
Numeric types:
INTEGER: MySQL TINYINT, SMALLINT, MEDIUMINT and INT and BIGINT scalars;DOUBLE: MySQL DOUBLE FLOAT scalars;DECIMAL: MySQL DECIMAL and NUMERIC scalars
Temporal types:
DATETIME: MySQL DATETIME and TIMESTAMP scalars;DATE: MySQL DATE scalars;TIME: MySQL TIME scalars
String types:
STRING: MySQL utf8 character type scalars;CHAR, VARCHAR, TEXT, ENUM, and SET
Binary types:
BLOB: MySQL binary type scalars;BINARY, VARBINARY, BLOB;BIT: MySQL BIT scalars
All other types:
OPAQUE (raw bits)
9、JSON_UNQUOTE 去掉前后特殊字符
JSON_UNQUOTE 主要是用来去掉前后的特殊字符,格式化值,目前支持一下几种字符: