MySQL5.7新增数据类型 - JSON类型

 提示:转载请注明原文链接

 本文永久链接:https://360us.net/article/48.html

算是对MySQL官方文档对JSON数据类型描述的一个笔记吧!

官方介绍在这里:http://dev.mysql.com/doc/refman/5.7/en/json.html

从MySQL 5.7.8开始,MySQL新增了对JSON数据结构的原生支持。

JSON数据类型相对于存储JSON格式的字符串的一些优势:

1、JSON列会自动验证格式的正确性,错误的JSON会报错。

2、优化的存储格式。存储的JSON文档会转换成一个内部的格式,以提供对文档元素的快速访问,当需要访问JSON的某个值得时候,不需要从一个文本里面去解析出这个值。这个二进制结构允许直接查找子对象或者通过key查找嵌套的值,或者是在不需要读取整个数组的情况下查找数组索引。


JSON数据类型的最大存储大小是通过系统变量max_allowed_packet来限制的。

JSON列不可以有默认值。

JSON列不可以被索引。这个限制可以通过生成虚拟列的方法解决,链接:http://dev.mysql.com/doc/refman/5.7/en/create-table.html#create-table-secondary-indexes-virtual-columns

MySQL处理JSON类型列的字符编码是utf8mb4mb4,校对规则是utf8mb4mb4_bin。


插入JSON类型的值

创建一个测试表:

CREATE TABLE `t_json` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `j` json NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4mb4;

插入几条数据:

INSERT INTO `t_json` (`j`) VALUES ('["abc", 10, null, true, false]'),('{"k1": "value", "k2": 10}'),('["12:18:29.000000", "2015-07-29", "2015-07-29 12:18:29.000000"]'),('[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]'),('{"k1": "value", "k2": [10, 20]}');

在MySQL里面,JSON值是通过一个字符串来写入的。无效的JSON将会报错。

1452939364938691.png

另一种插入JSON数据的方式是通过内置函数。

1452939492167985.png

有JSON_ARRAY,JSON_OBJECT、JSON_MERGE(合并两个或多个JSON文档):

1452939526786637.png

JSON值是大小写敏感的,a和A是两个不同的字符。

同时对于类型null,true和false的字符也是大小写敏感的:

1452939551344957.png


搜索和修改

通过路径表达式来从一个JSON文档里面查出一个值。

路径语法是最前面一个$字符,代表查询的整个JSON文档,后面是可选的选择器,表示特定部分,如果你只写一个$的话,是会查询出整个JSON来的。

1452939970474881.png

1452939748426262.png

1、一个点(.)后面跟着key的名字,用来查找对象的成员。假如key里面有空白字符,这个key必须用双引号包裹起来。

1452939774691545.png


2、数组的话用[N],N表示要查找元素在数组中的位置,从0开始。

1452939796304113.png

3、可以用*和**通配符:

  • .*匹配对象的所有成员。

  • [*]匹配数组的所有元素。

  • prefix**suffix匹配所有以prefix开头,以suffix结尾的路径

例如:

[3, {"a": [5, 6], "b": 10}, [99, 100]]

$[0]是3;

$[1]是{"a": [5, 6], "b": 10};

$[2]是[99, 100];

$[3]是NULL,因为没有这个元素。

1452940159952975.png

$[1]和$[2]是非标量数据,可以在里面再查找更具体的值:

如$[1].a[1]查找出来的值是6。

1452940170666880.png


$**.b匹配$.a.b和$.c.b。

1452940183421279.png


JSON_SET()存在的话会替换,不存在的会添加;

1452940219886521.png

JSON_REPLACE()替换值,但是会忽略新值;

1452940251517693.png

JSON_REMOVE()移除一个或多个指定的值;

1452940262842469.png

$[2]匹配[10,20],第一个$[1].b[1]匹配false,第二个$[1].b[1]在前面已经被移除了,这里就是不存在的。


比较和排序

JSON的值可以用=, <, <=, >, >=, <>, !=, 和 <=>去比较。

BETWEEN、IN()、GREATEST()、LEAST()还不支持JSON值类型。

JSON值得比较是根据值的类型来的,如果类型不同,则根据类型的优先级来决定,如果相同的话就需要用特殊的规则的判断了。

JSON值优先顺序如下,从高到低排列(以下类型是调用JSON_TYPE()函数返回的类型名字):

BLOB
BIT
OPAQUE
DATETIME
TIME
DATE
BOOLEAN
ARRAY
OBJECT
STRING
INTEGER, DOUBLE
NULL

在同一行的大小相同,任何JSON值类型在前面的总是大于类型位于后面。

