Documentation Home
MySQL 8.3 Reference Manual
Related Documentation
MySQL 8.3 Release Notes
Download this Manual
PDF (US Ltr) - 40.8Mb
PDF (A4) - 40.9Mb
Man Pages (TGZ) - 294.0Kb
Man Pages (Zip) - 409.0Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb
Excerpts from this Manual
MySQL Backup and Recovery
MySQL NDB Cluster 8.3
MySQL Globalization
MySQL Information Schema
MySQL Installation Guide
MySQL and Linux/Unix
MySQL and macOS
MySQL Partitioning
MySQL Performance Schema
MySQL Replication
Using the MySQL Yum Repository
MySQL Restrictions and Limitations
Security in MySQL
MySQL and Solaris
Building MySQL from Source
Starting and Stopping MySQL
MySQL Tutorial
MySQL and Windows


MySQL 8.3 Reference Manual  /  Data Types  /  The JSON Data Type

13.5 JSON 数据类型

MySQL 支持原生 JSON 数据类型，定义于 RFC 7159，使得可以高效地访问 JSON（JavaScript 对象表示）文档中的数据。 JSON 数据类型提供了以下优点，相比于在字符串列中存储 JSON 格式的字符串：

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

  • 优化的存储格式。存储在 JSON 列中的 JSON 文档将被转换为内部格式，以便快速读取文档元素。当服务器后续需要读取存储在这种二进制格式中的 JSON 值时，无需从文本表示中解析该值。该二进制格式结构化以便服务器可以直接通过键或数组索引查找文档中的子对象或嵌套值，而不需要读取文档中的所有值。

MySQL 8.3 还支持 JSON 合并补丁 格式，定义于 RFC 7396，使用 JSON_MERGE_PATCH() 函数。请参阅该函数的描述，以及 标准化、合并和自动包装 JSON 值，以获取示例和更多信息。

Note

本讨论使用 JSON 单体字体表示特定的 JSON 数据类型，并使用 “JSON” 正常字体表示一般的 JSON 数据。

存储 JSON 文档所需的空间大致相同于 LONGBLOB 或 LONGTEXT；请参阅 第 13.7 节，“数据类型存储要求”，以获取更多信息。请注意，存储在 JSON 列中的任何 JSON 文档的大小都受到 max_allowed_packet 系统变量值的限制。（当服务器在内存中操作 JSON 值时，可以大于这个限制；该限制适用于服务器存储时。）您可以使用 JSON_STORAGE_SIZE() 函数来获取存储 JSON 文档所需的空间大小；请注意，对于 JSON 列，存储大小——因此该函数返回的值——是该列在进行部分更新之前的存储大小（请参阅本节后面的 JSON 部分更新优化讨论）。

除了 JSON 数据类型外，还有一组 SQL 函数可用于操作 JSON 值，例如创建、操作和搜索。以下讨论将展示这些操作的示例。有关每个函数的详细信息，请参阅 第 14.17 节，“JSON 函数”。

还有一组空间函数可用于操作 GeoJSON 值。请参阅 第 14.16.11 节，“空间 GeoJSON 函数”。

JSON 列，如同其他二进制类型的列，不是直接索引的；相反，您可以在生成的列上创建索引，该列从 JSON 列中提取标量值。请参阅 在生成的列上创建索引以提供 JSON 列索引，以获取详细示例。

MySQL 优化器还会查找虚拟列上的兼容索引，以匹配 JSON 表达式。

InnoDB 存储引擎支持多值索引在 JSON 数组上。见 多值索引。

MySQL NDB Cluster 支持 JSON 列和 MySQL JSON 函数，包括从 JSON 列生成的列作为无法索引 JSON 列的解决方法。每个 NDB 表最多支持 3 个 JSON 列。

JSON 值的部分更新

在 MySQL 8.3 中，优化器可以对 JSON 列执行部分、就地更新，而不是删除旧文档并将新文档写入列中。这项优化可以在满足以下条件的更新中执行：

  • 被更新的列被声明为 JSON。

  • UPDATE 语句使用任何三个函数 JSON_SET()、JSON_REPLACE() 或 JSON_REMOVE() 来更新列。直接分配列值（例如，UPDATE mytable SET jcol = '{"a": 10, "b": 25}'）不能作为部分更新。

    可以对单个 UPDATE 语句中的多个 JSON 列执行部分更新；MySQL 可以对使用这三个函数更新的列执行部分更新。

  • 输入列和目标列必须是相同的列；语句 UPDATE mytable SET jcol1 = JSON_SET(jcol2, '$.a', 100) 不能作为部分更新。

    更新可以使用任何三个函数的嵌套调用，以任何组合，前提是输入和目标列是相同的。

  • 所有更改都将现有的数组或对象值替换为新的值，而不添加任何新的元素到父对象或数组中。

  • 被替换的值必须至少与替换值一样大。换言之，新值不能比旧值大。

    一个可能的例外情况是，之前的部分更新留下了足够的空间以容纳较大的值。你可以使用函数 JSON_STORAGE_FREE() 查看任何部分更新的 JSON 列中释放了多少空间。

