11.5 JSON 数据类型

• 创建 JSON 值

• JSON 值的规范化，合并和自动包装

• 搜索和修改 JSON 值

• JSON 路径语法

• JSON 值的比较和排序

• 在 JSON 和非 JSON 值之间转换

• JSON 值的汇总

从 MySQL 5.7.8 开始，MySQL 支持由RFC 7159定义的本机JSON数据类型，该类型可有效访问 JSON(JavaScript 对象 table 示法)文档中的数据。与将 JSON 格式的字符串存储在字符串列中相比，JSON数据类型具有以下优点：

• 自动验证存储在JSON列中的 JSON 文档。无效的文档会产生错误。

• 优化的存储格式。存储在JSON列中的 JSON 文档将转换为内部格式，从而可以快速读取文档元素。当服务器稍后必须读取以该二进制格式存储的 JSON 值时，则无需从文本 table 示形式解析该值。二进制格式的结构使服务器可以直接通过键或数组索引查找子对象或嵌套值，而无需读取文档中它们之前或之后的所有值。

存储JSON文档所需的空间与LONGBLOB或LONGTEXT大致相同；有关更多信息，请参见第 11.7 节“数据类型存储要求”。重要的是要记住，存储在JSON列中的任何 JSON 文档的大小都限于max_allowed_packet系统变量的值。 (当服务器内部在内存中操作 JSON 值时，该值可以大于此值；该限制在服务器存储时适用.)

JSON列不能具有非NULL的默认值。

除JSON数据类型外，还有一组 SQL 函数可用于启用对 JSON 值的操作，例如创建，操作和搜索。以下讨论显示了这些操作的示例。有关各个功能的详细信息，请参见第 12.17 节“ JSON 函数”。

还提供了一组用于处理 GeoJSON 值的空间函数。参见第 12.16.11 节，“空间 GeoJSON 函数”。

JSON列与其他二进制类型的列一样，不会直接构建索引；而是相反，您可以在生成的列上创建索引，该索引从JSON列中提取标量值。有关详细示例，请参见索引生成的列以提供 JSON 列索引。

MySQL 优化器还在与 JSONtable 达式匹配的虚拟列上寻找兼容的索引。

MySQL NDB Cluster 7.5(7.5.2 及更高版本)支持JSON列和 MySQL JSON 函数，包括在从JSON列生成的列上创建索引，以作为无法对JSON列构建索引的解决方法。每个NDBtable 最多支持 3 JSON列。

接下来的几节提供有关 JSON 值的创建和操作的基本信息。

创建 JSON 值

JSON 数组包含用逗号分隔并包含在[和]字符内的值的列 table：

["abc", 10, null, true, false]

JSON 对象包含一组键值对，以逗号分隔并包含在{和}字符内：

{"k1": "value", "k2": 10}

如示例所示，JSON 数组和对象可以包含字符串或数字的标量值，JSON 空 Literals 或 JSON 布尔值 true 或 falseLiterals。 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.17.2 节“创建 JSON 值的函数”)，以及使用CAST(值为 JSON)将其他类型的值强制转换为JSON类型(请参见在 JSON 和非 JSON 值之间转换)。接下来的几段描述 MySQL 如何处理作为 Importing 提供的 JSON 值。

在 MySQL 中，JSON 值被编写为字符串。 MySQL 会解析在需要 JSON 值的上下文中使用的任何字符串，如果该字符串作为 JSON 无效，则会产生错误。这些上下文包括将值插入具有JSON数据类型的列中，以及将参数传递给需要 JSON 值的函数(在 MySQL JSON 函数的文档中通常显示为* json_doc 或 json_val *)，如下所示示例说明：

• 如果值是有效的 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的子集.)

作为使用 Literals 字符串编写 JSON 值的替代方法，存在一些用于从组件元素组成 JSON 值的函数。 JSON_ARRAY()接受(可能为空)值列 table，并返回包含这些值的 JSON 数组：

