JSON 数据类型

概述

MySQL 支持 RFC 7159 定义的原生 JSON 数据类型，该类型实现高效访问 JSON（JavaScript Object Notation）文档中的数据。与存储在字符串列的 JSON 序列化字符串相比，JSON 数据类型具有以下优点：

  • 自动校验存储在 JSON 列中的 JSON 文档。非法文档会报错。

  • 优化存储格式。存储在 JSON 列的 JSON 文档会被转为能快速读取文档元素的内部格式。当服务器读取以二进制格式存储的 JSON 值时，无需再以文本形式解析该值了。二进制格式的结构使得服务器能直接通过键名或数组下标查找子对象或嵌套值，而无需读取它们在文档中的上下文。

MySQL 8.0 还支持 RFC 7396 定义的 JSON Merge Path，可通过 JSON_MERGE_PATH() 函数进行使用。有关该函数的示例和更详尽信息，请阅读 Normalization, Merging, and Autowrapping of JSON Values。

注意：本章节使用 monotype 字体的 JSON 是专门表示 JSON 数据类型的，而使用常规字体的 “JSON” 通常表示 JSON 数据。（译者注：前者用代码标识符 `` 表示）

JSON 文档的存储空间与 LONGBLOB 或 LONGTEXT 列大致相同，详情请查看 Section 11.7, “Data Type Storage Requirements”。请牢记任何存储在 JSON 列的 JSON 文档的大小都受限于 max_allowed_packet 系统变量（当服务器在内存操作一个 JSON 文档时，则不受该值限制。即该值仅限制存储值时）。可通过 JSON_STORAGE_SIZE() 函数获取一个 JSON 文档在 JSON 列存储时所需的空间。

在 MySQL 8.0.13 前，JSON 列不能使用 非 NULL 作为默认值。

对于 JSON 数据类型，有一组 SQL 函数适用于 JSON 值的操作，如创建、操作、搜索。以下通过几个案例展示这些操作。对于每个函数的详情，请查阅 Section 12.18, “JSON Functions”。

JSON 列和其他二进制类型一样，都不能直接索引。然而，你可以从 JSON 列中提取一个标量值作为生成列，然后在生成列上建立索引。详情请查阅 Indexing a Generated Column to Provide a JSON Column Index。

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

在 MySQL 8.0.17 及以上，InnoDB 存储引擎支持 JSON 数组的多值索引，详情请看 Multi-Valued Indexes。

MySQL NDB Cluster 8.0 支持 JSON 列和 MySQL JSON 函数，其中包括基于 JSON 列生成的列上创建索引，从而解决无法索引 JSON 列的问题。每个 NDB 表最多支持 3 个 JSON 列。

部分更新 JSON 值

对于 MySQL 8.0，优化器可以对 JSON 列执行部分就地更新，而不是删除旧文档后再插入新文档。满足以下条件的更新才支持该优化：

  • 更新的是 JSON 列。

  • UPDATE 语句使用以下 3 个函数进行更新：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) 该语句不会执行部分更新。

    只要输入列和目标列相同，上述三个函数以任意组合进行嵌套调用均可实现部分更新。

  • 所有改动都只是用新值替换已有的数组值或对象值，并且未向数组或对象根目录（parent object）添加任何新元素。

  • 新值（所需存储空间）不能比旧值大。

    除非先前部分更新已为较大值预留足够大的空间。你可以使用 JSON_STORAGE_FREE() 函数查看一个 JSON 列被先前部分更新释放出多大空间。

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

以下这段话，译者不理解且翻译不准确，暂可忽略。

区分存储在表中的 JSON 列值的部分更新和将行的部分更新写入二进制日志是很重要的。JSON 列的完整更新可能会作为部分更新记录在二进制日志中。当上述条件的最后两个条件中的一个（或两个）不满足但其他条件满足时，就会发生这种情况。

详情请看 binlog_row_value_options。

创建 JSON 值

JSON 数组：由逗号（,）分隔并用 [ 和 ] 字符包围起来的值列表：

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

JSON 对象：由逗号（,）分隔并用 { 和 } 字符包围起来的键值对：

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

如下所示，JSON 数组和对象可包含字符串和数值等标量值、JSON null 字面量、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 值的函数（查阅 Section 12.18.2, “Functions That Create JSON Values”），并且可通过 CAST(value AS JSON) 将其他类型的值转换为 JSON 类型（查阅 Converting between JSON and non-JSON values）。

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。应将其视为值中发生问题的近似位置。

  • JSON_TYPE() 函数期待一个 JSON 参数，并尝试将其解析为 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 和 utf8 字符集的字符串，无需进行转换，因为它们是 utf8mb4 的子集）。

