0
0

MySQL 之 JSON 支持（一）—— JSON 数据类型

    官方文档链接：13.5 The JSON Data Type

    MySQL 支持由 RFC 7159 所定义的原生 JSON 数据类型，通过该类型能够有效访问 JSON（JavaScript 对象表示法）文档中的数据。与将 JSON 格式字符串存储在字符串列中相比，JSON 数据类型提供了以下优点：

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

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

      MySQL 8.0 中还用 JSON_MERGE_PATCH() 函数支持 RFC 7396 中定义的 JSON Merge Patch 格式。可参阅此函数的描述，以及“JSON值的规范化、合并和自动封装”，以获取示例和更多信息。

  说明：本讨论使用 monotype 字体的 JSON 来具体表示 JSON 数据类型，使用普通字体中的“JSON”来表示 JSON 数据。

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

  在 MySQL 8.0.13 之前，JSON 列不能具有非 NULL 默认值。

  除 JSON 数据类型外，还有一组 SQL 函数可用于对 JSON 值进行操作，如创建、修改和搜索。下面显示了相关操作的一些示例。有关各个函数的详细信息，参阅“第14.17节 JSON 函数”。

  还有一组用于对 GeoJSON 值进行操作的空间函数。参阅“第14.16.11节 空间 GeoJSON 函数”。

  与其它二进制类型的列一样，不能直接对 JSON 列进行索引，但可以在生成列上创建一个索引，利用该索引从 JSON 列中提取标量值。有关详细示例，参阅“索引生成列以提供 JSON 列索引”。

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

  在 MySQL 8.0.17 及更高版本中，InnoDB 存储引擎支持 JSON 数组上的多值索引。参见“多值索引”。

  MySQL NDB Cluster 8.0 支持 JSON 列和 MySQL JSON 函数，包括在 JSON 列的生成列上创建索引，作为不能为 JSON 列直接创建索引的解决方案。每个 NDB 表最多支持 3 个 JSON 列。

1. JSON 值的部分更新

    在 MySQL 8.0 中，优化器可以执行 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 列的完整更新可能作为部分更新记录在二进制日志中。当前面列表中的最后两个条件中的一个（或两个）不满足，但其它条件满足时，可能会发生这种情况。另可参阅 binlog_row_value_options 的说明。

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

2. 创建 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 值（参阅“第14.17.2节 创建JSON值的函数”），也可以使用 CAST(value 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 的，但应被视为值中实际发生问题位置的粗略指示。

  • 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 8.0.3 及更高版本也支持 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 执行转义序列处理，而是将字符串文本传递给存储引擎进行处理。以刚才显示的任何一种方式插入 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". |
+-----------------------------------------+

    说明：如果启用了 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() 函数的描述。

3. JSON 值的规范化、合并和自动封装

（1）规范化

    当一个字符串被解析并被发现是一个有效的 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）

    在 8.0.3 之前的 MySQL 版本中，具有与文档前面发现的键重复的键的成员将被丢弃。以下 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 8.0.3 之前，在 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 文档中键、值或元素之间的额外空白，并在显示时在每个逗号（,）或冒号（:）后面留下（或在必要时插入）一个空格。这样做是为了增强可读性。

    生成 JSON 值的 MySQL 函数（参阅“第14.17.2节 创建 JSON 值的函数”）总是返回规范化的值。

    为了提高查找效率，MySQL 还会对 JSON 对象的键进行排序。应该知道，此排序的结果可能会发生更改，并且不能保证在各个版本中保持一致。

（2）合并 JSON 值

    MySQL 8.0.3（及更高版本）支持两种合并算法，由函数 JSON_MERGE_PRESERVE() 和 JSON_MERGE_PATCH() 实现。它们处理重复键的方式不同：JSON_MERGE_PRESERVE() 保留重复键的值，而 JSON_MERGE_PATCH() 丢弃除最后一个值以外的所有值。接下来的几段分别解释这两个函数如何处理 JSON 文档（即对象和数组）的不同组合的合并。

    说明：JSON_MERGE_PRESERVE() 与以前版本的 MySQL（在 MySQL 8.0.3 中重命名）中的 JSON_MERGE() 函数相同。在 MySQL 8.0 中，JSON_MERGE() 仍然作为 JSON_MERGE_PRESERVE() 的一个别名被支持，但已不建议使用，并将在未来的版本中删除。

合并数组

    在组合多个数组的上下文中，被合并为一个数组。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}

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

4. 搜索和修改 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[0] 的计算结果与 path 相同： mysql> SELECT JSON_SET('"x"', '$[0]', 'a');+------------------------------+| JSON_SET('"x"', '$[0]', 'a') |+------------------------------+| "a" |+------------------------------+1 row in set (0.00 sec)

  • [M 到 N] 指定数组值的子集或范围，从位置 M 处的值开始，到位置 N 处的值结束。last 被支持作为最右边数组元素下标的同义词。还支持数组元素的相对地址。如果 path 没有选择数组值，则 path[last] 的计算结果与 path 的值相同，如本节稍后所示（参见“最右边的数组元素”）。

  • 路径可以包含 * 或 ** 通配符：.[] 评估 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]                                                  |
+---------------------------------------------------------+

