简单理解Mysql json数据类型

2021-01-28 23:38 阅读：6793

MySQL支持RFC 7159JSON定义的本机数据类型，该类型可有效访问JSON（JavaScript对象表示法）文档中的数据。该数据类型提供了这些优点超过存储在字符串列JSON格式的字符串： JSON

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

MySQL 8.0还使用该功能支持RFC 7396中定义的JSON Merge Patch格式。有关示例和更多信息，请参见此函数的描述以及 JSON值的规范化，合并和自动包装。 JSON_MERGE_PATCH()

简单理解Mysql json数据类型JSON值的部分更新创建JSON值JSON值的规范化，合并和自动包装合并JSON值搜索和修改JSON值JSON路径语法JSON值的比较和排序在JSON和非JSON值之间转换JSON值的汇总

本讨论使用JSONmonotype专门表示JSON数据类型，而使用常规字体的“ JSON ”通常表示JSON数据。

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

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

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

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

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

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

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

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

JSON值的部分更新

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

正在更新的列声明为 JSON。
该UPDATE语句使用任何的三个功能 JSON_SET()， JSON_REPLACE()或 JSON_REMOVE()更新列。列值的直接分配（例如 UPDATE mytable SET jcol = '{"a": 10, "b": 25}'）不能作为部分更新执行。
可以通过这种方式优化JSON单个UPDATE语句中多个列的更新。MySQL只能对使用刚刚列出的三个函数更新其值的那些列执行部分更新。
输入列和目标列必须是同一列；这样的语句UPDATE mytable SET jcol1 = JSON_SET(jcol2, '$.a', 100)不能作为部分更新执行。
只要输入和目标列相同，此更新就可以以任何组合方式使用对上一项列出的任何函数的嵌套调用。
所有更改都会用新值替换现有的数组或对象值，并且不会将任何新元素添加到父对象或数组。
要替换的值必须至少与替换值一样大。换句话说，新值不能大于旧值。
当先前的部分更新为较大的值留出了足够的空间时，可能会发生此要求的例外情况。您可以使用该函数 JSON_STORAGE_FREE()查看任何部分JSON列更新已释放了多少空间。

可以使用节省空间的紧凑格式将此类部分更新写入二进制日志。可以通过将binlog_row_value_options 系统变量设置为启用此功能PARTIAL_JSON。有关更多信息，请参见此变量的描述。

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

创建JSON值

JSON数组包含一个值列表，这些值用逗号分隔并包含在[和] 字符内：

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

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

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

如示例所示，JSON数组和对象可以包含字符串或数字的标量值，JSON空文字或JSON布尔值true或false文字。JSON对象中的键必须是字符串。还允许使用时间（日期，时间或日期时间）标量值：

["12:18:29.000000", "2015-07-29", "2015-07-29 12:18:29.000000"]

JSON数组元素和JSON对象键值中允许嵌套：

[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]
{"k1": "value", "k2": [10, 20]}

为此，您还可以从MySQL提供的许多函数中获取JSON值（请参见第12.18.2节“创建JSON值的函数”），以及通过JSON使用将其他类型的值强制转换为使用该类型的值（请参见在JSON之间进行转换）和非JSON值）。接下来的几段描述MySQL如何处理作为输入提供的JSON值。 CAST(*value* AS JSON)

在MySQL中，JSON值被写为字符串。MySQL会解析在需要JSON值的上下文中使用的任何字符串，如果该字符串作为JSON无效，则会产生错误。这些上下文包括将值插入具有JSON数据类型的列中，并将参数传递给需要JSON值的函数（通常显示为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。）

作为使用文字字符串编写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'.

从该SQL的JSON文字不同的情况下的灵敏度NULL，TRUE和 FALSE文字，它可以在任何大小写被写成：

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