mysql> SELECT JSON_ARRAY('a', 1, NOW());
+----------------------------------------+
| JSON_ARRAY('a', 1, NOW())              |
+----------------------------------------+
| ["a", 1, "2015-07-27 09:43:47.000000"] |
+----------------------------------------+

JSON_OBJECT()提取键值对的列 table(可能为空)，并返回包含这些对的 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 值相同的字符集和排序规则，但* 4 *并不具有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 null，true和falseLiterals，它们必须始终以小写形式编写：

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'.

JSONLiterals 的区分大小写不同于 SQL NULL，TRUE和FALSELiterals，区分大小写：

mysql> SELECT ISNULL(null), ISNULL(Null), ISNULL(NULL);
+--------------+--------------+--------------+
| ISNULL(null) | ISNULL(Null) | ISNULL(NULL) |
+--------------+--------------+--------------+
|            1 |            1 |            1 |
+--------------+--------------+--------------+

有时可能需要或希望将引号字符("或')插入 JSON 文档。假设在此示例中，您想要在 table 中插入使用以下语句创建的 table 中的一些 JSON 对象，这些对象包含 table 示有关 MySQL 的某些事实的语句，每个语句都与一个适当的关键字配对，这些语句 table 示有关 MySQL 的某些事实：

mysql> CREATE TABLE facts (sentence JSON);

这些关键字句子对中的一个是：

mascot: The MySQL mascot is a dolphin named "Sakila".

将其作为 JSON 对象插入factstable 的一种方法是使用 MySQL JSON_OBJECT()函数。在这种情况下，必须使用反斜杠对每个引号字符进行转义，如下所示：

mysql> INSERT INTO facts VALUES
     >   (JSON_OBJECT("mascot", "Our mascot is a dolphin named \"Sakila\"."));

如果将值作为 JSON 对象 Literals 插入，则此方法无法以相同的方式工作，在这种情况下，必须使用双反斜杠转义序列，如下所示：

mysql> INSERT INTO facts VALUES
     >   ('{"mascot": "Our mascot is a dolphin named \\"Sakila\\"."}');

使用双反斜杠使 MySQL 无法执行转义序列处理，而是使它将字符串 Literals 传递给存储引擎进行处理。以上述两种方式插入 JSON 对象后，通过执行简单的SELECT，您可以看到反斜杠出现在 JSON 列值中，如下所示：

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". |
+-----------------------------------------+

mysql> INSERT INTO facts VALUES
> (JSON_OBJECT('mascot', 'Our mascot is a dolphin named "Sakila".'));

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"}                           |
+------------------------------------------------------+

MySQL 还会在原始 JSON 文档中的键，值或元素之间舍弃多余的空格，并在显示时在每个逗号(,)或冒号(:)后留一个空格(或在必要时插入)。这样做是为了提高可读性。

产生 JSON 值的 MySQL 函数(请参阅第 12.17.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 路径 table 达式选择 JSON 文档中的值。

路径 table 达式对于提取 JSON 文档的一部分或修改 JSON 文档以指定在该文档中的何处进行操作的函数很有用。例如，以下查询使用name键从 JSON 文档中提取成员的值：

mysql> SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
+---------------------------------------------------------+
| JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |
+---------------------------------------------------------+
| "Aztalan"                                               |
+---------------------------------------------------------+

路径语法使用前导$字符 table 示所考虑的 JSON 文档，还可以选择后面跟有 selectors，这些 selectors 依次指示文档的更具体部分：

• 句点后跟一个键名，使用给定的键为对象中的成员命名。如果在路径 table 达式中不带引号的名称不合法(例如，如果包含空格)，则必须在双引号中指定键名称。

• [N]附加到* path (用于选择数组)后，将该值命名为数组中 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]]

Then:

• $[0]等于3。

• $[1]等于{"a": [5, 6], "b": 10}。

• $[2]等于[99, 100]。

• $[3]的计算结果为NULL(它指向第四个数组元素，该元素不存在)。

因为$[1]和$[2]的计算结果为非标量值，所以它们可以用作选择嵌套值的更特定路径 table 达式的基础。例子：

• $[1].a等于[5, 6]。

• $[1].a[1]等于6。