（1）JSON 数组中的范围

    可以使用带有 to 关键字的 range 来指定 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 到 N，其中 M 和 N 分别是 JSON 数组中一系列元素的第一个和最后一个下标。N 必须大于 M；M 必须大于或等于0。数组元素的下标以 0 开头。可以在支持通配符的上下文中使用范围。

（2）最右边的数组元素

    支持用 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]]             |
+------------------------------------------------+

    path-value 对是从左到右评估的。通过评估一对生成的文档将成为评估下一对的新值。

    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] 的第二个实例与任何内容都不匹配：该元素已被删除，路径不再存在，也没有任何效果。

5. JSON 路径语法

    MySQL 支持并在本手册其它地方描述的许多 JSON 函数（参阅“第14.17节 JSON函数”），需要一个路径表达式来识别 JSON 文档中的特定元素。路径由路径的范围和一个或多个路径分支组成。对于 MySQL JSON 函数中使用的路径，作用域始终是要搜索或以其它方式操作的文档，由前导 $ 字符表示。路径分支由句点字符（.）分隔。数组中的单元格由 [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 路径表达式中使用 “$” 作为文档的同义词。

    说明：有些实现支持 JSON 路径作用域的列引用，但 MySQL 8.0 不支持。

    通配符 * 和 ** 标记的使用方式如下：

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

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

  • [prefix]**suffix 表示所有以前缀开头、以后缀结尾的路径。前缀是可选的，后缀是必需的；换句话说，路径不能以 ** 结束。

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

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

  MySQL 8.0 还支持使用 to 关键字（如 $[2 to 10]）作为 JSON 数组子集的范围表示法，以及 last 关键字作为数组最右边元素的同义词。有关更多信息和示例，参阅“搜索和修改 JSON 值”。

6. JSON 值的比较和排序

    JSON 值可以使用 =、<、<=、>、>=、<>、!= 和 <=> 运算符进行比较。JSON 值还不支持以下比较运算符和函数：

  • BETWEEN

  • IN()

  • GREATEST()

  • LEAST()

      使用这里列出的比较运算符和函数的一个解决方法是将 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：被比较的两个字符串的 utf8mb4 表示的前 N 个字节上按词法排序，其中 N 是较短字符串的长度。如果两个字符串中的前 N 字节相同，则较短的字符串被认为比较长的字符串小。例如： "a" < "ab" < "b" < "bc"此排序等效于排序规则为 utf8mb4_bin 的 SQL 字符串的排序。因为 utf8mb 4_bin 是二进制排序规则，所以 JSON 值的比较区分大小写："A" < "a"

  • INTEGER, DOUBLE：JSON 值可以包含精确的数值和近似的数值。有关这些数字类型的一般讨论，参阅“第11.1.2节 数字文本”。“第14.3节 表达式评估中的类型转换”讨论了比较原生 MySQL 数字类型的规则，但比较 JSON 值中的数字的规则有些不同： - 在分别使用原生 MySQL INT 和 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，然后按前面所述进行比较。

7. 在 JSON 和非 JSON 值之间转换

    下表提供了 MySQL 在 JSON 值和其它类型的值之间转换时遵循的规则摘要：

表 13.3 JSON 转换规则

其它类型

CAST(other type AS JSON)

CAST(JSON AS other type)

JSON

不变

不变

uft8****字符类型(utf8mb4, utf8mb3, ascii)

字符串被解析为JSON值。

JSON值被序列化为一个utf8mb4字符串。

其它字符类型

其它字符编码隐式转换为utf8mb4，并按照针对该字符类型的描述进行处理。

JSON值被序列化为utf8mb4字符串，然后转换为其它字符编码。结果可能没有意义。

NULL

结果是JSON类型的NULL值。

不适用。

地理类型

通过调用ST_AsGeoJSON()将地理值转换为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 标量强制转换为其它一些原生 MySQL 类型可能是有益的。例如，如果名为 jdoc 的列包含 JSON 对象，该对象的成员由 id 键和非负值组成，则使用此表达式按 id 值排序：

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

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

8. JSON 值的聚合

    其它数据类型一样，对于 JSON 值的聚合，SQL NULL 值被忽略。非 NULL 值将转换为数字类型并进行聚合，但 MIN()、MAX() 和 GROUP_CONCAT() 除外。对于作为数字标量的 JSON 值，转换为数字应该会产生有意义的结果，尽管（取决于值）可能会发生截断和精度损失。转换其它 JSON 值为数字可能不会产生有意义的结果。
标签： json mysql
本文转载自: https://blog.csdn.net/wzy0623/article/details/139458694
版权归原作者 wzy0623 所有， 如有侵权，请联系我们删除。
登录后发布评论

“MySQL 之 JSON 支持（一）—— JSON 数据类型”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

落实算法安全主体责任：全面保障算法合规与安全

Hadoop生态圈框架部署（四）- Hadoop完全分布式部署

毕设项目 基于大数据的b站数据分析

【前端】Bootstrap：栅格系统 (Grid System)

docker部署rustdesk

【PostgreSQL】实战篇——用户管理、角色和权限控制的高级用法及技巧

AI时代：弯道超车的新思维与实践路径

文章导航