有时可能需要或希望在JSON文档中插入引号字符（"或'）。假设在此示例中，您想要在表中插入使用以下语句创建的表中的JSON对象，这些JSON对象包含表示有关MySQL的某些事实的句子，每个句子都声明了有关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对象后，您可以通过执行以下简单操作看到JSON列值中存在反斜杠SELECT：

mysql> SELECT sentence FROM facts;
+---------------------------------------------------------+
| sentence                                                |
+---------------------------------------------------------+
| {"mascot": "Our mascot is a dolphin named \"Sakila\"."} |
+---------------------------------------------------------+

要查找使用mascot关键字作为关键字的特定句子，可以使用column-path运算符 ->，如下所示：

mysql> SELECT col->"$.mascot" FROM qtest;
+---------------------------------------------+
| col->"$.mascot"                             |
+---------------------------------------------+
| "Our mascot is a dolphin named \"Sakila\"." |
+---------------------------------------------+
1 row in set (0.00 sec)

这使反斜杠以及周围的引号保持完整。要使用mascot键显示所需的值，但不包括周围的引号或任何转义符->>，请使用内联路径运算符，如下所示：

mysql> SELECT sentence->>"$.mascot" FROM facts;
+-----------------------------------------+
| sentence->>"$.mascot"                   |
+-----------------------------------------+
| Our mascot is a dolphin named "Sakila". |
+-----------------------------------------+

注意

如果NO_BACKSLASH_ESCAPES启用了服务器SQL模式，上一个示例将无法正常运行。如果设置了此模式，则可以使用单个反斜杠而不是双反斜杠来插入JSON对象文字，并且保留反斜杠。如果JSON_OBJECT()在执行插入操作时使用了该函数，并且设置了此模式，则必须使用单引号和双引号，例如：

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

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

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

解析字符串并发现它是有效的JSON文档时，也会对其进行规范化。这意味着具有与在文档的后面找到的键重复的键的成员（从左到右读取）将被丢弃。由以下JSON_OBJECT()调用产生的对象值仅包含第二个key1元素，因为该键名早于该值出现，如下所示：

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def');
+------------------------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def') |
+------------------------------------------------------+
| {"key1": "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解析器都实现了这种行为。（缺陷＃86866，错误＃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函数（请参见第12.18.2节“创建JSON值的函数”）始终返回规范化的值。

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

合并JSON值

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

注意

JSON_MERGE_PRESERVE()与JSON_MERGE()以前版本的MySQL（在MySQL 8.0.3中重命名）中找到的功能相同。在MySQL 8.0中JSON_MERGE()仍支持它作为别名JSON_MERGE_PRESERVE()，但已被弃用，并且在将来的版本中会被删除。

合并数组。 在组合多个数组的上下文中，将这些数组合并为一个数组。 JSON_MERGE_PRESERVE()通过将稍后命名的数组连接到第一个数组的末尾来实现此目的。JSON_MERGE_PATCH()将每个参数视为由单个元素组成的数组（因此其索引为0），然后应用“ last last key key wins ”逻辑仅选择最后一个参数。您可以比较此查询显示的结果：

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*]追加到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)

[*M* to *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]                                                  |
+---------------------------------------------------------+

JSON数组的范围。 您可以将range与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)

语法为，其中和分别是JSON数组中一系列元素的第一个和最后一个索引。必须大于; 必须大于或等于0。数组元素的索引从0开始。 *M* to *N*MNNMM

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

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

您可以将 JSON列标识符和JSON路径表达式用作的同义词。有关更多信息，请参见第12.18.3节“搜索JSON值的函数”。另请参见索引生成的列以提供JSON列索引。 *column*->*path*JSON_EXTRACT(*column*, *path*)

某些函数采用现有的JSON文档，以某种方式对其进行修改，然后返回生成的修改后的文档。路径表达式指示在文档中的何处进行更改。例如，JSON_SET()， JSON_INSERT()，和 JSON_REPLACE()每个功能采取JSON文档，加上一个或描述，其中修改文档多个路径-值对和值来使用。这些功能在如何处理文档中现有和不存在的值方面有所不同。