作为 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)

关于更多合并规则，请查阅 Normalization, Merging, and Autowrapping of JSON Values。

（MySQL 8.0.3 及以上支持 JSON_MERGE_PATCH()，该函数和它有不同，具体有哪些不同，请查阅 JSON_MERGE_PATCH() compared with 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'.

SQL 的 NULL、TRUE 和 FALSE 字面量则不区分大小写：

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

有时候需要在 JSON 文档中插入引号（" 或 '）。下面例子中，假设你想插入一些陈述 MySQL 事实语句的 JSON 对象。创建表的 SQL 语句如下：

mysql> CREATE TABLE facts (sentence JSON);

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 对象后，可通过简单的 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)

以上返回包含了反斜杠和两侧引号。可使用内联路径操作符 ->> 返回不包含反斜杠和两侧引号的值，如下所示：

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 值中转义字符的影响的更多信息，请查阅 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]} |
+------------------+

last duplicate key wins 行为在 RFC 7159 中提出，并在大多数 JavaScript 解析器实现。

在 MySQL 8.0.3 前，对于重复键，则保留键第一次出现的值。所示如下：

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

在 MySQL 8.0.3 前，first duplicate key wins 规范化操作同样会在向 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 还去除原始 JSON 文档中键、值和元素之间的空格。但为了增强可读性，MySQL 会在输出时在每个逗号（,）和冒号（:）后面插入一个空格。

返回 JSON 值的 MySQL 函数（详情请查阅 Section 12.18.2, “Functions That Create JSON Values”）都会返回规范化后的值。

为提高查找效率，MySQL 还对 JSON 对象的键进行排序。但你应该意识到排序结果可能会变，不保证不同版本中保持一致。

合并 JSON 值

MySQL 8.0.3 及以上支持两种合并算法，由两个函数实现：JSON_MERGE_PRESERVE() 和 JSON_MERGE_PATCH()。两者不同之处在于如何处理重复键：JSON_MERGE_PRESERVE() 会保留重复键的值，而 JSON_MERGE_PATCH() 仅保留最后出现的值。接下来几段文字会阐述这两个函数如何处理不同类型 JSON 文档（即对象和数组）之间的合并。

注意： JSON_MERGE_PRESERVE() 与 MySQL 8.0.3 之前的 JSON_MERGE() 表现一致。在 MySQL 8.0 中，JSON_MERGE() 仍会作为 JSON_MERGE_PRESERVE() 别名的形式存在，但已被弃用，并可能在未来的版本中删除。

合并数组。在合并多个数组的上下文中，多个数组会被合并到一个数组。JSON_MERGE_PRESERVE() 会依次将后面出现的数组串联到前一个数组末尾。JSON_MERGE_PATCH() 会将每个参数视为由单个元素组成的数组（因此将 0 作为其索引），然后应用 “last duplicate key wins” 逻辑选择最后一个参数。如下所示：

mysql> SELECT
    ->   JSON_MERGE_PRESERVE('[1, 2, "a"]', '["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", "a", "b", "c", true, false]
   Patch: [true, false]

合并对象。将多个对象合并为一个对象。JSON_MERGE_PRESERVE() 会将多个对象的相同键名的值组合并为一个数组，然后将该数组作为合并对象中该键的值。JSON_MERGE_PATCH() 会按从左到右的次序，保留最后出现的值。示例如下：

mysql> SELECT
    ->   JSON_MERGE_PRESERVE('{"a": 1, "b": 2}', '{"c": 3, "a": 1}', '{"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, 1], "b": 2, "c": [3, 5], "d": 3}
   Patch: {"a": 4, "b": 2, "c": 5, "d": 3}

合并非数组值。在需要数组值的上下文中使用非数组值时会自动包装为数组值：值会被 [ 和 ] 包围（即转为一个数组）。在以下示例中，每个参数都会被自动包装为数组（[1], [2]），然后合并为一个数组。合并规则与上述合并数组的案例一致：

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

合并数组和对象。对象被自动包装为一个数组，然后执行上述提到的合并数组规则。示例如下：

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] 追加到 path 来获取数组中位置 N 的值。数组位置都是从 0 开始整数。若 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，这将会在后续提到（Rightmost array element）。

  • path 可包含 * 和 ** 通配符：

    • .[*] 表示 JSON 对象所有成员的值。

    • [*] 表示 JSON 数组的所有元素。

    • prefix**suffix 表示所有开始于 prefix 且结束于 suffix 的路径。

  • 对于不存在于 JSON 文档的 path 返回 NULL。

$ 引用以下 JSON 数组：

[3, {"a": [5, 6], "b": 10}, [99, 100]]

然后：

  • $[0] 得到 3。

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

  • $[2] 得到 [99, 100]。

  • $[3] 得到 NULL（指向并不存在的第 4 个数组元素）。

因为 $[1] 和 $[2] 是非字面量值，能通过更具体的路径表达式查询嵌套值，如：

  • $[1].a 得到 [5, 6]。

  • $[1].a[1] 得到 6。

  • $[1].b 得到 10。

  • $[2][0] 得到 99。

如前面提到，path 中的键名必须使用引号，否则可能是个非法的路径表达式。$ 引用以下值：

{"a fish": "shark", "a bird": "sparrow"}

键名都含有一个空格，因此必须使用引号：

  • $."a fish" 得到 shark。

  • $."a bird" 得到 sparrow。

对于 JSON 数组，使用通配符的 path 会得到一个可能包含多个值的数组：

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 会得到多个 path（$.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。N 必须大于或等于 M，而 M 必须大于或等于 0。

可在支持通配符的上下文中使用区间取值。

最右数组元素。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)