这些部分更新可以使用紧凑的格式写入二进制日志，以节省空间；这可以通过设置 binlog_row_value_options 系统变量为 PARTIAL_JSON 来启用。

重要的是要区分表中的 JSON 列值的部分更新和将部分更新写入二进制日志之间的区别。可能会将完整的 JSON 列更新记录到二进制日志中作为部分更新。这可能发生在最后两个条件不满足但其他条件满足的情况下。

见 also binlog_row_value_options 的描述。

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

创建 JSON 值

JSON 数组包含以逗号分隔的值，括在 [ 和 ] 字符之间：

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

JSON 对象包含以逗号分隔的键值对，括在 { 和 } 字符之间：

{"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 值（见 第 14.17.2 节，“创建 JSON 值的函数”），以及通过使用 CAST(值 AS JSON) 将其他类型的值转换为 JSON 类型（见 在 JSON 和非 JSON 值之间转换）。接下来的几段描述了 MySQL 如何处理作为输入的 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,'.

    在这些错误消息中的“at position N”是 0-based 的，但应该被认为是值中问题的 rough 指示。

  • 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 或 utf8mb3 字符集的字符串，不需要转换，因为 ascii 和 utf8mb3 是 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_PRESERVE() 接受两个或多个 JSON 文档，并返回组合的结果：

mysql> SELECT JSON_MERGE_PRESERVE('["a", 1]', '{"key": "value"}');
+-----------------------------------------------------+
| JSON_MERGE_PRESERVE('["a", 1]', '{"key": "value"}') |
+-----------------------------------------------------+
| ["a", 1, {"key": "value"}]                          |
+-----------------------------------------------------+
1 row in set (0.00 sec)

有关合并规则的信息，请参阅 JSON 值的标准化、合并和自动包装。

(MySQL 还支持 JSON_MERGE_PATCH()，它具有略有不同的行为。请参阅 JSON_MERGE_PATCH() 与 JSON_MERGE_PRESERVE() 的比较，以获取这两个函数之间的差异信息。)

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

JSON 字面量的区分大小写不同于 SQL NULL、TRUE 和 FALSE 字面量，它们可以以任何字母大小写形式书写：

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

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

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 执行转义序列处理，而是将字符串文字传递给存储引擎进行处理。然后，您可以使用简单的 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". |
+-----------------------------------------+
Note

如果启用了 NO_BACKSLASH_ESCAPES 服务器 SQL 模式，则前面的示例不起作用。在这种情况下，可以使用单个反斜杠而不是双反斜杠来插入 JSON 对象文字，并且反斜杠将被保留。如果您使用 JSON_OBJECT() 函数来执行插入操作，并且启用了该模式，则必须交替使用单引号和双引号，如下所示：

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

有关该模式对 JSON 值中转义字符的影响的更多信息，请参阅 JSON_UNQUOTE() 函数的描述。

JSON 值的标准化、合并和自动包装

当字符串被解析并被发现是一个有效的 JSON 文档时，它也将被标准化。这意味着文档中从左到右读取的成员键的重复键将被丢弃。使用以下 JSON_OBJECT() 调用生成的对象值仅包含第二个 key1 元素，因为该键名称出现在值的早期，如下所示：

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def');
+------------------------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def') |
+------------------------------------------------------+
| {"key1": "def", "key2": "abc"}                       |
+------------------------------------------------------+

标准化也在将值插入 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": "red"}     |
| {"x": [3, 5, 7]} |
+------------------+

这种 “最后一个重复键获胜”” 行为是由 RFC 7159 建议的，并且是大多数 JavaScript 解析器实现的。（Bug #86866, Bug #26369555）

MySQL 会丢弃原始 JSON 文档中的额外空白字符，并在显示时添加或插入单个空格，以增强可读性。

MySQL 生成 JSON 值的函数（参见 第 14.17.2 节，“生成 JSON 值的函数”）总是返回标准化的值。

为了提高查找效率，MySQL 还会对 JSON 对象的键进行排序。您应该注意，结果的顺序可能会更改，并且不保证在版本之间的一致性。

合并 JSON 值

两种合并算法被支持，分别由函数 JSON_MERGE_PRESERVE() 和 JSON_MERGE_PATCH() 实现。它们在处理重复键时有所不同：JSON_MERGE_PRESERVE() 保留重复键的值，而 JSON_MERGE_PATCH() 丢弃所有除最后一个值以外的值。下面几段落解释了每个函数如何处理不同组合的 JSON 文档（即对象和数组）的合并。

合并数组。 在合并多个数组的上下文中，数组被合并成一个单个数组。JSON_MERGE_PRESERVE() 通过将后续数组连接到第一个数组的末尾来实现此操作。JSON_MERGE_PATCH() 将每个参数视为一个单元素数组（因此索引为 0），然后应用“最后一个重复键获胜”逻辑来选择最后一个参数。你可以比较以下查询的结果：

mysql> SELECT
    ->   JSON_MERGE_PRESERVE('[1, 2]', '["a", "b", "c"]', '[true, false]') AS Preserve,
    ->   JSON_MERGE_PATCH('[1, 2]', '["a", "b", "c"]', '[true, false]') AS Patch\G
*************************** 1. row ***************************
Preserve: [1, 2, "a", "b", "c", true, false]
   Patch: [true, false]

多个对象在合并时生成一个单个对象。JSON_MERGE_PRESERVE() 处理具有相同键的多个对象时，通过将所有唯一值组合成一个数组来处理该键；然后将该数组用作结果中的该键的值。JSON_MERGE_PATCH() 丢弃具有重复键的值，从左到右，以便结果仅包含最后一个值。以下查询说明了键 a 的重复键的结果差异：

mysql> SELECT
    ->   JSON_MERGE_PRESERVE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}') AS Preserve,
    ->   JSON_MERGE_PATCH('{"a": 3, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}') AS Patch\G