• $[1].b等于10。

• $[2][0]等于99。

如前所述，如果未加引号的键名在路径 table 达式中不合法，则必须用引号命名路径的组件。让$引用此值：

{"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 及更高版本中，您可以将column->path与 JSON 列标识符和 JSON 路径 table 达式一起用作JSON_EXTRACT(column, path)的同义词。有关更多信息，请参见第 12.17.3 节“搜索 JSON 值的函数”。另请参见索引生成的列以提供 JSON 列索引。

某些函数采用现有的 JSON 文档，以某种方式对其进行修改，然后返回生成的修改后的文档。路径 table 达式指示在文档中的何处进行更改。例如，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]的第一个实例匹配b元素中的false并将其删除。

• $[1].b[1]的第二个实例不匹配任何内容：该元素已被删除，该路径不再存在，并且无效。

JSON 路径语法

MySQL 支持的许多 JSON 函数以及本手册的其他部分(请参见第 12.17 节“ JSON 函数”)中都介绍了这些函数，它们需要路径 table 达式才能标识 JSON 文档中的特定元素。路径由路径的范围和一个或多个路径分支组成。对于 MySQL JSON 函数中使用的路径，范围始终是要搜索或对其进行操作的文档，并以前导$字符 table 示。路径分支由句点字符(.)分隔。数组中的单元格由[N]table 示，其中* N *是非负整数。键名必须为双引号字符串或有效的 ECMAScript 标识符(请参见http://www.ecma-international.org/ecma-262/5.1/#sec-7.6)。路径 table 达式(如 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 中，路径的范围始终是要对其进行操作的文档，table 示为$。您可以使用'$'作为 JSON 路径 table 达式中文档的同义词。

通配符*和**令牌的用法如下：

• .*代 table 对象中所有成员的值。

• [*]table 示数组中所有单元格的值。

• [prefix]**suffix代 table 所有以* prefix 开始并以 suffix *结尾的路径。 * prefix 是可选的，而 suffix *是必需的；换句话说，路径可能不会以**结尾。

此外，路径可能不包含序列***。

有关路径语法的示例，请参见以路径作为参数的各种 JSON 函数的描述，例如JSON_CONTAINS_PATH()，JSON_SET()和JSON_REPLACE()。有关包括使用*和**通配符的示例，请参见JSON_SEARCH()函数的描述。

JSON 值的比较和排序

可以使用=，<，<=，>，>=，<>，!=和<=>运算符比较 JSON 值。

JSON 值尚不支持以下比较运算符和函数：

• BETWEEN

• IN()

• GREATEST()

• LEAST()

上面列出的比较运算符和函数的解决方法是将 JSON 值转换为本地 MySQL 数字或字符串数据类型，以便它们具有一致的非 JSON 标量类型。

JSON 值的比较分为两个级别。比较的第一级基于比较值的 JSON 类型。如果类型不同，则比较结果仅由优先级更高的类型确定。如果两个值具有相同的 JSON 类型，则使用特定于类型的规则进行第二级比较。

以下列 table 显示了 JSON 类型的优先级，从最高优先级到最低优先级。 (类型名称是JSON_TYPE()函数返回的类型。)一起显示在一行上的类型具有相同的优先级。列 table 前面列出的 JSON 类型的任何值的比较都比列 table 后面列出的 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

table 示较早时间点的值先于 table 示较晚时间点的值排序。如果两个值最初分别来自 MySQL DATETIME和TIMESTAMP类型，则如果它们 table 示相同的时间点，则它们相等。

• TIME

两个时间值中较小的一个先于较大的一个。

• DATE

较早的日期在较新的日期之前排序。

• ARRAY

如果两个 JSON 数组的长度相同并且数组中对应位置的值相等，则它们相等。

如果数组不相等，则它们的 Sequences 由存在差异的第一个位置的元素确定。在该位置具有较小值的数组将首先排序。如果较短数组的所有值都等于较长数组中的相应值，则首先对较短数组进行排序。

Example:

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

• BOOLEAN

JSON 错误 Literals 小于 JSON 真实 Literals。

• OBJECT

如果两个 JSON 对象具有相同的键集，并且两个键中的每个键具有相同的值，则它们相等。

Example:

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

两个对象不相等的 Sequences 是不确定的，而是确定性的。

• STRING

字符串在要比较的两个字符串的utf8mb4table 示形式的前* N 个字节上按词法排序，其中 N 是较短字符串的长度。如果两个字符串的前 N *字节相同，则较短的字符串被认为小于较长的字符串。

Example:

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

此排序等效于排序规则为utf8mb4_bin的 SQL 字符串的排序。因为utf8mb4_bin是二进制排序规则，所以 JSON 值的比较区分大小写：

"A" < "a"

• INTEGER , DOUBLE

JSON 值可以包含精确值数字和近似值数字。有关这些类型的数字的一般讨论，请参见第 9.1.2 节“数字 Literals”。

在第 12.2 节“table 达式评估中的类型转换”中讨论了比较本机 MySQL 数字类型的规则，但是在 JSON 值内比较数字的规则有些不同：

• 在分别使用本机 MySQL INT和DOUBLE数值类型的两列之间的比较中，众所周知，所有比较都涉及一个整数和一个双精度数，因此对于所有行，该整数都将转换为双精度数。即，将精确值数字转换为近似值数字。

  • 另一方面，如果查询比较两个包含数字的 JSON 列，则无法事先知道数字是整数还是双精度。为了在所有行中提供最一致的行为，MySQL 将近似值数字转换为精确值数字。所得的 Sequences 是一致的，并且不会丢失精确值数字的精度。例如，给定标量 9223372036854775805、9223372036854775806、9223372036854775807 和 9.223372036854776e18，Sequences 如下：

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

如果是 JSON 比较以使用非 JSON 数字比较规则，则可能会出现不一致的 Sequences。常用的 MySQL 数字比较规则产生以下 Sequences：

• Integer comparison:

9223372036854775805 < 9223372036854775806 < 9223372036854775807

(未为 9.223372036854776e18 定义)

• Double comparison:

9223372036854775805 = 9223372036854775806 = 9223372036854775807 = 9.223372036854776e18

为了将任何 JSON 值与 SQL NULL进行比较，结果为UNKNOWN。

为了比较 JSON 和非 JSON 值，将根据下 table 中的规则将非 JSON 值转换为 JSON，然后按照前面所述比较这些值。

在 JSON 和非 JSON 值之间转换

下 table 概述了在 JSON 值和其他类型的值之间进行转换时 MySQL 遵循的规则：

table11.3 JSON 转换规则

JSON 值的ORDER BY和GROUP BY按照以下原则工作：

• 标量 JSON 值的排序使用与前面的讨论相同的规则。

• 对于升序排序，SQL NULL在所有 JSON 值(包括 JSON 空 Literals)之前进行排序；对于降序排序，SQL NULL对所有 JSON 值(包括 JSON 空 Literals)进行排序。

• JSON 值的排序键受max_sort_length系统变量的值限制，因此仅在前max_sort_length个字节之后才不同的键比较相等。

• 当前不支持对非标量值进行排序，并且会出现警告。

对于排序，将 JSON 标量转换为其他一些本机 MySQL 类型可能是有益的。例如，如果名为jdoc的列包含 JSON 对象，其成员由id键和一个非负值组成，则使用此 table 达式按id值进行排序：

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

如果碰巧有一个生成的列被定义为使用与ORDER BY中相同的 table 达式，则 MySQL 优化器将识别出该列，并考虑将索引用于查询执行计划。参见第 8.3.10 节，“优化使用生成的列索引”。

JSON 值的汇总

对于 JSON 值的汇总，与其他数据类型一样，将忽略 SQL NULL值。非NULL值将转换为数字类型并进行汇总，但MIN()，MAX()和GROUP_CONCAT()除外。尽管可能会发生截断和精度损失，但转换为数字应该为数字标量的 JSON 值产生有意义的结果。转换为其他 JSON 值的数量可能不会产生有意义的结果。