若 path 得到值不是数组，则将该值作为单元素数组进行处理：

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

column->path 等同于 JSON_EXTRACT(column, path)，详情请查阅 Section 12.18.3, “Functions That Search JSON Values” 和 Indexing a Generated Column to Provide a JSON Column Index。

一些函数接受 JSON 文档作为参数，然后以某种方式修改它并返回修改后的文档。路径表达式指示文档哪里需要进行修改。如 JSON_SET()、JSON_INSERT() 和 JSON_REPLACE() 函数均接受一个 JSON 文档、一个或多个用于指定文档位置和值的 path-value 对作为参数。这些函数的差异在于：指定路径的值是否存在的处理方式。

考虑以下 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]]             |
+------------------------------------------------+

path-value 对会从左往右执行。前一对产生的文档会成为下一对的新值。

JSON_REMOVE() 接受一个 JSON 文档和一个或多个用于删除文档指定路径值的 path。其返回值是原始文档减去指定且存在路径的值后的文档：

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 并删之。

  • 第二个 $[1].b[1] 匹配为空：因为该元素已被删除，故无影响。

JSON 路径语法

Section 12.18, “JSON Functions” 描述的大多数 JSON 函数都需要一个路径表达式作为参数，以指定 JSON 文档的特定元素。路径由路径作用域（scope）及其后续的一个或多个路径分支组成。对于 MySQL JSON 函数使用的路径，作用域（scope）始终是正在搜索或其他操作的 JSON 文档，由前置 $ 表示。路径分支由句号（.）分隔。数组中的元素由 [N] 表示，其中 N 是非负整数。键名必须是双引号包围的字符串或合法的 ECMAScript 标识符（详情看 ECMAScript 语言规范的 Identifier Names and Identifiers）。路径表达式，如 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 路径作用域的列引用。但目前 MySQL 不支持。

通配符 * 和 ** 用法如下：

  • .* 表示对象所有键的值。

  • [*] 表示数组所有元素。

  • [prefix]**suffix 表示所有开始于 prefix 且结束于 suffix 的路径。prefix 是可选项，而 suffix 是必选项。也就是说，路径不能结束于 **。

    另外，路径不能包含 ***。

关于路径语法的案例，请查看各个接受路径参数的 JSON 函数的描述，如 JSON_CONTAINS_PATH()、JSON_SET() 和 JSON_REPLACE()。

MySQL 8.0.2 及以上支持 JSON 数组使用 to 关键字进行范围选择（如 $[2 to 10]），last 关键字表示数组最右元素。详情请查看 Searching and Modifying JSON Values。

JSON 值的比较与排序

JSON 值可使用 =、<、<=、>、>=、<>、!= 和 <=> 操作符进行比较。

以下用于比较的操作符和函数目前仍不支持 JSON 值：

相应的解决办法是：将 JSON 值转为原生 MySQL 的数值或字符串数据类型后再进行比较。