*************************** 1. row ***************************
Preserve: {"a": [1, 4], "b": 2, "c": [3, 5], "d": 3}
   Patch: {"a": 4, "b": 2, "c": 5, "d": 3}

在需要数组值的上下文中使用的非数组值将被自动包装：该值将被 [ 和 ] 字符包围，以将其转换为数组。在以下语句中，每个参数都被自动包装为数组 ([1]、[2])。然后，它们被合并以生成单个结果数组；在前两个案例中，JSON_MERGE_PRESERVE() 结合具有相同键的值，而 JSON_MERGE_PATCH() 丢弃所有重复键的值，除了最后一个，如下所示：

mysql> SELECT
	  ->   JSON_MERGE_PRESERVE('1', '2') AS Preserve,
	  ->   JSON_MERGE_PATCH('1', '2') AS Patch\G
*************************** 1. row ***************************
Preserve: [1, 2]
   Patch: 2

数组和对象值通过将对象自动包装为数组并合并数组来合并，方法是结合具有相同键的值或根据选择的合并函数 (JSON_MERGE_PRESERVE() 或 JSON_MERGE_PATCH()，分别），如下所示：

mysql> SELECT
	  ->   JSON_MERGE_PRESERVE('[10, 20]', '{"a": "x", "b": "y"}') AS Preserve,
	  ->   JSON_MERGE_PATCH('[10, 20]', '{"a": "x", "b": "y"}') AS Patch\G
*************************** 1. row ***************************
Preserve: [10, 20, {"a": "x", "b": "y"}]
   Patch: {"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] 附加到选择数组的路径，指定数组中的值位于位置 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)

  • [M to N] 指定数组值的子集或范围，开始于位置 M，结束于位置 N。

    last 作为右侧数组元素的索引的同义词支持。数组元素的相对寻址也支持。如果 path 不选择数组值，则 path[last] 评估为与 path 相同的值，如本节后面所示（见 右侧数组元素）。

  • 路径可以包含 * 或 ** 通配符：

    • .[*] 评估为 JSON 对象中的所有成员值。

    • [*] 评估为 JSON 数组中的所有元素值。

    • 前缀**后缀 评估为以命名的前缀开始并以命名的后缀结束的所有路径。

  • 文档中不存在的路径（评估为不存在的数据）评估为 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]                                                  |
+---------------------------------------------------------+

JSON 数组的范围。 您可以使用范围和 to 关键字指定 JSON 数组的子集。例如，$[1 to 3] 包括数组的第二、第三和第四个元素，如下所示：

mysql> SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]');
+----------------------------------------------+
| JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]') |
+----------------------------------------------+
| [2, 3, 4]                                    |
+----------------------------------------------+
1 row in set (0.00 sec)

