JSON 数据类型
从 MySQL 5.7.8 开始,MySQL 支持JSON
由RFC 7159定义的本机数据类型 ,可以有效访问 JSON(JavaScript Object Notation)文档中的数据。该JSON
数据类型提供了这些优点超过存储在字符串列JSON格式的字符串:
- 自动验证存储在
JSON
列中的 JSON 文档 。无效的文档会产生错误。 - 优化的存储格式。存储在
JSON
列中的JSON 文档 被转换为允许快速读取文档元素的内部格式。当服务器稍后必须读取以这种二进制格式存储的 JSON 值时,不需要从文本表示中解析该值。二进制格式的结构使服务器能够直接通过键或数组索引查找子对象或嵌套值,而无需读取文档中它们之前或之后的所有值。
笔记
本次讨论使用JSON
in monotype 来具体表示 JSON 数据类型, 使用常规字体的“ JSON ”来表示一般的 JSON 数据。
存储JSON
文档所需的空间与LONGBLOBor 大致相同LONGTEXT;有关更多信息,请参见 第 11.7 节,“数据类型存储要求”。请务必记住,存储在JSON
列中的任何 JSON 文档的大小都限于max_allowed_packet系统变量的值。(当服务器在内存中内部操作 JSON 值时,它可以大于此值;当服务器存储它时,限制适用。)
一JSON
列不能有非NULL
默认值。
除了JSON
数据类型之外,还有一组 SQL 函数可用于启用对 JSON 值的操作,例如创建、操作和搜索。以下讨论显示了这些操作的示例。有关单个函数的详细信息,请参阅第 12.18 节,“JSON 函数”。
一组用于操作 GeoJSON 值的空间函数也可用。请参阅第 12.17.11 节,“空间 GeoJSON 函数”。
JSON
列与其他二进制类型的列一样,不直接索引;相反,您可以在从列中提取标量值的生成列上创建索引 JSON
。有关详细示例,请参阅 索引生成的列以提供 JSON 列索引。
MySQL 优化器还会在与 JSON 表达式匹配的虚拟列上查找兼容索引。
MySQL NDB Cluster 7.5(7.5.2 及更高版本)支持 JSON
列和 MySQL JSON 函数,包括在从列生成的JSON
列上创建索引 作为无法索引JSON
列的解决方法。JSON
每个NDB表最多支持3 列 。
接下来的几节提供了有关 JSON 值的创建和操作的基本信息。
创建 JSON 值
JSON 数组包含由逗号分隔并包含在[
和]
字符中的值列表:
["abc", 10, null, true, false]
一个 JSON 对象包含一组由逗号分隔并包含在{
和 }
字符中的键值对:
{"k1": "value", "k2": 10}
如示例所示,JSON 数组和对象可以包含字符串或数字形式的标量值、JSON 空字面量或 JSON 布尔值 true 或 false 字面量。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.18.2 节,“创建 JSON 值的函数”)以及通过将其他类型的值转换为JSON
使用的类型 (请参阅 在 JSON 之间转换和非 JSON 值)。接下来的几段描述了 MySQL 如何处理作为输入提供的 JSON 值。 CAST(*value* AS JSON)
在 MySQL 中,JSON 值被写为字符串。MySQL 解析上下文中使用的任何需要 JSON 值的字符串,如果它作为 JSON 无效,则会产生错误。这些上下文包括将值插入到具有JSON
数据类型的列中 并将参数传递给需要 JSON 值的函数(通常显示为json_doc
或 json_val
在 MySQL 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,'. 为位置“在位置N
”在这样的错误消息是基于0的,但应考虑其中一个值问题实际发生的粗指示。- 该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 使用utf8mb4
字符集和utf8mb4_bin
排序规则处理 JSON 上下文中使用的 字符串 。其他字符集中的字符串utf8mb4
根据需要进行转换。(对于ascii
或 utf8
字符集中的字符串,不需要转换,因为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 值的规范化、合并和自动包装。
JSON 值可以分配给用户定义的变量:
mysql> SET @j = JSON_OBJECT('key', 'value'); mysql> SELECT @j; +------------------+ | @j | +------------------+ | {"key": "value"} | +------------------+
但是,用户定义的变量不能是 JSON
数据类型,所以虽然 @j
在前面的例子中看起来像一个JSON值,并且具有相同的字符集并归类为JSON值,但它不具有 JSON
数据类型。相反,结果 from 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 null
、true
和 false
文字,它们必须始终以小写形式编写:
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'.
从该SQL的JSON文字不同的情况下的灵敏度NULL
,TRUE
和 FALSE
文字,它可以在任何大小写被写成:
mysql> SELECT ISNULL(null), ISNULL(Null), ISNULL(NULL); +--------------+--------------+--------------+ | ISNULL(null) | ISNULL(Null) | ISNULL(NULL) | +--------------+--------------+--------------+ | 1 | 1 | 1 | +--------------+--------------+--------------+
有时可能需要或希望将引号字符 ("
或'
) 插入到 JSON 文档中。假设在这个例子中,你想要插入一些包含字符串的 JSON 对象,这些字符串表示陈述一些关于 MySQL 的一些事实的句子,每个与适当的关键字配对,到使用这里显示的 SQL 语句创建的表中:
mysql> CREATE TABLE facts (sentence JSON);
在这些关键字-句子对中,有一个:
mascot: The MySQL mascot is a dolphin named "Sakila".
将其作为 JSON 对象插入facts
表中的一种方法 是使用 MySQL JSON_OBJECT()函数。在这种情况下,您必须使用反斜杠对每个引号字符进行转义,如下所示:
mysql> INSERT INTO facts VALUES > (JSON_OBJECT("mascot", "Our mascot is a dolphin named \"Sakila\"."));
如果您将值作为 JSON 对象文字插入,则这不会以相同的方式工作,在这种情况下,您必须使用双反斜杠转义序列,如下所示:
mysql> INSERT INTO facts VALUES > ('{"mascot": "Our mascot is a dolphin named \\"Sakila\\"."}');
使用双反斜杠可以防止 MySQL 执行转义序列处理,而是使其将字符串文字传递给存储引擎进行处理。以上述任一方式插入 JSON 对象后,您可以通过执行简单的 看到 JSON 列值中存在反斜杠SELECT,如下所示:
mysql> SELECT sentence FROM facts; +---------------------------------------------------------+ | sentence | +---------------------------------------------------------+ | {"mascot": "Our mascot is a dolphin named \"Sakila\"."} | +---------------------------------------------------------+
要查找mascot
用作关键字的特定句子 ,您可以使用列路径运算符 ->,如下所示:
mysql> SELECT col->"$.mascot" FROM qtest; +---------------------------------------------+ | col->"$.mascot" | +---------------------------------------------+ | "Our mascot is a dolphin named \"Sakila\"." | +---------------------------------------------+ 1 row in set (0.00 sec)
这使反斜杠以及周围的引号保持完整。要使用mascot
作为键显示所需的值 ,但不包括周围的引号或任何转义符->>,请使用内联路径运算符 ,如下所示:
mysql> SELECT sentence->>"$.mascot" FROM facts; +-----------------------------------------+ | sentence->>"$.mascot" | +-----------------------------------------+ | Our mascot is a dolphin named "Sakila". | +-----------------------------------------+
笔记
如果NO_BACKSLASH_ESCAPES启用了服务器 SQL 模式,则前面的示例将无法正常工作 。如果设置了此模式,则可以使用单个反斜杠而不是双反斜杠来插入 JSON 对象文字,并保留反斜杠。如果JSON_OBJECT()
在执行插入时使用该函数并且设置了此模式,则必须交替使用单引号和双引号,如下所示:
mysql> INSERT INTO facts VALUES > (JSON_OBJECT('mascot', 'Our mascot is a dolphin named "Sakila".'));
有关JSON_UNQUOTE()此模式对 JSON 值中转义字符的影响的更多信息,请参阅函数说明 。
JSON 值的规范化、合并和自动包装
当一个字符串被解析并被发现是一个有效的 JSON 文档时,它也会被规范化:具有与文档中先前找到的键重复的键的成员将被丢弃(即使值不同)。以下JSON_OBJECT()调用生成的对象值 不包括第二个key1
元素,因为该键名称出现在值中的较早位置:
mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def'); +------------------------------------------------------+ | JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def') | +------------------------------------------------------+ | {"key1": 1, "key2": "abc"} | +------------------------------------------------------+
笔记
这种对重复密钥的“第一个密钥获胜”处理与RFC 7159不一致 。这是 MySQL 5.7 中的一个已知问题,已在 MySQL 8.0 中修复。(错误 #86866,错误 #26369555)
MySQL 还会丢弃原始 JSON 文档中的键、值或元素之间的额外空白,并在显示时在每个逗号 ( ,
) 或冒号 ( :
)后面留下(或在必要时插入)一个空格。这样做是为了提高可读性。
生成 JSON 值的 MySQL 函数(请参阅 第 12.18.2 节,“创建 JSON 值的函数”)始终返回规范化值。
为了提高查找效率,它还对 JSON 对象的键进行排序。您应该知道,此排序的结果可能会发生变化,并且不能保证跨版本保持一致。
合并 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] | +-----------------------------------------------------+
将值插入 JSON 列时也会执行规范化,如下所示:
mysql> CREATE TABLE t1 (c1 JSON); mysql> INSERT INTO t1 VALUES > ('{"x": 17, "x": "red"}'), > ('{"x": 17, "x": "red", "x": [3, 5, 7]}'); mysql> SELECT c1 FROM t1; +-----------+ | c1 | +-----------+ | {"x": 17} | | {"x": 17} | +-----------+
合并后的多个对象会生成一个对象。如果多个对象具有相同的键,则生成的合并对象中该键的值是一个包含键值的数组:
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} | +----------------------------------------------------+
在需要数组值的上下文中使用的非数组值是自动包装的:该值由[
和]
字符包围以将其转换为数组。在以下语句中,每个参数都自动包装为一个数组 ( [1]
, [2]
)。然后将它们合并以生成单个结果数组:
mysql> SELECT JSON_MERGE('1', '2'); +----------------------+ | JSON_MERGE('1', '2') | +----------------------+ | [1, 2] | +----------------------+
通过将对象自动包装为数组并合并两个数组来合并数组和对象值:
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 文档的函数很有用,以指定要在该文档中的哪个位置进行操作。例如,以下查询从 JSON 文档中提取具有name
键的成员的值 :
mysql> SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name'); +---------------------------------------------------------+ | JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') | +---------------------------------------------------------+ | "Aztalan" | +---------------------------------------------------------+
路径语法使用前导$
字符来表示正在考虑的 JSON 文档,可选地后跟选择器,依次指示文档的更具体部分:
- 后跟键名的句点命名具有给定键的对象中的成员。如果不带引号的名称在路径表达式中不合法(例如,如果它包含空格),则必须在双引号内指定键名称。
[*
N*]
附加到path
选择数组的 a 命名数组中位置处的值N
。数组位置是从零开始的整数。如果path
未选择数组值,则path
[0] 的计算结果与 相同path
: mysql> SELECT JSON_SET('"x"', '$[0]', 'a'); +------------------------------+ | JSON_SET('"x"', '$[0]', 'a') | +------------------------------+ | "a" | +------------------------------+ 1 row in set (0.00 sec)- 路径可以包含
*
或**
通配符:
.[*]
计算为 JSON 对象中所有成员的值。[*]
计算 JSON 数组中所有元素的值。*
prefix****
suffix*
计算为所有以命名前缀开头并以命名后缀结尾的路径。
- 文档中不存在的路径(评估为不存在的数据)评估为
NULL
。
让我们$
用三个元素来引用这个 JSON 数组:
[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
评估为[5, 6]
.$[1].a[1]
评估为6
.$[1].b
评估为10
.$[2][0]
评估为99
.
如前所述,如果路径表达式中未加引号的键名称不合法,则必须引用命名键的路径组件。让我们$
参考这个值:
{"a fish": "shark", "a bird": "sparrow"}
键都包含一个空格并且必须被引用:
$."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
计算为多个路径($.a.b
和 $.c.b
)并生成匹配路径值的数组:
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 路径表达式作为 . 有关更多信息,请参阅 第 12.18.3 节,“搜索 JSON 值的函数”。另请参阅索引生成的列以提供 JSON 列索引。 *column*->*path*JSON_EXTRACT(*column*, *path*)
一些函数采用现有的 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]
选择一个现有值 ( true
),该值将替换为路径参数 ( 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]
相匹配false
的b
元素并将其删除。 - 第二个实例不
$[1].b[1]
匹配:该元素已被删除,路径不再存在,且无效。
JSON 路径语法
MySQL 支持并在本手册其他地方描述的许多 JSON 函数(请参阅第 12.18 节,“JSON 函数”)需要路径表达式,以便标识 JSON 文档中的特定元素。路径由路径的范围和一个或多个路径支路组成。对于 MySQL JSON 函数中使用的路径,范围始终是正在搜索或以其他方式操作的文档,由前导$
字符表示 。路径段由句点字符 ( .
)分隔。数组中的单元格由 表示 ,其中 是非负整数。键名必须是双引号字符串或有效的 ECMAScript 标识符(请参阅 [*
N*]
N
http://www.ecma-international.org/ecma-262/5.1/#sec-7.6
)。路径表达式,如JSON文本,应该使用的编码 ascii
,utf8
或 utf8mb4
字符集。其他字符编码被隐式强制为utf8mb4
. 完整的语法如下所示:
pathExpression: scope[(pathLeg)*] pathLeg: member | arrayLocation | doubleAsterisk member: period ( keyName | asterisk ) arrayLocation: leftBracket ( nonNegativeInteger | asterisk ) rightBracket keyName: ESIdentifier | doubleQuotedString doubleAsterisk: '**' period: '.' asterisk: '*' leftBracket: '[' rightBracket: ']'
如前所述,在 MySQL 中,路径的范围始终是正在操作的文档,表示为 $
. 您可以'$'
在 JSON 路径表达式中用作文档的同义词。
笔记
一些实现支持 JSON 路径范围的列引用;目前,MySQL 不支持这些。
通配符*
和**
令牌的使用如下:
.*
表示对象中所有成员的值。[*]
表示数组中所有单元格的值。[*
prefix*]***
suffix*
表示以 开头prefix
和结尾的 所有路径suffix
。prefix
是可选的,whilesuffix
是必需的;换句话说,路径可能不会以**
.
此外,路径可能不包含序列***
。
对于路径语法的例子,见该采取的路径作为参数,例如各种JSON功能的说明 JSON_CONTAINS_PATH(), JSON_SET()和 JSON_REPLACE()。有关使用*
和 **
通配符的示例,请参阅JSON_SEARCH()函数说明 。
JSON 值的比较和排序
JSON值可以使用进行比较 =, <, <=, >, >=, <>, !=,和 <=> 运营商。
JSON 值尚不支持以下比较运算符和函数:
刚刚列出的比较运算符和函数的解决方法是将 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
N
比较两个值 的第一个字节,其中N
是较短值中的字节数。如果N
两个值的第一个 字节相同,则较短的值排在较长的值之前。BIT
与 相同的规则BLOB
。OPAQUE
与 相同的规则BLOB
。OPAQUE
值是未被归类为其他类型之一的值。DATETIME
表示较早时间点的值排在表示较晚时间点的值之前。如果两个值最初 分别来自 MySQLDATETIME
和TIMESTAMP
类型,则如果它们代表相同的时间点,则它们相等。TIME
两个时间值中较小的在较大的之前排序。DATE
较早的日期排在较近的日期之前。ARRAY
如果两个 JSON 数组具有相同的长度并且数组中相应位置的值相等,则它们相等。
如果数组不相等,则它们的顺序由存在差异的第一个位置的元素决定。在该位置具有较小值的数组首先排序。如果较短数组的所有值都等于较长数组中的相应值,则较短数组先排序。
例子: [] < ["a"] < ["ab"] < ["ab", "cd", "ef"] < ["ab", "ef"]BOOLEAN
JSON 假文字小于 JSON 真文字。OBJECT
如果两个 JSON 对象具有相同的键集,并且每个键在两个对象中具有相同的值,则它们是相等的。
例子: {"a": 1, "b": 2} = {"b": 2, "a": 1} 两个不相等的对象的顺序是未指定的,但具有确定性。STRING
字符串在被比较的两个字符串N
的utf8mb4
表示形式的第一个字节 上按词法排序 ,其中N
是较短字符串的长度。如果N
两个字符串的第一个 字节相同,则较短的字符串被认为小于较长的字符串。
例子: "a" < "ab" < "b" < "bc" 此排序等效于使用 collation 对 SQL 字符串进行排序utf8mb4_bin
。因为utf8mb4_bin
是二进制排序规则,JSON 值的比较区分大小写: "A" < "a"INTEGER
,DOUBLE
JSON 值可以包含精确值数字和近似值数字。有关这些类型数字的一般讨论,请参阅第 9.1.2 节,“数字文字”。
比较本机 MySQL 数字类型的规则在第 12.3 节,“表达式评估中的类型转换”中讨论,但比较 JSON 值中的数字的规则有些不同:
- 在分别使用本机 MySQLINT和 DOUBLE数字类型的两列之间的比较中,已知所有比较都涉及一个整数和一个双精度数,因此所有行的整数都转换为双精度数。即,精确值数字被转换为近似值数字。
- 另一方面,如果查询比较两个包含数字的 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 的比较NULL
,结果是UNKNOWN
.
JSON 和非 JSON 值的比较,根据下表中的规则将非 JSON 值转换为 JSON,然后按照前面描述的方法比较值。
在 JSON 和非 JSON 值之间转换
下表总结了 MySQL 在 JSON 值和其他类型的值之间进行转换时遵循的规则:
表 11.3 JSON 转换规则
其他类型 | CAST(其他类型 AS JSON) | CAST(JSON AS 其他类型) |
JSON | 没变化 | 没变化 |
utf8 字符类型 ( | 该字符串被解析为 JSON 值。 | JSON 值被序列化为 |
其他字符类型 | 其他字符编码被隐式转换 | JSON 值被序列化为 |
| 结果 | 不适用。 |
几何类型 | 通过调用将几何值转换为 JSON 文档 ST_AsGeoJSON()。 | 非法操作。解决方法:将结果传递 给 .CAST(*json_val* AS CHAR)ST_GeomFromGeoJSON() |
所有其他类型 | 生成由单个标量值组成的 JSON 文档。 | 如果 JSON 文档由目标类型的单个标量值组成,并且该标量值可以转换为目标类型,则成功。否则,返回 |
ORDER BY
并GROUP BY
为JSON值根据这些原则,工作原理:
- 标量 JSON 值的排序使用与前面讨论相同的规则。
- 对于升序,SQL
NULL
在所有 JSON 值之前排序,包括 JSON 空字面量;对于降序排序,SQLNULL
在所有 JSON 值之后排序,包括 JSON null 文字。 - JSON 值的排序键受max_sort_length系统变量值的约束 ,因此只有在第一个max_sort_length字节比较相等后才不同的键 。
- 当前不支持对非标量值进行排序,并且会出现警告。
对于排序,将 JSON 标量转换为其他一些本机 MySQL 类型可能是有益的。例如,如果名为的列 jdoc
包含具有由id
键和非负值组成的成员的 JSON 对象,请使用此表达式按id
值排序:
ORDER BY CAST(JSON_EXTRACT(jdoc, '$.id') AS UNSIGNED)
如果恰好有一个生成的列被定义为使用与 中相同的表达式ORDER BY
,则 MySQL 优化器会识别出这一点并考虑将索引用于查询执行计划。请参阅 第 8.3.10 节,“生成的列索引的优化器使用”。
JSON 值的聚合
对于 JSON 值的聚合,SQLNULL
值与其他数据类型一样被忽略。非NULL
值被转换为数字类型和聚合,除 MIN(), MAX()和 GROUP_CONCAT()。对于作为数字标量的 JSON 值,转换为数字应该会产生有意义的结果,尽管(取决于值)可能会发生截断和精度损失。转换为许多其他 JSON 值可能不会产生有意义的结果。