JSON 值的比较发生在两个级别。第一级别是基于比较值的 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 规则相同。

  • DATETIME

    较早时间点的值排在较晚时间点前。若两值最初分别是 MySQL 的 DATEIME 和 TIMESTAMP 类型，当它们表示的时间点一致，则它们相等。

  • TIME

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

  • DATE

    较早日期排在较大日期前。

  • ARRAY

    两 JSON 数组若长度相等且相应坐标的值也相等，则两者相等。

    若两 JSON 数组不等，则次序取决于首个不同的元素值，该位置的值较小的排前面。若所有相应坐标的相等，则数组长度短的排前面。

    示例如下：

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

  • BOOLEAN

    false 字面量小于 true 字面量。

  • OBJECT

    两 JSON 对象若所有键相等且键对应值也相等，则两者相等。

    示例如下：

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

    两个不相等对象的次序是未知的，但却是固定的。

  • STRING

    两值在 utf8mb4 排序规则下对前 N 字节进行比较，其中 N 是较短值的长度。若两值前 N 字节相同，则较短值排在较长值前。

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

    该次序与 SQL 字符串在 utf8mb4_bin 排序规则相同。因为 utf8mb4 是二进制排序规则，JSON 值的比较是大小写敏感的：

    "A" < "a"

  • INTEGER、DOUBLE

    JSON 值可包含精确数值和近视数值。对于这些数值类型的详情，请查阅 Section 9.1.2, “Numeric Literals”。 MySQL 原生数值类型的比较规则，请查阅 Section 12.3, “Type Conversion in Expression Evaluation”，JSON 内的数值比较与之不同的地方有：

    • 在原生 MySQL INT 和 DOUBLE 数值类型的两列比较中，所有比较都涉及一个整数和一个双精度浮点数，因此所有行的整数都会被转为双精度浮点数。即精确数值都被转为近似数值。

    • 若两个包含数值的 JSON 列进行比较，则无法提前知道这些数值是整数还是双精度浮点数。为了在所有行中提供一致的行为，MySQL 会将近视数值转为精确数值。这样，排序结果是一致的，并且精确数值不会失去精度。例如，给定这些数值：9223372036854775805, 9223372036854775806, 9223372036854775807 和 9.223372036854776e18，它们的次序如下：

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

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

  • 整数值比较：

    9223372036854775805 < 9223372036854775806 < 9223372036854775807

  • 双精度值比较：

    9223372036854775805 = 9223372036854775806 = 9223372036854775807 = 9.223372036854776e18

对于任何 JSON 值与 SQL NULL 比较，结果均为 UNKNOWN。

对于 JSON 和 非 JSON 值比较，非 JSON 值会根据下表的规则转为 JSON，然后再根据上述规则进行比较。

JSON 值与非 JSON 值的相互转换

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

其他类型

CAST(其他类型 AS JSON)

CAST(JSON AS 其他类型)

JSON

没变化

没变化

utf8 字符类型（utf8mb4、utf8、ascii）

该字符串解析为 JSON 值。

JSON 值会序列化为 utf8mb4 字符串。

其他字符类型

其他字符类型会被隐式转为 utf8mb4，然后按照 utf8 字符类型的描述进行处理。

JSON 值会被序列化为 utf8mb4 字符串，然后转为其他字符类型编码。这个结果可能没有意义。

NULL

JSON 类型的 null 值。

不适用。

Geometry 类型

通过调用 ST_AsGeoJSON() 将 geometry 值转为 JSON 文档。

非法操作。解决方法：将 CAST(json_val AS CHAR) 传递给 ST_GeomFromGeoJSON()

其余类型

生成由单个标量值组成的 JSON 文档。

如果 JSON 文档由目标类型的单个标量值组成，则将该标量值转为目标类型。否则，返回 NULL 并产生警告。

JSON 值的 ORDER BY 和 GROUP BY 会根据以下原则工作：

  • 对标量 JSON 值排序时，使用上述比较规则。

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

  • JSON 值的键名排序受 max_sort_length 系统变量限制，因此键名仅在前 max_sort_length 字节相等时才会进行比较。

  • 目前仍不支持对非标量 JSON 值进行排序，会产生警告。

对于排序，将标量 JSON 值转为原生 MySQL 类型可能会更好。例如，假设 jdoc 列包含由 id 键和非负整数组成的 JSON 对象，则使用以下语句按 id 值进行排序：

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

若恰好有一个生成列的定义使用与 ORDER BY 使用相同表达式，则会被 MySQL 优化器所识别，并考虑将索引用于查询执行计划。详情请查阅 Section 8.3.11, “Optimizer Use of Generated Column Indexes”。

JSON 值的聚合

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

上一页空间数据类型下一页数据类型的默认值

最后更新于