考虑以下文档：

mysql> SET @j = '["a", {"b": [true, false]}, [10, 20]]';

JSON_SET() 替换存在的路径的值，并添加不存在的路径的值：。

mysql> SELECT JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+--------------------------------------------+
| JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+--------------------------------------------+
| ["a", {"b": [1, false]}, [10, 20, 2]]      |
+--------------------------------------------+

在这种情况下，路径会$[1].b[0]选择一个现有值（true），该值将替换为路径参数（1）后的值。该路径$[2][2]不存在，因此会将相应的值（2）添加到由选择的值$[2]。

JSON_INSERT() 添加新值，但不替换现有值：

mysql> SELECT JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+-----------------------------------------------+
| JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+-----------------------------------------------+
| ["a", {"b": [true, false]}, [10, 20, 2]]      |
+-----------------------------------------------+

JSON_REPLACE() 替换现有值并忽略新值：

mysql> SELECT JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+------------------------------------------------+
| JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+------------------------------------------------+
| ["a", {"b": [1, false]}, [10, 20]]             |
+------------------------------------------------+

路径值对从左到右评估。通过评估一对对生成的文档将成为新的价值，以此评估下一对。

JSON_REMOVE()接收JSON文档和一个或多个指定要从文档中删除的值的路径。返回值是原始文档减去文档中存在的路径选择的值：

mysql> SELECT JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]');
+---------------------------------------------------+
| JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]') |
+---------------------------------------------------+
| ["a", {"b": [true]}]                              |
+---------------------------------------------------+

路径具有以下影响：

$[2]匹配[10, 20] 并删除它。
的第一个实例$[1].b[1]相匹配 false的b元素并将其删除。
不$[1].b[1]匹配的第二个实例：该元素已被删除，路径不再存在，并且无效。

JSON路径语法

MySQL支持的许多JSON函数以及本手册中其他地方所描述的（请参见第12.18节“ JSON函数”）都需要使用路径表达式来标识JSON文档中的特定元素。路径由路径的范围和一个或多个路径分支组成。对于MySQL JSON函数中使用的路径，范围始终是要搜索或对其进行操作的文档，并以前导$字符表示。路径分支由句点字符（.）分隔。数组中的单元格由表示，其中是非负整数。键的名称必须为双引号字符串或有效的ECMAScript标识符（请参阅 [*N*]N标识符名称和标识符，请参阅 ECMAScript语言规范）。路径表达式，如JSON文本，应该使用的编码 ascii，utf8或 utf8mb4字符集。其他字符编码被隐式强制为utf8mb4。完整的语法如下所示：

pathExpression:
    scope[(pathLeg)*]

pathLeg:
    member | arrayLocation | doubleAsterisk

member:
    period ( keyName | asterisk )

arrayLocation:
    leftBracket ( nonNegativeInteger | asterisk ) rightBracket

keyName:
    ESIdentifier | doubleQuotedString

doubleAsterisk:
    '**'

period:
    '.'

asterisk:
    '*'

leftBracket:
    '['

rightBracket:
    ']'

如前所述，在MySQL中，路径的范围始终是要对其进行操作的文档，表示为 $。您可以'$'在JSON路径表达式中用作文档的同义词。

注意

一些实现支持JSON路径范围的列引用；目前，MySQL不支持这些。

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

.* 表示对象中所有成员的值。
[*] 表示数组中所有单元格的值。
[*prefix*]***suffix* 表示以开头prefix和结尾的所有路径 suffix。 prefix是可选的，而 suffix必需的；换句话说，路径可能不会以结尾**。
另外，路径可能不包含序列 ***。

对于路径语法的例子，见该采取的路径作为参数，例如各种JSON功能的说明 JSON_CONTAINS_PATH()， JSON_SET()和 JSON_REPLACE()。有关包括使用*和 **通配符的示例，请参见JSON_SEARCH()函数的说明。

