算是对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将会报错。
另一种插入JSON数据的方式是通过内置函数。
有JSON_ARRAY,JSON_OBJECT、JSON_MERGE(合并两个或多个JSON文档):
JSON值是大小写敏感的,a和A是两个不同的字符。
同时对于类型null,true和false的字符也是大小写敏感的:
搜索和修改
通过路径表达式来从一个JSON文档里面查出一个值。
路径语法是最前面一个$字符,代表查询的整个JSON文档,后面是可选的选择器,表示特定部分,如果你只写一个$的话,是会查询出整个JSON来的。
1、一个点(.)后面跟着key的名字,用来查找对象的成员。假如key里面有空白字符,这个key必须用双引号包裹起来。
2、数组的话用[N],N表示要查找元素在数组中的位置,从0开始。
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,因为没有这个元素。
$[1]和$[2]是非标量数据,可以在里面再查找更具体的值:
如$[1].a[1]查找出来的值是6。
$**.b匹配$.a.b和$.c.b。
JSON_SET()存在的话会替换,不存在的会添加;
JSON_REPLACE()替换值,但是会忽略新值;
JSON_REMOVE()移除一个或多个指定的值;
$[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 type | CAST(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的数值标量类型数据做这个操作。
本来链接:https://360us.net/article/48.html