MySQL 处理JSON字符串

本文介绍: 现在很多数据会以json格式存储，如果你还在用like查询json字符串，那你就OUT了， MySQL从5.6版本就开始支持json字符串查询了~下面来看看MySQL中支持的json处理函数吧！MySQL支持定义的原生JSON数据类型由RFC 7159支持高效访问JSON数据（JavaScript Object Notation）文档。的JSON数据类型提供了这些优势，在字符串列中存储JSON格式的字符串：自动验证存储在JSON列。无效的文档将生成错误.优化存储格式。JSON文档存储在。

前言

前言

现在很多数据会以json格式存储，如果你还在用like查询json字符串，那你就OUT了， MySQL从5.6版本就开始支持json字符串查询了~

下面来看看MySQL中支持的json处理函数吧！

MySQL支持定义的原生JSON数据类型由RFC 7159支持高效访问JSON数据（JavaScript Object Notation）文档。的 JSON数据类型提供了这些优势，在字符串列中存储JSON格式的字符串：

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

MySQL 8.3还支持JSON合并中定义的修补程序格式 RFC 7396，使用JSON_MERGE_PATCH() 功能请参阅此函数的说明，以及例如，JSON值的规范化、合并和自动包装，以及信息.

注意

本讨论使用monotype中的JSON来具体指明JSON数据类型和以常规字体表示JSON数据。

存储JSON文档所需的空间为与LONGBLOB或 LONGTEXT;见有关详细信息，请参见第11.7节它请记住，任何JSON文档的大小存储在JSON列中的值仅限于 max_allowed_packet系统变量(When服务器正在内部操作JSON值在内存中，它可以大于此值;当服务器存储它。）您可以获得所需的空间量，存储JSON文档使用 JSON_STORAGE_SIZE()function; note 对于JSON列，存储 size（以及此函数返回的值）为列在任何部分更新之前使用的（参见JSON部分的讨论）在本节稍后更新优化）。

沿着JSON数据类型，一组SQL 函数可用于启用对JSON值的操作，例如 as creation创建，manipulation操纵，and searching搜索.以下讨论显示了这些操作的示例。有关个人的详细信息函数，请参见第12.17节“

还提供了一组用于操作GeoJSON值的空间函数， available.请参见第12.16.11节

JSON列，与其他二进制列一样类型，不直接建立索引;相反，您可以创建索引对象中提取标量值的生成列上 JSON列。看到为生成的列建立索引以提供JSON列索引， example.

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

InnoDB存储引擎支持 JSON数组的多值索引。看到多值索引。

MySQL NDB集群支持JSON列， MySQL JSON函数，包括在列上创建索引从JSON列生成作为解决方法因为无法索引JSON列。一每台最多3个JSON柱 NDB表支持。

JSON值的部分更新

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

正在更新的列被声明为 JSON.
UPDATE语句使用任何三个职能中二号， JSON_SET()，或 #4更新柱列值的直接赋值（例如， UPDATE mytable SET jcol = '{“a”：10，“b”： 25}'）不能作为部分更新执行。

更新了一个中的多个JSON列单个UPDATE语句可以在 MySQL可以执行部分更新，使用以下三个函数更新其值的列刚刚上市。
输入列和目标列必须相同列; UPDATE mytable SET jcol 1等语句 = JSON_SET（jcol 2，'$. a'，100）无法执行为部分更新

更新可以对列出的任何函数使用嵌套调用在前一项中，以任何组合，只要输入和目标列相同。
所有更改都将用新的的，并且不向父对象添加任何新元素，阵
被替换的值必须至少与重置价值。换句话说，新值不能是任何比旧的大。

此要求的一个可能的例外是，上一次部分更新为更大的价值。您可以使用函数 JSON_STORAGE_FREE()如何的任何部分更新都释放了大量空间， JSON列。

可以使用节省空间的紧凑格式;这可以通过设置 binlog_row_value_options 系统变量为PARTIAL_JSON。

重要的是要区分部分更新的 JSON列值存储在表中，将行的所述部分更新写入所述二进制日志。是 JSON的完整更新列作为部分更新记录在二进制日志中。这当最后两个条件之一（或两者）不满足前面的列表，但满足其他条件满意

另请参见第一名。

接下来的几节提供了有关创建和操作JSON值。

创建JSON值

JSON数组包含由逗号分隔的值列表，封闭在[和]内字符数：

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

JSON对象包含一组键值对，逗号并括在{和 }人物：

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

如示例所示，JSON数组和对象可以包含标量值是字符串或数字，JSON null literal，或者JSON boolean true或false literal。钥匙 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]}