MySQL 8.0.2及更高版本还支持使用to关键字（例如 $[2 to 10]）以及作为last数组最右边元素的同义词的关键字来表示JSON数组子集的范围符号。有关更多信息和示例，请参见搜索和修改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值可以包含精确值数字和近似值数字。有关这些类型的数字的一般讨论，请参见第9.1.2节“数字文字”。
第12.3节“表达式评估中的类型转换” 中讨论了比较本机MySQL数值类型的规则，但是在JSON值内比较数字的规则有些不同：
- 在分别使用本机MySQLINT和 DOUBLE数字类型的两列之间的比较中，众所周知，所有比较都涉及一个整数和一个双精度数，因此对于所有行，该整数都将转换为双精度数。即，将精确值数字转换为近似值数字。
- 另一方面，如果查询比较两个包含数字的JSON列，则无法预先知道数字是整数还是双精度。为了在所有行中提供最一致的行为，MySQL将近似值数字转换为精确值数字。所得的顺序是一致的，并且不会丢失精确值数字的精度。例如，给定标量9223372036854775805、9223372036854775806、9223372036854775807和9.223372036854776e18，顺序如下：
```
9223372036854775805 < 9223372036854775806 < 9223372036854775807
< 9.223372036854776e18 = 9223372036854776000 < 9223372036854776001
```
如果是JSON比较以使用非JSON数字比较规则，则可能会出现不一致的顺序。通常的MySQL比较规则会产生以下顺序：
- 整数比较：
```
9223372036854775805 < 9223372036854775806 < 9223372036854775807
```
  （未为9.223372036854776e18定义）
- 双重比较：
```
9223372036854775805 = 9223372036854775806 = 9223372036854775807 = 9.223372036854776e18
```

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

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

在JSON和非JSON值之间转换

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

表11.3 JSON转换规则

其他类型	CAST（其他类型的AS JSON）	CAST（JSON AS其他类型）
JSON格式	没变化	没变化
UTF8字符类型（`utf8mb4`， `utf8`，`ascii`）	该字符串将解析为JSON值。	JSON值被序列化为`utf8mb4`字符串。
其他字符类型	其他字符编码将隐式转换`utf8mb4`为utf8字符类型，并按utf8字符类型所述进行处理。	JSON值被序列化为`utf8mb4`字符串，然后转换为其他字符编码。结果可能没有意义。
`NULL`	结果`NULL`为JSON类型的值。	不适用。
几何类型	通过调用将几何值转换为JSON文档 `ST_AsGeoJSON()`。	非法操作。解决方法：将的结果传递给。`CAST(`json_val` AS CHAR)ST_GeomFromGeoJSON()`
所有其他类型	结果是由单个标量值组成的JSON文档。	如果JSON文档由目标类型的单个标量值组成并且该标量值可以转换为目标类型，则成功。否则，返回`NULL` 并产生警告。

ORDER BY并GROUP BY为JSON值根据这些原则，工作原理：

标量JSON值的排序使用与前面的讨论相同的规则。
对于升序排序，SQLNULL在所有JSON值（包括JSON空文字）之前进行排序。对于降序排序，SQL对NULL所有JSON值（包括JSON空文字）进行排序。
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优化器将识别出该列，并考虑将索引用于查询执行计划。请参见第8.3.11节“优化使用生成的列索引”。

JSON值的汇总

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

原文地址：https://dev.mysql.com/doc/refman/8.0/en/json.html

此文章版权归凌雲所有，内容为作者个人观点，本站只提供参考并不构成任何投资及应用建议。本站拥有对此声明的最终解释权。

发表评论

{{commentTotal}} 条评论

- 上拉或点击加载更多 -

- 加载中 -

- 没有更多了 -

- 本文链接 -

记得博客网