语法是 M to N，其中 M 和 N 分别是数组元素范围的第一个和最后一个索引。 N 必须大于 M；M 必须大于或等于 0。数组元素从 0 开始索引。

您可以在支持通配符的上下文中使用范围。

数组的右most 元素。 last 关键字作为数组最后一个元素的索引的同义词。表达式 last - N 可以用于相对寻址，并在范围定义中使用，如下所示：

mysql> SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[last-3 to last-1]');
+--------------------------------------------------------+
| JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[last-3 to last-1]') |
+--------------------------------------------------------+
| [2, 3, 4]                                              |
+--------------------------------------------------------+
1 row in set (0.01 sec)

如果路径评估的值不是数组，则评估结果与将值包装在单个元素数组中的结果相同：

mysql> SELECT JSON_REPLACE('"Sakila"', '$[last]', 10);
+-----------------------------------------+
| JSON_REPLACE('"Sakila"', '$[last]', 10) |
+-----------------------------------------+
| 10                                      |
+-----------------------------------------+
1 row in set (0.00 sec)

您可以使用 column->path 与 JSON 列标识符和 JSON 路径表达式作为 JSON_EXTRACT(column, path) 的同义词。请参阅 第 14.17.3 节，“搜索 JSON 值的函数”，以获取更多信息。请参阅 为 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] 选择了一个现有的值 (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] 实例匹配 nothing：该元素已经被删除，路径不再存在，且无效果。

JSON 路径语法

许多 MySQL 支持的 JSON 函数（见 第 14.17 节，“JSON 函数”）需要路径表达式来标识 JSON 文档中的特定元素。路径由范围和一个或多个路径腿组成。对于 MySQL JSON 函数，范围始终是被搜索或操作的文档，表示为leading $ 字符。路径腿用点字符 (.) 分隔。数组单元格用 [N] 表示，其中 N 是非负整数。键名必须是双引号字符串或有效的 ECMAScript 标识符（见 标识符名称和标识符，在 ECMAScript 语言规范 中）。路径表达式，像 JSON 文本一样，应该使用 ascii、utf8mb3 或 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 路径表达式中的文档同义词。

Note

一些实现支持列引用作为 JSON 路径的范围；MySQL 8.3 不支持这些。

通配符 * 和 ** 按照以下方式使用：

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

  • [*] 代表数组的所有单元格值。

  • [prefix]**suffix 代表所有以 prefix 开头和以 suffix 结尾的路径。prefix 是可选的，而 suffix 是必需的；换言之，路径不能以 ** 结尾。

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

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

MySQL 还支持使用 to 关键字（例如 $[2 to 10]）和 last 关键字作为数组右侧元素的同义词。请参阅 搜索和修改 JSON 值，以获取更多信息和示例。

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

    较早的时间点排在较晚的时间点之前。如果两个值最初来自 MySQL DATETIME 和 TIMESTAMP 类型，分别，它们将在表示相同时间点时相等。

  • TIME

    较小的时间值排在较大的时间值之前。

  • DATE

    较早的日期排在较晚的日期之前。

  • ARRAY

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

    如果数组不相等，它们的顺序将由第一个不同位置的元素确定。具有较小值的数组将排在第一位。如果较短数组的所有值都等于较长数组的对应值，则较短数组将排在第一位。

    示例：

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

  • BOOLEAN

    JSON false 文字小于 JSON true 文字。

  • OBJECT

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

    示例：

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

    两个不相等的对象的顺序未指定，但确定性。

  • STRING

    字符串按字典顺序比较，比较的基础是两个字符串的前 N 字节的 utf8mb4 表示形式，其中 N 是较短字符串的长度。如果两个字符串的前 N 字节相同，则较短字符串被认为小于较长字符串。

    示例：

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

    这种顺序等同于 SQL 字符串的 utf8mb4_bin 排序。因为 utf8mb4_bin 是二进制排序，所以 JSON 值的比较是区分大小写的：

    "A" < "a"

  • INTEGER、 DOUBLE

    JSON 值可以包含精确值数字和近似值数字。有关这些数字类型的通用讨论，请参阅 第 11.1.2 节，“数字字面量”。

    本机 MySQL 数值类型的比较规则在 第 14.3 节，“表达式评估中的类型转换” 中讨论，但 JSON 值中的数字比较规则略有不同：

    • 在比较两个使用本机 MySQL INT 和 DOUBLE 数值类型的列时，知道所有比较都涉及整数和双精度浮点数，因此整数被转换为双精度浮点数以便比较所有行。也就是说，精确值数字被转换为近似值数字。

    • 另一方面，如果查询比较两个 JSON 列中的数字，无法预先知道数字是整数还是双精度浮点数。为了提供所有行的一致行为，MySQL 将近似值数字转换为精确值数字。结果排序是一致的，不会损失精确值数字的精度。例如，给定标量 9223372036854775805、9223372036854775806、9223372036854775807 和 9.223372036854776e18，排序如下：

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

    如果 JSON 比较使用非 JSON 数值比较规则，可能会出现不一致的排序。

    • 整数比较：

      9223372036854775805 < 9223372036854775806 < 9223372036854775807

      (未定义 9.223372036854776e18)

    • 双精度浮点数比较：

      9223372036854775805 = 9223372036854775806 = 9223372036854775807 = 9.223372036854776e18