您还可以从许多函数中获取JSON值由MySQL为此提供（请参见第12.17.2节，将其他类型的值转换为JSON类型，使用 CAST(value AS JSON）（请参见在JSON和非JSON值之间转换。下一有几段描述了MySQL如何处理JSON值作为输入提供。

在MySQL中，JSON值被写成字符串。MySQL解析任何在需要JSON值的上下文中使用的字符串，以及如果它不是有效的JSON，则会产生错误。这些上下文包括将值插入到具有 JSON数据类型和传递参数到一个一个需要JSON值的函数（通常显示为 json_doc或 json_val在文档中， MySQL JSON函数），如以下示例所示：

尝试将值插入到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整理。其他字符串字符集将转换为utf8mb4，必要(For字符串在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)

MySQL 8.3 还使用 JSON_MERGE_PATCH（）函数支持 RFC 7396 中定义的 JSON Merge Patch 格式。有关示例和详细信息，请参阅此函数的说明以及 JSON 值的规范化、合并和自动包装。

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对象包含表示句子的字符串，陈述一些关于MySQL的事实，每一个都与一个适当的关键字添加到使用所示SQL语句创建的表中这里：


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

要查找这个以吉祥物为键的特定句子，您可以使用列路径运算符 ->，如下所示：


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解析器实现。（Bug #86866，错误#26369555）

MySQL会丢弃键、值或在原始JSON文档中的元素，并离开（或插入，必要时）每个逗号后加一个空格（,）或冒号（:），显示它。这样做是为了增强可读性。

生成 JSON 值的 MySQL 函数（参见第 12.17.2 节“创建 JSON 值的函数”）始终返回规范化值。

为了使查找更有效，MySQL还对 JSON对象。你应该知道此订单可能会更改，但不保证在各版本之间保持一致。

合并JSON值

支持两种合并算法，由功能JSON_MERGE_PRESERVE() JSON_MERGE_PATCH()。这些不同之处在于它们处理重复密钥的方式： JSON_MERGE_PRESERVE()保留重复键的值，而 JSON_MERGE_PATCH()全部丢弃而是最后一个值。接下来的几段解释了这两个函数处理不同 JSON文档的组合（即对象和数组）。

正在合并数组。& nbsp;你好 在联合收割机组合多个数组的上下文中，数组是合并成一个数组 JSON_MERGE_PRESERVE()这样做将后面命名的数组连接到第一个数组的末尾阵JSON_MERGE_PATCH()2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined2undefined22undefined2undefined2undefined2013 参数作为由单个元素组成的数组（因此具有0作为其索引），然后应用“最后重复 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 PatchG
*************************** 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 PatchG
*************************** 1. row ***************************
Preserve: {"a": [1, 4], "b": 2, "c": [3, 5], "d": 3}
   Patch: {"a": 4, "b": 2, "c": 5, "d": 3}

在需要数组值的上下文中使用的非数组值 are autowrapped：值被[包围和]字符将其转换为数组。在下面的语句中，每个参数都自动打包为数组（[1]，[2]）。这些然后合并以生成单个结果数组;如前两个病例，JSON_MERGE_PRESERVE() 组合具有相同键的值， JSON_MERGE_PATCH()丢弃所有值除了最后一个键之外的重复键，如下所示：


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

数组和对象值通过将对象自动标记为数组并通过组合值或 “last duplicate key wins” 合并功能（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 PatchG
*************************** 1. row ***************************
Preserve: [10, 20, {"a": "x", "b": "y"}]
   Patch: {"a": "x", "b": "y"}

搜索和修改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到 N]指定子集或数组值的范围，以位置处的值开始 M，并以位置N。

last被支持为最右边的数组元素的索引。相对寻址也支持数组元素。如果 path不选择数组 value，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 (it引用第四个数组元素，它不存在）。

因为$[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]到 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开始。

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

最右边的数组元素。 支持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 路径表达式的 column->path 用作 JSON_EXTRACT（column， path）的同义词。有关更多信息，请参见第 12.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），替换为 path参数（1）后面的值。路径$[2][2]不存在，因此将相应的值（2）加到该值上选择#6

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]的第二个实例匹配 nothing：该元素已被删除，路径为它已经存在，并且没有效果。

JSON路径

MySQL支持的许多JSON函数，（参见第12.17节“ 需要一个路径表达式来标识特定的 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.3 不支持这些。

*和** 令牌的使用方法如下：

.*表示所有成员的值在物体中。
[*]表示中所有单元格的值阵
[prefix]**suffix 表示以 prefix结束 suffix. prefix是可选的，而 suffix是必需的;在其他例如，一条路径可能不会在**中结束。

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

有关路径语法示例，请参见以路径作为参数的JSON函数，例如 JSON_SET()， JSON_CONTAINS_PATH()，以及JSON_REPLACE()。对于实施例其中包括使用*和 **通配符，请参见 * JSON_SEARCH()*函数。

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

JSON值的比较和排序

JSON值可以使用 =, <, <=, >, >=, <>, !=,和 <=>运算符

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

比较运算符和函数的变通方法只是列出的是将JSON值转换为原生MySQL数字或字符串数据类型，因此它们具有一致的非JSON标量类型。

