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列转换为内部 允许快速读取文档元素的格式。 当服务器稍后必须读取存储在此 二进制格式，不需要从文本中解析该值 表示.二进制格式的结构是为了使 服务器直接通过键查找子目录或嵌套值 或数组索引，而不阅读它们之前或之后的所有值 在文件中。

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 1to 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 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}

在需要数组值的上下文中使用的非数组值 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 Patch\G
*************************** 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 Patch\G
*************************** 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。
• 路径可以包含*或 **通配符：- .[*]计算为所有值 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值：

• BETWEEN
• IN()
• GREATEST()
• LEAST()

比较运算符和函数的变通方法只是 列出的是将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"]
• BOOLEANJSON 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、DOUBLEJSON值可以包含精确值数字， 近似值数字。对于这些问题的一般性讨论， 类型的数字，请参见第9.1.2节比较原生MySQL数值类型的规则是 在第12.3节 比较JSON值中数字的规则不同 有点：- 在两个使用本机 MySQLINT和 DOUBLE数字类型， 分别地，已知所有比较涉及 一个整数和一个双精度数，因此整数被转换为 所有行都加倍。也就是说，精确值数字是 转换为近似值数字。- 另一方面，如果查询比较两个JSON 列包含数字，则无法在 无论数字是整数还是双精度数。到 在所有行中提供最一致的行为， MySQL将近似值转换为精确值 号码结果排序是一致的， 不丢失精确值数字的精度。为 例如，给定标量9223372036854775805， 9223372036854775806、9223372036854775807和 9.223372036854776e18，订单如下：9223372036854775805 < 9223372036854775806 < 9223372036854775807< 9.223372036854776e18 = 9223372036854776000 < 9223372036854776001JSON比较是否使用非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 typeCAST(other type AS JSON)CAST(JSON AS other type)JSONNo changeNo changeutf8 character type (

utf8mb4

,

utf8mb3

,

ascii

)The string is parsed into a JSON value.The JSON value is serialized into a

utf8mb4

string.Other character typesOther 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 typesThe 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 typesResults 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值可能不会产生有意义的结果。