比较任何 JSON 值与 SQL NULL 时，结果是 UNKNOWN。

比较 JSON 和非 JSON 值时，非 JSON 值根据以下表中的规则转换为 JSON，然后按照前面描述的方式比较。

在 JSON 和非 JSON 值之间转换

以下表格总结了 MySQL 在将 JSON 值和其他类型值之间转换时遵循的规则：

表 13.3 JSON 转换规则

other type CAST(other type AS JSON) CAST(JSON AS other type)
JSON 无变化 无变化
utf8 character type (utf8mb4, utf8mb3, ascii) 字符串被解析为 JSON 值。 JSON 值被序列化为 utf8mb4 字符串。
Other character types 其他字符编码隐式转换为 utf8mb4 并按照该字符类型的描述处理。 JSON 值被序列化为 utf8mb4 字符串，然后转换为其他字符编码。结果可能不具意义。
NULL 结果为 NULL 值的 JSON 类型。 不适用。
Geometry types 几何值被转换为 JSON 文档，通过调用 ST_AsGeoJSON()。 非法操作。解决方法：将 CAST(json_val AS CHAR) 的结果传递给 ST_GeomFromGeoJSON()。
All other types 结果为单个标量值的 JSON 文档。 如果 JSON 文档由单个标量值组成，并且该标量值可以转换为目标类型，则成功。否则，返回 NULL 并产生警告。

ORDER BY 和 GROUP BY 对 JSON 值的工作原理如下：

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

对于升序排序，SQL NULL 排序在所有 JSON 值之前，包括 JSON 空字面量；对于降序排序，SQL NULL 排序在所有 JSON 值之后，包括 JSON 空字面量。

ORDER BY and GROUP BY for JSON values works according to these principles:

  • Ordering of scalar JSON values uses the same rules as in the preceding discussion.

  • For ascending sorts, SQL NULL orders before all JSON values, including the JSON null literal; for descending sorts, SQL NULL orders after all JSON values, including the JSON null literal.

  • 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 优化器将识别出这一点，并考虑在查询执行计划中使用索引。请参阅 第 10.3.11 节，“优化器使用生成列索引”。

JSON 值聚合

对于 JSON 值的聚合，SQL NULL 值将被忽略，就像其他数据类型一样。非 NULL 值将被转换为数字类型并聚合，除了 MIN()、MAX() 和 GROUP_CONCAT()。将 JSON 值转换为数字应该对数字标量 JSON 值产生有意义的结果，尽管（取决于值）可能会出现截断和精度损失。将其他 JSON 值转换为数字可能不会产生有意义的结果。


PREV   HOME   UP   NEXT
Related Documentation
MySQL 8.3 Release Notes
Download this Manual
PDF (US Ltr) - 40.8Mb
PDF (A4) - 40.9Mb
Man Pages (TGZ) - 294.0Kb
Man Pages (Zip) - 409.0Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb
Excerpts from this Manual
MySQL Backup and Recovery
MySQL NDB Cluster 8.3
MySQL Globalization
MySQL Information Schema
MySQL Installation Guide
MySQL and Linux/Unix
MySQL and macOS
MySQL Partitioning
MySQL Performance Schema
MySQL Replication
Using the MySQL Yum Repository
MySQL Restrictions and Limitations
Security in MySQL
MySQL and Solaris
Building MySQL from Source
Starting and Stopping MySQL
MySQL Tutorial
MySQL and Windows