JSON值的比较发生在两个级别。第一比较级别基于所比较对象的JSON类型。价值观如果类型不同，则确定比较结果只取决于哪个类型具有更高的优先级。如果两个值具有相同的JSON类型，则会发生第二级比较使用特定类型的规则。

下面的列表显示了JSON类型的优先级，从最高优先级到最低。(The类型名称是那些返回JSON_TYPE() 功能。）同一行上显示的类型具有相同的优先级。前面列出的JSON类型的任何值 list的比较结果大于列出了JSON类型的任何值在名单的后面。

BLOB
BIT
OPAQUE
DATETIME
TIME
DATE
BOOLEAN
ARRAY
OBJECT
STRING
INTEGER, DOUBLE
NULL

对于具有相同优先级的JSON值，比较规则为具体类型：

BLOB

两个字节的前N个字节值进行比较，其中N是较短值中的字节数。如果第一 N两个值的字节是相同，较短的值在较长的值之前排序。值
BIT

与#1相同的规则。
OPAQUE

与#1相同的规则。 BLOB价值观是指那些被归类为其他类型。
DATETIME

表示较早时间点的值是有序的在表示稍后时间点的值之前。如果两值最初来自MySQL DATETIME和TIMESTAMP 类型，如果它们表示同一时间点
TIME

两个时间值中较小的时间值在较大的时间值之前排序一个.
DATE

较早的日期在较近的日期之前排序。
ARRAY

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

如果数组不相等，则它们的顺序由在第一位置中的元件，在第一位置中，差其中值较小的数组位置是第一位的。如果较短的数组中的值等于较长的数组中，较短的数组首先排序。

范例：
```
[] < ["a"] < ["ab"] < ["ab", "cd", "ef"] < ["ab", "ef"]
```
BOOLEAN

JSON false literal小于JSON true literal。
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节

比较原生MySQL数值类型的规则是在第12.3节比较JSON值中数字的规则不同有点：
- 在两个使用本机 MySQLINT和 DOUBLE数字类型，分别地，已知所有比较涉及一个整数和一个双精度数，因此整数被转换为所有行都加倍。也就是说，精确值数字是转换为近似值数字。
- 另一方面，如果查询比较两个JSON 列包含数字，则无法在无论数字是整数还是双精度数。到在所有行中提供最一致的行为， MySQL将近似值转换为精确值号码结果排序是一致的，不丢失精确值数字的精度。为例如，给定标量9223372036854775805， 9223372036854775806、9223372036854775807和 9.223372036854776e18，订单如下：
```
9223372036854775805 < 9223372036854775806 < 9223372036854775807
< 9.223372036854776e18 = 9223372036854776000 < 9223372036854776001
```
JSON比较是否使用非JSON数值比较规则，可能会出现不一致的顺序。常见的MySQL 数字的比较规则产生这些排序：
- 对比：
```
9223372036854775805 < 9223372036854775806 < 9223372036854775807
```
  (not定义为9.223372036854776e18）
- 双重比较：
```
9223372036854775805 = 9223372036854775806 = 9223372036854775807 = 9.223372036854776e18
```

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

对于JSON和非JSON值的比较，非JSON值根据以下规则转换为JSON 表中，然后按前面所述进行比较。

Table 11.3 JSON 转换规则

other type	CAST(other type AS JSON)	CAST(JSON AS other type)
JSON	No change	No change
utf8 character type (`utf8mb4`, `utf8mb3`, `ascii`)	The string is parsed into a JSON value.	The JSON value is serialized into a `utf8mb4` string.
Other character types	Other character encodings are implicitly converted to `utf8mb4` and treated as described for this character type.	The JSON value is serialized into a `utf8mb4` string, then cast to the other character encoding. The result may not be meaningful.
`NULL`	Results in a `NULL` value of type JSON.	Not applicable.
Geometry types	The geometry value is converted into a JSON document by calling ST_AsGeoJSON().	Illegal operation. Workaround: Pass the result of CAST(json_val AS CHAR) to ST_GeomFromGeoJSON().
All other types	Results in a JSON document consisting of a single scalar value.	Succeeds if the JSON document consists of a single scalar value of the target type and that scalar value can be cast to the target type. Otherwise, returns `NULL` and produces a warning.

ORDER BY和GROUP BY用于 JSON值根据这些原则工作：

标量JSON值的排序使用与前面的讨论。
对于升序排序，SQL NULL排序在所有JSON值之前，包括JSON空文本;对于降序排序，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 优化器认识到这一点，并考虑将索引用于查询执行计划。看到第8.3.11节

JSON值的聚合

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

原文地址:https://blog.csdn.net/TalorSwfit20111208/article/details/135982132

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。

如若转载，请注明出处：http://www.7code.cn/show_64447.html

如若内容造成侵权/违法违规/事实不符，请联系代码007邮箱：suwngjj01@126.com进行投诉反馈，一经查实，立即删除！

声明：本站所有文章，如无特殊说明或标注，均为本站原创发布。任何个人或组织，在未征得本站同意时，禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益，可联系我们进行处理。