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

 发布日期： 2016-08-02 23:25:07    分类： MySQL    浏览量： 5572    标签： 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的数值标量类型数据做这个操作。


 评论
暂无评论
 同类热门
 随机推荐