在MySQL 5.7.8中,MySQL支持原生JSON 数据类型,使有效的数据访问在JSON(JavaScript对象符号)的文件。该 JSON数据类型提供了这些优点存储JSON格式的字符串,字符串中的列:
自动验证存储在JSON文件 JSON列。无效的文件产生错误。
优化的存储格式。存储在JSON文件 JSON列转换为内部格式,允许以文档元素快速读取权限。当服务器后来必须读取存储在此的二进制格式的JSON值,该值不必从一个文本表示解析。二进制格式的结构,以使服务器查找子对象或嵌套值直接通过按键或数组索引没有之前或之后他们在文件中读取所有的值。
注意
本讨论使用“JSON”的单型来具体说明JSON数据类型和 “JSON”常规字体,表示一般的JSON数据。
存储在JSON文档的大小的JSON 列被限制为值 max_allowed_packet的系统变量。(当服务器在存储器内部操纵一个JSON值,也可以是较大的;所述规定范围内时,服务器存储它)。
JSON列不能有默认值。
JSON列不能被索引。您可以通过创建一个生成列提取从一个标值的指数变通解决此限制JSON 列。见 辅助索引和虚拟生成的列,对于一个具体的例子。
下面的讨论包括以下主题:
随着JSON数据类型,一组SQL函数可用来对JSON的值,如创建,操作和搜索启用的操作。后续的讨论示出了这些操作的示例。各功能的详细信息,请参阅第12.16章节,“JSON功能”。
一组空间功能上GeoJSON值的操作也可以。参见第11年12月15日,“空间GeoJSON功能”。
创建JSON值
一个JSON数组包含在由逗号分隔并括值的列表[and] 字符:
["abc", 10, null, true, false]
一个JSON对象包含了一组内由逗号分隔并括键/值对{and}字符:
{"k1": "value", "k2": 10}
作为例子说明,JSON数组和对象可以包含标量值是字符串或数字,JSON null文本,或者JSON布尔真或假的文字。在JSON对象键必须是字符串。时间(日期,时间或日期)标值也是允许的:
["12:18:29.000000", "2015-07-29", "2015-07-29 12:18:29.000000"]
嵌套是允许的JSON数组元素和JSON对象键值中:
[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]
{"k1": "value", "k2": [10, 20]}
您也可以从一些由MySQL为这个目的而提供的功能得到JSON值(见 第12.16.2,“创造JSON值的函数”)以及由铸造其他类型的值的JSON使用类型 CAST(价值 AS JSON)(见 JSON和非JSON的值之间转换)。在接下来的几个段落描述的MySQL如何处理作为输入提供的JSON值。
在MySQL中,JSON值写为字符串。MySQL的解析在上下文需要一个JSON值使用的任何串,并产生一个错误,如果它不是有效的为JSON。这些环境包括插入值,即有一列 JSON数据类型,并传递一个参数传递给函数需要一个JSON值,如下面的例子演示:
试图将值插入一个JSON 列成功,如果值是一个有效的JSON值,但未能如果不是:
mysql>
CREATE TABLE t1 (jdoc JSON);
Query OK, 0 rows affected (0.20 sec)
mysql>
INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');
Query OK, 1 row affected (0.01 sec)
mysql>
INSERT INTO t1 VALUES('[1, 2,');
ERROR 3140 (22032) at line 2: Invalid JSON text: "Invalid value." at position 6 in value (or column) '[1, 2,'.
对于位置“ 的位置 ñ”在这样的错误信息是从零开始的,但应该算是其中在价值问题实际发生粗糙的迹象。
该JSON_TYPE()函数需要一个JSON的说法,并试图解析成JSON值。它返回值的JSON类型,如果它是有效的否则会产生一个错误:
mysql>
SELECT JSON_TYPE('["a", "b", 1]');
+----------------------------+
| JSON_TYPE('["a", "b", 1]') |
+----------------------------+
| ARRAY |
+----------------------------+
mysql>
SELECT JSON_TYPE('"hello"');
+----------------------+
| JSON_TYPE('"hello"') |
+----------------------+
| STRING |
+----------------------+
mysql>
SELECT JSON_TYPE('hello');
ERROR 3146 (22032): Invalid data type for JSON data in argument 1
to function json_type; a JSON string or JSON type is required.
MySQL使用了在JSON上下文中使用字符串 utf8mb4字符集和 utf8mb4_bin整理。在其他的字符集字符串转换为utf8mb4是必要的。(对于字符串ASCII或 UTF-8字符集,则不需要转换,因为ASCII和UTF8的子集utf8mb4。)
作为替代使用文字字符串写入JSON值,函数组成的组成元素的JSON值存在。JSON_ARRAY()取值的(可能为空)列表,并返回一个包含这些值的JSON数组:
mysql>
SELECT JSON_ARRAY('a', 1, NOW());
+----------------------------------------+
| JSON_ARRAY('a', 1, NOW()) |
+----------------------------------------+
| ["a", 1, "2015-07-27 09:43:47.000000"] |
+----------------------------------------+
JSON_OBJECT()采用键/值对(可能为空)列表,并返回一个包含这些对一个JSON对象:
mysql>
SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');
+---------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc') |
+---------------------------------------+
| {"key1": 1, "key2": "abc"} |
+---------------------------------------+
JSON_MERGE()需要两个或更多的JSON文件,并返回综合作用的结果:
mysql>
SELECT JSON_MERGE('["a", 1]', '{"key": "value"}');
+--------------------------------------------+
| JSON_MERGE('["a", 1]', '{"key": "value"}') |
+--------------------------------------------+
| ["a", 1, {"key": "value"}] |
+--------------------------------------------+
有关合并规则的信息,请参阅 规范化,合并和JSON价值Autowrapping。
可以JSON值分配到用户定义的变量:
mysql>
SET @j = JSON_OBJECT('key', 'value');
mysql>
SELECT @j;
+------------------+
| @j |
+------------------+
| {"key": "value"} |
+------------------+
然而,用户定义的变量不能是 JSON数据类型,所以虽然 @j在前面的例子中看起来像一个JSON值并具有作为一个JSON值相同的字符集和校对规则,它不具备 JSON数据类型。相反,结果从 JSON_OBJECT()被转换为字符串时分配给变量。
通过将JSON值所产生的字符串有一个字符集utf8mb4和排序规则 utf8mb4_bin:
mysql>
SELECT CHARSET(@j), COLLATION(@j);
+-------------+---------------+
| CHARSET(@j) | COLLATION(@j) |
+-------------+---------------+
| utf8mb4 | utf8mb4_bin |
+-------------+---------------+
因为utf8mb4_bin是一个二进制排序,JSON值的比较是区分大小写。
mysql>
SELECT JSON_ARRAY('x') = JSON_ARRAY('X');
+-----------------------------------+
| JSON_ARRAY('x') = JSON_ARRAY('X') |
+-----------------------------------+
| 0 |
+-----------------------------------+
区分大小写也适用于JSON 空,真和 假的文字,它总是必须写成小写:
mysql>
SELECT JSON_VALID('null'), JSON_VALID('Null'), JSON_VALID('NULL');
+--------------------+--------------------+--------------------+
| JSON_VALID('null') | JSON_VALID('Null') | JSON_VALID('NULL') |
+--------------------+--------------------+--------------------+
| 1 | 0 | 0 |
+--------------------+--------------------+--------------------+
mysql>
SELECT CAST('null' AS JSON);
+----------------------+
| CAST('null' AS JSON) |
+----------------------+
| null |
+----------------------+
1 row in set (0.00 sec)
mysql>
SELECT CAST('NULL' AS JSON);
ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast_as_json:
"Invalid value." at position 0 in 'NULL'.
JSON的文本中区分大小写不同于SQL的NULL,TRUE和 FALSE文字,它可以用任何大小写:
mysql>
SELECT ISNULL(null), ISNULL(Null), ISNULL(NULL);
+--------------+--------------+--------------+
| ISNULL(null) | ISNULL(Null) | ISNULL(NULL) |
+--------------+--------------+--------------+
| 1 | 1 | 1 |
+--------------+--------------+--------------+
规范化,合并JSON价值Autowrapping,和
当一个字符串进行分析,发现是一个有效的JSON文件,也归:会员与重复前面的文档中找到被丢弃(即使值不同)的关键钥匙。通过以下产生的对象值 JSON_OBJECT()调用不包括第二个键1,因为键名先前在值出现的元素:
mysql>
SELECT JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def');
+------------------------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def') |
+------------------------------------------------------+
| {"key1": 1, "key2": "abc"} |
+------------------------------------------------------+
由MySQL执行的规范化也排序的JSON对象的键(让查找更高效的目的)。这个排序的结果是随时更改,不保证在发布一致。此外,在原始文档中键,值或元素之间多余的空格被丢弃。
产生的JSON值MySQL的函数(请参见 第12.16.2“功能,创造JSON值”)总是返回的标准值。
在上下文,结合多个阵列,该阵列由串联后来被命名为第一个阵列的高端阵列合并成一个单一的阵列。在下面的例子中 ,JSON_MERGE()合并其参数为一个数组:
mysql>
SELECT JSON_MERGE('[1, 2]', '["a", "b"]', '[true, false]');
+-----------------------------------------------------+
| JSON_MERGE('[1, 2]', '["a", "b"]', '[true, false]') |
+-----------------------------------------------------+
| [1, 2, "a", "b", true, false] |
+-----------------------------------------------------+
当合并多个对象产生一个单一的对象。如果多个对象具有相同的密钥,在合并后的对象键的值是一个包含键值的数组:
mysql>
SELECT JSON_MERGE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}');
+----------------------------------------------------+
| JSON_MERGE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}') |
+----------------------------------------------------+
| {"a": [1, 4], "b": 2, "c": 3} |
+----------------------------------------------------+
在上下文中,需要一个数组值用于非数组值autowrapped:该值是由包围[ 和]的字符将其转换成一个数组。在下面的语句,每个参数autowrapped作为数组([1],[2])。这些都是然后合并,以产生一个结果数组:
mysql>
SELECT JSON_MERGE('1', '2');
+----------------------+
| JSON_MERGE('1', '2') |
+----------------------+
| [1, 2] |
+----------------------+
数组和对象的值合并由autowrapping的对象数组和合并两个数组:
mysql>
SELECT JSON_MERGE('[10, 20]', '{"a": "x", "b": "y"}');
+------------------------------------------------+
| JSON_MERGE('[10, 20]', '{"a": "x", "b": "y"}') |
+------------------------------------------------+
| [10, 20, {"a": "x", "b": "y"}] |
+------------------------------------------------+
搜索和修改的JSON值
一个JSON路径表达式选择一个JSON文档内的值。
路径表达式是与提取部分或修改一个JSON文件,到指定范围内的文件进行操作的功能非常有用。例如,下面的查询从一个JSON文件中提取与该成员的值 名称键:
mysql>
SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
+---------------------------------------------------------+
| JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |
+---------------------------------------------------------+
| "Aztalan" |
+---------------------------------------------------------+
路径语法采用了领先的$字符代表正在审议的JSON文档,后面可以选择,表明该文件的相继更具体的部分:
一个时期后跟一个键名名称中的对象与给定键的成员。密钥名必须用双引号来指定,如果不加引号的名字是不是路径表达式中是合法的(例如,如果它包含空格)。
[Ñ]附加到其选择的阵列名称在位置的值的路径 Ñ阵列内。阵列位置是整数,从零开始。
路径可以包含*或 **通配符:
[*]计算为一个JSON对象中的所有成员的值。
[*]的计算结果为在JSON数组所有元素的值。
前缀 ** 后缀 的计算结果为开头命名的前缀和结尾的后缀名为所有路径。
未在文件中存在的路径(计算结果为不存在的数据)的计算结果为NULL。
让$参照本JSON数组包含三个元素:
[3, {"a": [5, 6], "b": 10}, [99, 100]]
然后:
$ [0]的计算结果为3。
$ [1]的计算结果为{“一”:[5,6],“b”的:10}。
$ [2]的计算结果为[99,100]。
$ [3]的计算结果为空 (它指的是第四数组元素,其中不存在)。
因为$ [1]和$ [2] 计算为非标量的值,它们可以作为依据该选择嵌套值的更多特定的路径表达式。例如:
$ [1]。一种计算结果为[5,6]。
$ [1]。一种[1]的计算结果为 6。
$ [1] .B计算结果为 10。
$ [2] [0]的计算结果为 99。
正如前面提到的,命名键路径组件必须,如果未加引号的键名是不合法的路径表达式被引用。让$参考这个值:
{"a fish": "shark", "a bird": "sparrow"}
既包含空格,必须用引号引起来的钥匙:
$。“一条鱼”的计算结果为 鲨鱼。
$,“鸟”的计算结果为 麻雀。
使用通配符的路径,来评估可以包含多个值的数组:
mysql>
SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*');
+---------------------------------------------------------+
| JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*') |
+---------------------------------------------------------+
| [1, 2, [3, 4, 5]] |
+---------------------------------------------------------+
mysql>
SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]');
+------------------------------------------------------------+
| JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]') |
+------------------------------------------------------------+
| [3, 4, 5] |
+------------------------------------------------------------+
在下面的例子中,路径。$ ** b 的计算结果为多个路径($ .ab和 $ .CB)并且产生匹配的路径值的数组:
mysql>
SELECT JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');
+---------------------------------------------------------+
| JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b') |
+---------------------------------------------------------+
| [1, 2] |
+---------------------------------------------------------+
在MySQL 5.7.9或更高版本,可以使用 列 - > 路径 用JSON列标识符和JSON路径表达式作为一个同义词 JSON_EXTRACT(列, 路径 )。见 第12.16.3,“功能该搜索的JSON值”,以获取更多信息。另见 辅助索引和虚拟生成的列。
有些功能利用现有的JSON文件,修改它以某种方式,并返回得到修改的文档。路径表达式显示在文档中进行更改。例如 ,JSON_SET(),JSON_INSERT(),和 JSON_REPLACE()各功能采取JSON文档,以及一个或多个路径/值对,描述其中修改文档和要使用的值。功能不同,他们是如何在文档中处理现有的和不存在的价值。
审议本文件:
mysql> SET @j = '["a", {"b": [true, false]}, [10, 20]]';
JSON_SET()替换值存在路径和为不存在的路径增加值:.
mysql>
SELECT JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+--------------------------------------------+
| JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+--------------------------------------------+
| ["a", {"b": [1, false]}, [10, 20, 2]] |
+--------------------------------------------+
在这种情况下,路径$ [1] .B [0]选择现有值(真),其被替换为路径参数(以下的值1)。路径$ [2] [2]不存在,所以相应的值(2)加入到所选择的值 $ [2]。
JSON_INSERT()增加了新的值,但不会取代现有的值:
mysql>
SELECT JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+-----------------------------------------------+
| JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+-----------------------------------------------+
| ["a", {"b": [true, false]}, [10, 20, 2]] |
+-----------------------------------------------+
JSON_REPLACE()替换现有的值,并忽略新值:
mysql>
SELECT JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+------------------------------------------------+
| JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+------------------------------------------------+
| ["a", {"b": [1, false]}, [10, 20]] |
+------------------------------------------------+
路径/值对被评估从左到右。通过评估一对产生的文件成为针对其下一对被评估的新值。
JSON_REMOVE()需要一个JSON文档和一个或多个路径指定的值被从文档移除。返回值是原始文件减去文件中存在的路径选择的值:
mysql>
SELECT JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]');
+---------------------------------------------------+
| JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]') |
+---------------------------------------------------+
| ["a", {"b": [true]}] |
+---------------------------------------------------+
该路径有以下影响:
$ [2]匹配 [10,20],并删除它。
的第一个实例$ [1] .B [1]匹配 虚假的b元素并将其删除。
第二个实例$ [1] .B [1]匹配什么:该元素已被移除,道路不再存在,并且没有任何效果。
比较和排序JSON价值
JSON值可使用进行比较 =,<,<=,>,> =,<>,!=和 <=> 运营商。
下面的比较操作和功能尚不支持JSON值:
之间
在()
GREATEST()
最小()
一种解决方法对于刚刚上市的比较操作符和函数是投JSON值到本机MySQL的数字或字符串数据类型,使他们有一个一致的非JSON标量型。
JSON值的比较发生在两个层面。比较的第一级是根据被比较的值的JSON类型。如果类型不同,则比较结果单独确定由类型具有更高的优先级。如果这两个值具有相同的JSON类型,发生使用型特异性的规则比较的第二级。
下面的列表显示JSON类型的优先级,从最高优先级到最低。(类型名是那些由返回JSON_TYPE() 函数。)一行共同显示类型具有相同的优先级。有前面的列表中列出的JSON类型的任何值进行比较,超过其在后面的列表中列出的JSON类型的任意值。
BLOB
BIT
OPAQUE
DATETIME
TIME
DATE
BOOLEAN
ARRAY
OBJECT
STRING
INTEGER, DOUBLE
NULL
对于优先级相同的JSON值,比较规则是特定类型:
BLOB第一Ñ两个值的字节相比较,其中Ñ是较短值中的字节数。如果第一 Ñ两个值中的字节是相同的,较短的值的时间越长值之前排序。
BIT相同的规则BLOB。
不透明相同的规则的BLOB。 乳浊值是不属于其它类型之一的值。
日期时间它表示一个较早的时间点A值是表示在以后某个时间点的值之前排序。如果两个值最初来自MySQL的 DATETIME和TIMESTAMP 类型,分别为,他们是平等的,如果他们代表相同的时间点。
时间两个时间值越小之前较大的一个排序。
日期早期的日期更近的日期之前订购。
ARRAY两个JSON阵列相等,如果它们有在对应于所述阵列的位置相同的长度和值相等。
如果阵列是不相等的,它们的顺序是由在其中是有区别的第一位置中的元素确定的。与在该位置中的较小值的阵列一阶。如果短阵列的所有值都等于较长阵列中的相应值,较短的阵列一阶。
例:
[] < ["a"] < ["ab"] < ["ab", "cd", "ef"] < ["ab", "ef"]
布尔JSON的错误文字小于JSON真正的文字。
目的两个JSON对象相等,如果它们具有相同的密钥集合,并且每个键有两个对象相同的值。
例:
{"a": 1, "b": 2} = {"b": 2, "a": 1}
两个对象中不等于顺序不确定但确定性。
串串是词法在第一有序 Ñ的字节 utf8mb4两个字符串的表示被比较,其中Ñ是较短的字符串的长度。如果第一 Ñ两个字符串的字节是相同的,在较短的字符串被认为比较长的字符串小。
例:
"a" < "ab" < "b" < "bc"
此排序相当于SQL字符串中使用归类排序utf8mb4_bin。因为 utf8mb4_bin是一个二进制排序,JSON值的比较是区分大小写:
"A" < "a"
INTEGER,DOUBLEJSON值可以包含准确值数值和近似值数值。对于这些类型的数字的一般讨论,请参见第9.1.2节,“数字字面”。
比较原生的MySQL数值类型的规则在讨论第12.2节,“在表达式求值类型转换”,但对于JSON值范围内比较数的规则有所不同:
在使用天然的MySQL两列之间的比较的INT和 双数字类型,分别已知的是所有的比较涉及一个整数和一个双重的,所以整数转换为加倍所有行。也就是说,准确值数值转换为近似值数值。
另一方面,如果查询比较包含数字2的JSON列,它不能被事先已知的数字是否是整数或双。为客户提供横跨所有行的最一致的行为,MySQL的近似值数值转换为准确值数值。由此产生的顺序是一致的,不会丢失精度的准确值数值。例如,给定的标量9223372036854775805,9223372036854775806,9223372036854775807和9.223372036854776e18,顺序是如此:
9223372036854775805 < 9223372036854775806 < 9223372036854775807
< 9.223372036854776e18 = 9223372036854776000 < 9223372036854776001
是JSON的比较使用非JSON数值比较规则,可能会出现不一致的排序。对数字通常MySQL的比较规则产生这些排序:
整数比较:
9223372036854775805 < 9223372036854775806 < 9223372036854775807
(没有定义9.223372036854776e18)
双重对比:
9223372036854775805 = 9223372036854775806 = 9223372036854775807 = 9.223372036854776e18
对于任何一个JSON值的SQL比较空,结果是未知的。
对于JSON和非JSON值的比较,所述非JSON值根据如先前所描述的相比,下表中的规则,那么这些值转换为JSON。
JSON和非JSON的值之间的转换。 下表提供的JSON值和其他类型的值之间的铸造时的MySQL遵循规则的摘要:
表11.1 JSON转换规则
其他类型 CAST(其它类型为JSON) CAST(JSON作为其他类型的)
JSON 不用找了 不用找了
utf8字符类型 (utf8mb4,UTF8,ASCII) 该字符串被解析成一个JSON值。 JSON的值序列化为utf8mb4字符串。
其他字符类型 其它字符编码的隐式转换为 utf8mb4和所描述的UTF-8字符类型处理。 JSON的值序列化为utf8mb4字符串,然后转换为其他字符编码。其结果可能没有意义。
空值 结果在一个NULL类型的JSON值。 不适用。
几何类型 几何值是通过调用转换成JSON文件 ST_AsGeoJSON()。 非法操作。解决方法:通过的结果 CAST(json_val AS CHAR)到 ST_GeomFromGeoJSON()。
所有其他类型 结果在一个JSON文件由单个标量值。 成功,如果JSON文件包括目标类型的单个标值和标量值可以转换为目标类型。否则,返回NULL 并产生警告。
ORDER BY和GROUP BY为JSON值是根据这些原则的工作原理:
订购标JSON值使用相同的规则在前面的讨论。
对于升序排序,SQL NULL命令所有的JSON值,包括JSON null文本之前; 为降序排序,SQL NULL命令所有的JSON值后,包括JSON null文本。
排序键JSON值由值约束 的max_sort_length系统变量,因此,只有后的第一个不同的密钥 max_sort_length个字节的比较结果相等。
非标量数值排序目前不支持,并发出警告。
对于排序,也可以是有益的投JSON标到一些其他本地MySQL的类型。例如,如果一个名为列 jdoc包含具有由一个成员JSON对象ID键和一个非负值,用这个表达排序ID 值:
ORDER BY CAST(JSON_EXTRACT(jdoc, '$.id') AS UNSIGNED)
如果恰好是定义为使用相同的表达式作为生成列ORDER BY,MySQL 优化认识到并考虑使用索引的查询执行计划。见 第8.3.9,“优化器使用的生成列的索引”。
JSON值的聚合
对于JSON值的聚合,SQL NULL 值被忽略为其它数据类型。非NULL值转换为数值型和汇总,除了 MIN(),MAX(),和 GROUP_CONCAT()。转换为数字应该产生一个有意义的结果对JSON值是数字标量,虽然(取决于值)截断和精度可能会发生损失。转化为其它的JSON值数目可能无法产生一个有意义的结果。