优先顺序相同的,使用特殊规则,如下(下面说的排在前面是指从小到大的顺序排序,小的值在前面):

BLOB

两个值得前N个字节比较,N是值较短的字节总数。如果前N字节都是相同的,那么值短的排在长的前面。


BIT

规则同BLOB。


OPAQUE

规则同BLOB。OPAQUE值是不能归入其他类型的值。


DATETIME

更早的时间排在更晚的时间前面。如果两个值是从DATETIME和TIMESTAMP类型转换来的,如果时间点相同的话,他们就是相等的。


TIME

小的排在大的前面


DATE

早的日期排在更近的日期前面


ARRAY

两个数组,如果长度相同并且对应位置上面的值也是相等的,那么这两数组就是相等的。

如果是不相等的,排序取决于他们第一个不同的元素,值更小的值的数组排在前面。

如果较短数组的所有值都和较长数组里面对应位置的值相等,那么较短的数组排在前面。

比如:

[] < ["a"] < ["ab"] < ["ab", "cd", "ef"] < ["ab", "ef"]

BOOLEAN

false小于true。


OBJECT

有相同的key并且每个key的值也是相同的,两个JSON对象就是相等的。

比如:

{"a": 1, "b": 2} = {"b": 2, "a": 1}

两个对象的排序是不明确的,但是不相等的话是明确的。


STRING

两个字符串前N字节相等的话,较短的字符串较小。

例如:

"a" < "ab" < "b" < "bc"

JSON字符串是区分大小写的:"A" < "a"。


INTEGER, DOUBLE

两列原生的MySQL INT和DOUBLE类型相比较,INT会被转换成DOUBLE。

如果是比较两列包含数字的JSON类型值,程序并不知道数字是int型还是double型。

为了在所有行提供一致的行为,MySQL会把近似的数字转换成精确的数字。

例如:

9223372036854775805 < 9223372036854775806 < 9223372036854775807 < 9.223372036854776e18 = 9223372036854776000 < 9223372036854776001

任何JSON值和NULL比较,结果都是UNKNOWN。


JSON值和非JSON值相比较,非JSON值会根据一定的规则转换成JSON值,然后根据上面说的规则去比较。

转换规则如下:

other typeCAST(other type AS JSON)CAST(JSON AS other type)
JSON 没有改变没有改变
utf8mb4字符(包括utf8mb4mb4,utf8mb4和ascii)字符串直接解析成JSON值JSON值序列化成utf8mb4mb4字符串
其他字符编码会隐含的转换成utf8mb4mb4,被当作是utf8mb4字符序列化成utf8mb4mb4字符串,然后再转成其他字符编码,结果可能没有意义
NULL结果是JSON的值类型NULL不适用
地理位置类型(Geometry types)通过函数ST_AsGeoJSON()转换成JSON文档

非法操作,解决办法是将CAST(json_val AS CHAR)的结果传给ST_GeomFromGeoJSON()

所有其他类型  由单个标量类型组成的JSON文档 如果JSON只含一个标量类型值,并且这个值可以转换成目标类型,则会成功,否则会会返回NULL,并且会产生一个警告

ORDER BY和GROUP BY JSON值有其他规则:

对于标量JSON值得排序适用上面的的规则。

对于升序排序(ASC),NULL排在所有其他JSON值前面,包括JSON null字符,对于降序排序(DESC)则正好想法。

更具key来排序的话,会和系统变量max_sort_length有关,所以,max_sort_length字节之后的key的部分相比较都是相等的,不管是否相同。

当前不支持非标量值的排序,这样做的话会产生一个警告。


对于排序,转换JSON标量数据到其他MySQL数据类型是有帮助的。

例如,有一个名字叫jdoc的列,列的值是key为id,值为非负数的JSON对象,可以通过id的值来排序:

ORDER BY CAST(JSON_EXTRACT(jdoc, '$.id') AS UNSIGNED)

如果恰巧在ORDER BY子句里面对生成列使用了同样的语句,MySQL优化器可以分辨出来并且执行使用了索引的查询计划。


聚合JSON值

NULL会被忽略,非NULL值都会转换成数值类型,然后聚合,用 MIN(), MAX(),  GROUP_CONCAT()的话则不会。

这个转换应该得到一个有意义的结果,即使是丢失精度,就是说只对JSON的数值标量类型数据做这个操作。


 评论
prime 2016-08-10 14:42:44
感觉你博客风格挺不错的,Sharks 有开源吗? :)
dyllen 2016-08-02 23:30:31
这本来是一月份写的,后面换了服务器,从四月份的备份里面恢复的发现这篇不见了。
昵称
邮箱
网址
最多500个字符