JOSN函数参考（六）

JOSN函数参考

CV大法的

JSON_ARRAY() 函数

MySQLJSON_ARRAY()函数返回一个包含了所有参数的JSON数组。JSON_ARRAY()语法这里是MySQLJSON_ARRAY()的语法：JSON_ARRAY(value1[,value2[,…]])参数value1[,value2[,…]]可选的。一些值，他们将被放在JSON

MySQL JSON_ARRAY() 函数返回一个包含了所有参数的 JSON 数组。

JSON_ARRAY() 语法

这里是 MySQL JSON_ARRAY() 的语法：

JSON_ARRAY(value1[, value2[, ...]])

参数

• value1[, value2[, …]]

  可选的。一些值，他们将被放在 JSON 数组中。

返回值

JSON_ARRAY() 函数评估参数中的所有的值，并返回一个包含了所有参数的 JSON 数组。

这里可能有一些转换发生：

• TRUE 被转换为 true
• FALSE 被转换为 false
• NULL 被转换为 null
• 日期，时间，日期时间 被转换为 字符串

JSON_ARRAY() 示例

这里列出了几个常见的 JSON_ARRAY() 示例。

示例 1

SELECT JSON_ARRAY(123, 'abc', NULL, TRUE, FALSE, NOW());
+---------------------------------------------------------------+
| JSON_ARRAY(123, 'abc', NULL, TRUE, FALSE, NOW())              |
+---------------------------------------------------------------+
| [123, "abc", null, true, false, "2022-04-18 07:47:23.000000"] |
+---------------------------------------------------------------+

包含数组的数组

SELECT JSON_ARRAY(JSON_ARRAY(123, 456), JSON_ARRAY('abc', 'dec'));
+------------------------------------------------------------+
| JSON_ARRAY(JSON_ARRAY(123, 456), JSON_ARRAY('abc', 'dec')) |
+------------------------------------------------------------+
| [[123, 456], ["abc", "dec"]]                               |
+------------------------------------------------------------+

这里， 我们使用了 JSON_ARRAY() 的结果作为 JSON_ARRAY() 的参数。

包含对象的数组

SELECT JSON_ARRAY(
        JSON_OBJECT('name', 'Jim', 'age', 20),
        JSON_OBJECT('name', 'Tim', 'age', 18)
    ) AS objct_array;
+----------------------------------------------------------+
| objct_array                                              |
+----------------------------------------------------------+
| [{"age": 20, "name": "Jim"}, {"age": 18, "name": "Tim"}] |
+----------------------------------------------------------+

JSON_ARRAY_APPEND() 函数

MySQLJSON_ARRAY_APPEND()函数向JSON文档中的指定的数组中追加一个值并返回修改后的JSON文档。JSON_ARRAY_APPEND()语法这里是MySQLJSON_ARRAY_APPEND()的语法：JSON_ARRAY_APPEND(json,path,value[,pat

MySQL JSON_ARRAY_APPEND() 函数向 JSON 文档中的指定的数组中追加一个值并返回修改后的 JSON 文档。

JSON_ARRAY_APPEND() 语法

这里是 MySQL JSON_ARRAY_APPEND() 的语法：

JSON_ARRAY_APPEND(json, path, value[, path2, value2] ...)

参数

• json

  必需的。被修改的 JSON 文档。

• path

  必需的。添加新元素的路径。一个有效的路径表达式，它不能包含 * 或 **。

• value

  必需的。被添加到数组的新元素值。

返回值

JSON_ARRAY_APPEND() 函数用来修改 JSON 文档，它向指定的数组节点中追加一个元素，并返回修改后的 JSON 文档。

• 如果路径表达式指示的节点不是一个数组节点，JSON_ARRAY_APPEND() 函数将会将此节点的值作为数组的第一个元素，并在数组的尾部追加新元素。
• 如果 JSON 文档或者路径为 NULL，此函数将返回 NULL。

JSON_ARRAY_APPEND() 函数将在以下情况下返回错误：

• 如果参数 json 不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用 JSON_VALID() 验证 JSON 文档的有效性。
• 如果参数 path 不是有效的路径表达式或者其中包含 * 或 **, MySQL 将会给出错误。

JSON_ARRAY_APPEND() 示例

这里列出了几个常见的 JSON_ARRAY_APPEND() 用法示例。

在数组末尾追加元素

SELECT JSON_ARRAY_APPEND('[1, 2, 3]', '$', 4);
+----------------------------------------+
| JSON_ARRAY_APPEND('[1, 2, 3]', '$', 4) |
+----------------------------------------+
| [1, 2, 3, 4]                           |
+----------------------------------------+

这里， 路径表达式 $ 表示正在操作的 JSON 文档。

向内嵌数组中追加元素

SELECT JSON_ARRAY_APPEND('[1, [2, 3]]', '$[1]', 4);
+---------------------------------------------+
| JSON_ARRAY_APPEND('[1, [2, 3]]', '$[1]', 4) |
+---------------------------------------------+
| [1, [2, 3, 4]]                              |
+---------------------------------------------+

这里， 路径表达式 $[1] 表示正在操作的 JSON 文档数组中的第 2 个元素。

向对象中的数组中追加元素

SELECT JSON_ARRAY_APPEND('{"name": "Tim", "hobby": ["car"]}', '$.hobby', "food");
+---------------------------------------------------------------------------+
| JSON_ARRAY_APPEND('{"name": "Tim", "hobby": ["car"]}', '$.hobby', "food") |
+---------------------------------------------------------------------------+
| {"name": "Tim", "hobby": ["car", "food"]}                                 |
+---------------------------------------------------------------------------+

这里， 路径表达式 $.hobby 表示正在操作的 JSON 文档对象的成员 hobby。

向非数组中追加值

SELECT JSON_ARRAY_APPEND('1', '$', 2);
+--------------------------------+
| JSON_ARRAY_APPEND('1', '$', 2) |
+--------------------------------+
| [1, 2]                         |
+--------------------------------+

这里，JSON 文档有元素 1 修改成了数组 [1, 2]。

同样可以向数组中的非数组成员中追加，比如：

SELECT JSON_ARRAY_APPEND('[1, 2]', '$[1]', 3);
+----------------------------------------+
| JSON_ARRAY_APPEND('[1, 2]', '$[1]', 3) |
+----------------------------------------+
| [1, [2, 3]]                            |
+----------------------------------------+

还可以向对象中的非数组成员中追加，比如：

SELECT JSON_ARRAY_APPEND('{"name": "Tim", "hobby": "car"}', '$.hobby', "food");
+-------------------------------------------------------------------------+
| JSON_ARRAY_APPEND('{"name": "Tim", "hobby": "car"}', '$.hobby', "food") |
+-------------------------------------------------------------------------+
| {"name": "Tim", "hobby": ["car", "food"]}                               |
+-------------------------------------------------------------------------+

JSON_ARRAY_INSERT() 函数

MySQLJSON_ARRAY_INSERT()函数向JSON文档中的指定的数组中的指定位置插入一个值并返回新的JSON文档。JSON_ARRAY_INSERT()语法这里是MySQLJSON_ARRAY_INSERT()的语法：JSON_ARRAY_INSERT(json,path,value[,

MySQL JSON_ARRAY_INSERT() 函数向 JSON 文档中的指定的数组中的指定位置插入一个值并返回新的 JSON 文档。

JSON_ARRAY_INSERT() 语法

这里是 MySQL JSON_ARRAY_INSERT() 的语法：

JSON_ARRAY_INSERT(json, path, value[, path2, value2] ...)

参数

• json

  必需的。被修改的 JSON 文档。

• path

  必需的。插入新元素的数组元素位置。一个有效的路径表达式，它不能包含 * 或 **。比如 $[0] 和 $.a[0] 表示在数组的开头插入新元素。

• value

  必需的。被插入到数组的新元素值。

返回值

JSON_ARRAY_INSERT() 函数用来修改 JSON 文档，它向 JSON 文档中的指定的数组中的指定位置插入一个值并返回新的 JSON 文档。

如果路径表达式指示的数组元素超过了数组的长度，那么新元素将插入到数组的尾部。

如果 JSON 文档或者路径为 NULL，此函数将返回 NULL。

JSON_ARRAY_INSERT() 函数将在以下情况下返回错误：

• 如果参数 json 不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用 JSON_VALID() 验证 JSON 文档的有效性。
• 如果参数 path 不是有效的路径表达式或者其中包含 * 或 **, MySQL 将会给出错误。
• 如果参数 path 指示的不是数组元素的路径, MySQL 将会给出错误。

JSON_ARRAY_INSERT() 示例

这里列出了几个常见的 JSON_ARRAY_INSERT() 用法示例。

让我们首先创建一个 JSON 文档以便于演示下面的示例：

SET @json = '[1, [2, 3], {"a": [4, 5]}]';

在数组指定位置插入元素

让我们在数组的开头插入一个元素：

SELECT JSON_ARRAY_INSERT(@json, '$[0]', 0);
+-------------------------------------+
| JSON_ARRAY_INSERT(@json, '$[0]', 0) |
+-------------------------------------+
| [0, 1, [2, 3], {"a": [4, 5]}]       |
+-------------------------------------+

让我们将新元素插入到数组的第 3 个元素的位置：

SELECT JSON_ARRAY_INSERT(@json, '$[2]', 4);
+-------------------------------------+
| JSON_ARRAY_INSERT(@json, '$[2]', 4) |
+-------------------------------------+
| [1, [2, 3], 4, {"a": [4, 5]}]       |
+-------------------------------------+

让我们在数组的末尾插入新元素：

SELECT JSON_ARRAY_INSERT(@json, '$[3]', 'x');
+---------------------------------------+
| JSON_ARRAY_INSERT(@json, '$[3]', 'x') |
+---------------------------------------+
| [1, [2, 3], {"a": [4, 5]}, "x"]       |
+---------------------------------------+

向内嵌数组中插入元素

SELECT JSON_ARRAY_INSERT(@json, '$[1][0]', 'x');
+------------------------------------------+
| JSON_ARRAY_INSERT(@json, '$[1][0]', 'x') |
+------------------------------------------+
| [1, ["x", 2, 3], {"a": [4, 5]}]          |
+------------------------------------------+

向对象中的数组中插入元素

SELECT JSON_ARRAY_INSERT(@json, '$[2].a[0]', 'x');
+--------------------------------------------+
| JSON_ARRAY_INSERT(@json, '$[2].a[0]', 'x') |
+--------------------------------------------+
| [1, [2, 3], {"a": ["x", 4, 5]}]            |
+--------------------------------------------+

JSON_CONTAINS() 函数

MySQLJSON_CONTAINS()函数检查一个JSON文档中是否包含另一个JSON文档。如果您需要检查JSON文档中指定的路径下的是否存在数据，请使用JSON_CONTAINS_PATH()函数。JSON_CONTAINS()语法这里是MySQLJSON_CONTAINS()的语法：JSON_

MySQL JSON_CONTAINS() 函数检查一个 JSON 文档中是否包含另一个 JSON 文档。

如果您需要检查 JSON 文档中指定的路径下的是否存在数据，请使用 JSON_CONTAINS_PATH() 函数。

JSON_CONTAINS() 语法

这里是 MySQL JSON_CONTAINS() 的语法：

JSON_CONTAINS(target_json, candidate_json)
JSON_CONTAINS(target_json, candidate_json, path)

参数

• target_json

  必需的。一个 JSON 文档。

• candidate_json

  必需的。被包含的 JSON 文档。

• path

  可选的。一个路径表达式。

返回值

如果在 JSON 文档 target_json 中包含了 JSON 文档 candidate_json，JSON_CONTAINS() 函数将返回 1，否则返回 0。如果提供了 path 参数，则检查由 path 匹配的部分是否包含 candidate_json JSON 文档。

如果存在以下的情况， JSON_CONTAINS() 函数将返回 NULL：

• 如果 JSON 文档中不存在指定的路径。
• 如果任意一个参数为 NULL。

JSON_CONTAINS() 函数将在以下情况下返回错误：

• 如果参数 json 不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用 JSON_VALID() 验证 JSON 文档的有效性。
• 如果参数 path 不是有效的路径表达式, MySQL 将会给出错误。

JSON_CONTAINS() 示例

这里列出了几个常见的 JSON_CONTAINS() 用法示例。

示例: 数组

SELECT
    JSON_CONTAINS('[1, 2, {"x": 3}]', '1') as `1`,
    JSON_CONTAINS('[1, 2, {"x": 3}]', '{"x": 3}') as `{"x": 3}`,
    JSON_CONTAINS('[1, 2, {"x": 3}]', '3') as `3`;
+------+----------+------+
| 1    | {"x": 3} | 3    |
+------+----------+------+
|    1 |        1 |    0 |
+------+----------+------+

示例: 指定路径

SELECT
    JSON_CONTAINS('[1, 2, [3, 4]]', '2'),
    JSON_CONTAINS('[1, 2, [3, 4]]', '2', '$[2]');
+--------------------------------------+----------------------------------------------+
| JSON_CONTAINS('[1, 2, [3, 4]]', '2') | JSON_CONTAINS('[1, 2, [3, 4]]', '2', '$[2]') |
+--------------------------------------+----------------------------------------------+
|                                    1 |                                            0 |
+--------------------------------------+----------------------------------------------+

这里，我们已经在 JSON_CONTAINS('[1, 2, [3, 4]]', '2', '$[2]') 指定了路径表达式 $[2]，其匹配的内容是 [3, 4]。 [3, 4] 中没有包含 2， 因此，它返回了 0。

JSON_CONTAINS_PATH() 函数

MySQLJSON_CONTAINS_PATH()函数检查一个JSON文档中在指定的路径上是否有值存在。JSON_CONTAINS_PATH()语法这里是MySQLJSON_CONTAINS_PATH()的语法：JSON_CONTAINS_PATH(json,one_or_all,path[,pat

MySQL JSON_CONTAINS_PATH() 函数检查一个 JSON 文档中在指定的路径上是否有值存在。

JSON_CONTAINS_PATH() 语法

这里是 MySQL JSON_CONTAINS_PATH() 的语法：

JSON_CONTAINS_PATH(json, one_or_all, path[, path])

参数

• json

  必需的。一个 JSON 文档。

• one_or_all

  必需的。可用值：'one', 'all'。它指示是否检查所有的路径。

• path

  必需的。您应该至少指定一个路径表达式。

返回值

如果 JSON 文档在指定的路径上有值，JSON_CONTAINS_PATH() 函数将返回 1，否则返回 0。

JSON_CONTAINS_PATH() 根据 one_or_all 参数决定是否检查所有的路径：

• 如果是 'one'，且至少有一个路径上有值，JSON_CONTAINS_PATH() 函数将返回 1，否则返回 0。
• 如果是 'all'，且所有的路径上有值，JSON_CONTAINS_PATH() 函数将返回 1，否则返回 0。

如果任意一个参数为 NULL， JSON_CONTAINS_PATH() 函数将返回 NULL。

JSON_CONTAINS_PATH() 函数将在以下情况下返回错误：

• 如果参数 json 不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用 JSON_VALID() 验证 JSON 文档的有效性。
• 如果参数 path 不是有效的路径表达式, MySQL 将会给出错误。

JSON_CONTAINS_PATH() 示例

这里列出了几个常见的 JSON_CONTAINS() 用法示例。

示例: 数组

SELECT
    JSON_CONTAINS_PATH('[1, 2, {"x": 3}]', 'all', '$[0]') as `$[0]`,
    JSON_CONTAINS_PATH('[1, 2, {"x": 3}]', 'all', '$[3]') as `$[3]`,
    JSON_CONTAINS_PATH('[1, 2, {"x": 3}]', 'all', '$[2].x') as `$[2].x`;
+------+------+--------+
| $[0] | $[3] | $[2].x |
+------+------+--------+
|    1 |    0 |      1 |
+------+------+--------+

示例: one vs all

SELECT
    JSON_CONTAINS_PATH('[1, 2, {"x": 3}]', 'one', '$[0]', '$[3]') as `one`,
    JSON_CONTAINS_PATH('[1, 2, {"x": 3}]', 'all', '$[0]', '$[3]') as `all`;
+------+------+
| one  | all  |
+------+------+
|    1 |    0 |
+------+------+

JSON_DEPTH() 函数

MySQLJSON_DEPTH()函数返回一个JSON文档的最大深度。JSON_DEPTH()语法这里是MySQLJSON_DEPTH()的语法：JSON_DEPTH(json)参数json必需的。一个JSON文档。返回值JSON_DEPTH()函数返回一个JSON文档的最大深度。JSON_DEPT

MySQL JSON_DEPTH() 函数返回一个 JSON 文档的最大深度。

JSON_DEPTH() 语法

这里是 MySQL JSON_DEPTH() 的语法：

JSON_DEPTH(json)

参数

• json

  必需的。一个 JSON 文档。

返回值

JSON_DEPTH() 函数返回一个 JSON 文档的最大深度。JSON_DEPTH() 函数按如下规则计算 JSON 文档的深度：

• 一个空的数组、空的对象或者纯值的深度是 1。
• 一个仅包含深度为 1 的元素的数组的深度是 2。
• 一个所有成员的值的深度为 1 的对象的深度是 2。
• 除此之外的其他 JSON 文档的深度都大于 2.

如果参数为 NULL，此函数将返回 NULL。

如果参数不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用 JSON_VALID() 验证 JSON 文档的有效性。

JSON_DEPTH() 示例

这里列出了几个常见的 JSON_DEPTH() 用法示例。

示例 1

SELECT JSON_DEPTH('[]'), JSON_DEPTH('[1, 2]'), JSON_DEPTH('[1, [2, 3]]');
+------------------+----------------------+---------------------------+
| JSON_DEPTH('[]') | JSON_DEPTH('[1, 2]') | JSON_DEPTH('[1, [2, 3]]') |
+------------------+----------------------+---------------------------+
|                1 |                    2 |                         3 |
+------------------+----------------------+---------------------------+

示例 2

SELECT JSON_DEPTH('{}'), JSON_DEPTH('{"x": 1}'), JSON_DEPTH('{"x": {"y": 1}}');
+------------------+------------------------+-------------------------------+
| JSON_DEPTH('{}') | JSON_DEPTH('{"x": 1}') | JSON_DEPTH('{"x": {"y": 1}}') |
+------------------+------------------------+-------------------------------+
|                1 |                      2 |                             3 |
+------------------+------------------------+-------------------------------+

JSON_EXTRACT() 函数

MySQLJSON_EXTRACT()函数在JSON文档提取路径表达式指定的数据并返回。JSON_EXTRACT()语法这里是MySQLJSON_EXTRACT()的语法：JSON_EXTRACT(json,path,…)参数json必需的。一个JSON文档。path必需的。您应该至少指定一个路

MySQL JSON_EXTRACT() 函数在 JSON 文档提取路径表达式指定的数据并返回。

JSON_EXTRACT() 语法

这里是 MySQL JSON_EXTRACT() 的语法：

JSON_EXTRACT(json, path, ...)

参数

• json

  必需的。一个 JSON 文档。

• path

  必需的。您应该至少指定一个路径表达式。

返回值

JSON_EXTRACT() 函数返回 JSON 文档中由路径表达式匹配的所有的值。如果路径表达式匹配了一个值，则返回该值，如果路径表达式匹配了多个值，则返回一个包含了所有值的数组。

如果存在以下的情况， JSON_EXTRACT() 函数将返回 NULL：

• 如果 JSON 文档中不存在指定的路径。
• 如果任意一个参数为 NULL。

JSON_EXTRACT() 函数将在以下情况下返回错误：

• 如果参数 json 不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用 JSON_VALID() 验证 JSON 文档的有效性。
• 如果参数 path 不是有效的路径表达式, MySQL 将会给出错误。

JSON_EXTRACT() 示例

这里列出了几个常见的 JSON_EXTRACT() 用法示例。

示例: 数组

下面的语句展示了如何从数组中提取一个元素：

SELECT JSON_EXTRACT('[1, 2, {"x": 3}]', '$[2]');
+------------------------------------------+
| JSON_EXTRACT('[1, 2, {"x": 3}]', '$[2]') |
+------------------------------------------+
| {"x": 3}                                 |
+------------------------------------------+

让我们再看一个带有多个路径参数的例子：

SELECT JSON_EXTRACT('[1, 2, {"x": 3}]', '$[2].x', '$[1]', '$[0]');
+------------------------------------------------------------+
| JSON_EXTRACT('[1, 2, {"x": 3}]', '$[2].x', '$[1]', '$[0]') |
+------------------------------------------------------------+
| [3, 2, 1]                                                  |
+------------------------------------------------------------+

示例: 对象

下面的语句展示了如何从对象中提取一个节点的值：

SELECT JSON_EXTRACT('{"x": 1, "y": [1, 2]}', '$.y');
+----------------------------------------------+
| JSON_EXTRACT('{"x": 1, "y": [1, 2]}', '$.y') |
+----------------------------------------------+
| [1, 2]                                       |
+----------------------------------------------+

让我们再看一个带有多个路径参数的例子：

SELECT JSON_EXTRACT('{"x": 1, "y": [1, 2]}', '$.x', '$.y');
+-----------------------------------------------------+
| JSON_EXTRACT('{"x": 1, "y": [1, 2]}', '$.x', '$.y') |
+-----------------------------------------------------+
| [1, [1, 2]]                                         |
+-----------------------------------------------------+

JSON_INSERT() 函数

MySQLJSON_INSERT()函数向一个JSON文档中插入数据并返回新的JSON文档。JSON_INSERT()语法这里是MySQLJSON_INSERT()的语法：JSON_INSERT(json,path,value[,path2,value2]…)参数json必需的。被修改的JSON

MySQL JSON_INSERT() 函数向一个 JSON 文档中插入数据并返回新的 JSON 文档。

JSON_INSERT() 语法

这里是 MySQL JSON_INSERT() 的语法：

JSON_INSERT(json, path, value[, path2, value2] ...)

参数

• json

  必需的。被修改的 JSON 文档。

• path

  必需的。一个有效的路径表达式，它不能包含 * 或 **。

• value

  必需的。被插入的数据。

返回值

JSON_INSERT() 函数向一个 JSON 文档中插入数据并返回新的 JSON 文档。您可以提供多对 path-value 参数，以便一次插入多个数据。

JSON_INSERT() 函数只能将数据插入到不存在路径。如果 JSON 文档中已经存在指定的路径，则不会插入数据。

如果 value 为字符串， JSON_INSERT() 函数会将其作为字符串写入到 JSON 文档中。为了保证写入到 JSON 文档中的值的类型正确，请对 value 使用 JSON 类型的数据。

如果 path 为 $，JSON_INSERT() 函数会返回原 JSON 文档。

如果 JSON 文档或者路径为 NULL，此函数将返回 NULL。

JSON_INSERT() 函数将在以下情况下返回错误：

• 如果参数 json 不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用 JSON_VALID() 验证 JSON 文档的有效性。
• 如果参数 path 不是有效的路径表达式或者其中包含 * 或 **, MySQL 将会给出错误。

JSON_INSERT() 示例

插入到数组

让我们首先创建一个 JSON 文档以便于演示下面的示例：

SET @array = '[1, [2, 3], {"a": [4, 5]}]';

让我们在数组的开头插入一个元素：

SELECT JSON_INSERT(@array, '$[0]', 0, '$[3]', 6);

这里，我们想要在数组的开头插入 0，在数组的尾部插入 6。让我们看一下结果：

+-------------------------------------------+
| JSON_INSERT(@array, '$[0]', 0, '$[3]', 6) |
+-------------------------------------------+
| [1, [2, 3], {"a": [4, 5]}, 6]             |
+-------------------------------------------+

我们发现，并没有在数据的开头($[0])插入数据。这是因为数组在 $[0] 位置已经有了值，因此不会插入。而数组在 $[3] 位置是没有数据的，因此 6 被插入到数组的尾部。

您还可以使用 JSON_ARRAY_APPEND() 向数组中插入数据。

插入 JSON 类型数据

除了插入简单的字面值，我们还可以插入复杂的 JSON 元素，比如数组和对象。

让我们首先创建一个包含 JSON 对象的 JSON 文档：

SET @obj = '{"x": 1}';

现在让我们插入一个值为 true 的成员 y:

SELECT JSON_INSERT(@obj, '$.y', 'true');
+----------------------------------+
| JSON_INSERT(@obj, '$.y', 'true') |
+----------------------------------+
| {"x": 1, "y": "true"}            |
+----------------------------------+

我们发现， true 变成了 "true"。而不是我们希望的 {"x": 1, "y": true}。

这是因为，如果 value 参数为字符串， JSON_INSERT() 函数会将其作为字符串写入到 JSON 文档中。我们再看几个相似的例子：

SELECT JSON_INSERT(@obj, '$.y', '[1, 2]');
+------------------------------------+
| JSON_INSERT(@obj, '$.y', '[1, 2]') |
+------------------------------------+
| {"x": 1, "y": "[1, 2]"}            |
+------------------------------------+

或者

SELECT JSON_INSERT(@obj, '$.y', '{"z": 2}');
+--------------------------------------+
| JSON_INSERT(@obj, '$.y', '{"z": 2}') |
+--------------------------------------+
| {"x": 1, "y": "{\"z\": 2}"}          |
+--------------------------------------+

为了解决这个问题，我们可以使用 CAST() 函数将数据转为 JSON 类型，比如：

SELECT JSON_INSERT(@obj, '$.y', CAST('{"z": 2}' AS JSON));
+----------------------------------------------------+
| JSON_INSERT(@obj, '$.y', CAST('{"z": 2}' AS JSON)) |
+----------------------------------------------------+
| {"x": 1, "y": {"z": 2}}                            |
+----------------------------------------------------+

JSON_KEYS() 函数

MySQLJSON_KEYS()函数返回一个包含了指定的JSON对象中最上层的成员(key)的数组。JSON_KEYS()语法这里是MySQLJSON_KEYS()的语法：JSON_KEYS(json)JSON_KEYS(json,path)参数json必需的。一个JSON对象文档。path可选的。

MySQL JSON_KEYS() 函数返回一个包含了指定的 JSON 对象中最上层的成员 (key) 的数组。

JSON_KEYS() 语法

这里是 MySQL JSON_KEYS() 的语法：

JSON_KEYS(json)
JSON_KEYS(json, path)

参数

• json

  必需的。一个 JSON 对象文档。

• path

  可选的。路径表达式。

返回值

MySQL JSON_KEYS() 函数返回一个包含了指定的 JSON 对象中最上层的成员 (key) 的数组。如果指定了路径表达式，则返回路径表达式匹配的 JSON 对象中的最上层的成员组成的数组。

如果存在以下的情况， JSON_KEYS() 函数将返回 NULL：

• 未指定路径，且 JSON 文档不是一个 JSON 对象。
• 指定了路径，且路径匹配的 JSON 值不是 JSON 对象。
• 任意参数为 NULL。

JSON_KEYS() 函数将在以下情况下返回错误：

• 如果参数 json 不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用 JSON_VALID() 验证 JSON 文档的有效性。
• 如果参数 path 不是有效的路径表达式, MySQL 将会给出错误。

JSON_KEYS() 示例

这里列出了几个常见的 JSON_KEYS() 用法示例。

下面的示例返回一个 JSON 对象的所有顶层成员组成的数组。

SELECT JSON_KEYS('{"x": 1, "y": 2, "z": 3}');
+---------------------------------------+
| JSON_KEYS('{"x": 1, "y": 2, "z": 3}') |
+---------------------------------------+
| ["x", "y", "z"]                       |
+---------------------------------------+

我们也可以返回有路径表达式匹配的 JSON 对象的键。

SELECT JSON_KEYS('[0, {"x": 1, "y": 2, "z": 3}]', '$[1]');
+----------------------------------------------------+
| JSON_KEYS('[0, {"x": 1, "y": 2, "z": 3}]', '$[1]') |
+----------------------------------------------------+
| ["x", "y", "z"]                                    |
+----------------------------------------------------+

如果匹配的 JSON 文档不是 JSON 对象，JSON_KEYS() 返回 NULL。这个示例说明了这一点：

SELECT
    JSON_KEYS('1') as `keys of 1`,
    JSON_KEYS('true') as `keys of true`,
    JSON_KEYS('"hello"') as `keys of "hello"`,
    JSON_KEYS('[1, 2]') as `keys of [1, 2]`;
+-----------+--------------+-----------------+----------------+
| keys of 1 | keys of true | keys of "hello" | keys of [1, 2] |
+-----------+--------------+-----------------+----------------+
| NULL      | NULL         | NULL            | NULL           |
+-----------+--------------+-----------------+----------------+

JSON_LENGTH() 函数

MySQLJSON_LENGTH()函数返回JSON文档或者JSON文档中通过路径指定的节点的长度。JSON_LENGTH()语法这里是MySQLJSON_LENGTH()的语法：JSON_LENGTH(json)JSON_LENGTH(json,path)参数json必需的。一个JSON文档。pa

MySQL JSON_LENGTH() 函数返回 JSON 文档或者 JSON 文档中通过路径指定的节点的长度。

JSON_LENGTH() 语法

这里是 MySQL JSON_LENGTH() 的语法：

JSON_LENGTH(json)
JSON_LENGTH(json, path)

参数

• json

  必需的。一个 JSON 文档。

• path

  可选的。一个路径表达式。

返回值

如果指定了 path, JSON_LENGTH() 函数返回 JSON 文档中由路径指定的值的长度，否则返回 JSON 文档的长度。JSON_LENGTH() 函数按照如下规则计算 JSON 文档的长度:

• 纯值的长度是 1。比如， 1, '"x"', true, false, null 的长度都是 1。
• 数组的长度是数组元素的数量。 比如， [1, 2] 的长度是 2。
• 对象的长度是对象成员的数量。 比如， {"x": 1} 的长度是 1。
• 内嵌的数组或对象不参与计算长度。 比如， {"x": [1, 2]} 的长度是 1。

如果存在以下的情况， JSON_LENGTH() 函数将返回 NULL：

• 如果 JSON 文档中不存在指定的路径。
• 如果任意一个参数为 NULL。

JSON_LENGTH() 函数将在以下情况下返回错误：

• 如果参数 json 不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用 JSON_VALID() 验证 JSON 文档的有效性。
• 如果参数 path 不是有效的路径表达式, MySQL 将会给出错误。
• 在 MySQL 8.0.26 之前，如果参数 path 中包含 * 或 **, MySQL 将会给出错误。

JSON_LENGTH() 示例

这里列出了几个常见的 JSON_LENGTH() 用法示例。

示例: 值的长度

SELECT
    JSON_LENGTH('1') as `1`,
    JSON_LENGTH('true') as `true`,
    JSON_LENGTH('false') as `false`,
    JSON_LENGTH('null') as `null`,
    JSON_LENGTH('"abc"') as `"abc"`;
+------+------+-------+------+-------+
| 1    | true | false | null | "abc" |
+------+------+-------+------+-------+
|    1 |    1 |     1 |    1 |     1 |
+------+------+-------+------+-------+

示例: 数组的长度

SELECT
    JSON_LENGTH('[]') as `[]`,
    JSON_LENGTH('[1, 2]') as `[1, 2]`,
    JSON_LENGTH('[1, {"x": 2}]') as `[1, {"x": 2}]`;
+------+--------+---------------+
| []   | [1, 2] | [1, {"x": 2}] |
+------+--------+---------------+
|    0 |      2 |             2 |
+------+--------+---------------+

示例: 对象的长度

SELECT
    JSON_LENGTH('{}') as `[]`,
    JSON_LENGTH('{"x": 1, "y": 2}') as `{"x": 1, "y": 2}`,
    JSON_LENGTH('{"x": 1, "y": {"z" : 2}}') as `{"x": 1, "y": {"z" : 2}}`;
+------+------------------+--------------------------+
| []   | {"x": 1, "y": 2} | {"x": 1, "y": {"z" : 2}} |
+------+------------------+--------------------------+
|    0 |                2 |                        2 |
+------+------------------+--------------------------+

示例: 路径

您可以获取 JSON 文档中通过路径表达式指定的节点值的长度。

SELECT JSON_LENGTH('{"x": 1, "y": [1, 2]}', '$.y');
+---------------------------------------------+
| JSON_LENGTH('{"x": 1, "y": [1, 2]}', '$.y') |
+---------------------------------------------+
|                                           2 |
+---------------------------------------------+

这里， 路径表达式 $.y 对应的值是 [1, 2]，[1, 2] 的长度为 2。这相当先使用 JSON_EXTRACT() 函数提取路径匹配的部分，再计算长度，如下：

SELECT JSON_LENGTH(JSON_EXTRACT('{"x": 1, "y": [1, 2]}', '$.y'));
+-----------------------------------------------------------+
| JSON_LENGTH(JSON_EXTRACT('{"x": 1, "y": [1, 2]}', '$.y')) |
+-----------------------------------------------------------+
|                                                         2 |
+-----------------------------------------------------------+

JSON_LENGTH() 函数

MySQLJSON_LENGTH()函数返回JSON文档或者JSON文档中通过路径指定的节点的长度。JSON_LENGTH()语法这里是MySQLJSON_LENGTH()的语法：JSON_LENGTH(json)JSON_LENGTH(json,path)参数json必需的。一个JSON文档。pa

MySQL JSON_LENGTH() 函数返回 JSON 文档或者 JSON 文档中通过路径指定的节点的长度。

JSON_LENGTH() 语法

这里是 MySQL JSON_LENGTH() 的语法：

JSON_LENGTH(json)
JSON_LENGTH(json, path)

参数

• json

  必需的。一个 JSON 文档。

• path

  可选的。一个路径表达式。

返回值

如果指定了 path, JSON_LENGTH() 函数返回 JSON 文档中由路径指定的值的长度，否则返回 JSON 文档的长度。JSON_LENGTH() 函数按照如下规则计算 JSON 文档的长度:

• 纯值的长度是 1。比如， 1, '"x"', true, false, null 的长度都是 1。
• 数组的长度是数组元素的数量。 比如， [1, 2] 的长度是 2。
• 对象的长度是对象成员的数量。 比如， {"x": 1} 的长度是 1。
• 内嵌的数组或对象不参与计算长度。 比如， {"x": [1, 2]} 的长度是 1。

如果存在以下的情况， JSON_LENGTH() 函数将返回 NULL：

• 如果 JSON 文档中不存在指定的路径。
• 如果任意一个参数为 NULL。

JSON_LENGTH() 函数将在以下情况下返回错误：

• 如果参数 json 不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用 JSON_VALID() 验证 JSON 文档的有效性。
• 如果参数 path 不是有效的路径表达式, MySQL 将会给出错误。
• 在 MySQL 8.0.26 之前，如果参数 path 中包含 * 或 **, MySQL 将会给出错误。

JSON_LENGTH() 示例

这里列出了几个常见的 JSON_LENGTH() 用法示例。

示例: 值的长度

SELECT
    JSON_LENGTH('1') as `1`,
    JSON_LENGTH('true') as `true`,
    JSON_LENGTH('false') as `false`,
    JSON_LENGTH('null') as `null`,
    JSON_LENGTH('"abc"') as `"abc"`;
+------+------+-------+------+-------+
| 1    | true | false | null | "abc" |
+------+------+-------+------+-------+
|    1 |    1 |     1 |    1 |     1 |
+------+------+-------+------+-------+

示例: 数组的长度

SELECT
    JSON_LENGTH('[]') as `[]`,
    JSON_LENGTH('[1, 2]') as `[1, 2]`,
    JSON_LENGTH('[1, {"x": 2}]') as `[1, {"x": 2}]`;
+------+--------+---------------+
| []   | [1, 2] | [1, {"x": 2}] |
+------+--------+---------------+
|    0 |      2 |             2 |
+------+--------+---------------+

示例: 对象的长度

SELECT
    JSON_LENGTH('{}') as `[]`,
    JSON_LENGTH('{"x": 1, "y": 2}') as `{"x": 1, "y": 2}`,
    JSON_LENGTH('{"x": 1, "y": {"z" : 2}}') as `{"x": 1, "y": {"z" : 2}}`;
+------+------------------+--------------------------+
| []   | {"x": 1, "y": 2} | {"x": 1, "y": {"z" : 2}} |
+------+------------------+--------------------------+
|    0 |                2 |                        2 |
+------+------------------+--------------------------+

示例: 路径

您可以获取 JSON 文档中通过路径表达式指定的节点值的长度。

SELECT JSON_LENGTH('{"x": 1, "y": [1, 2]}', '$.y');
+---------------------------------------------+
| JSON_LENGTH('{"x": 1, "y": [1, 2]}', '$.y') |
+---------------------------------------------+
|                                           2 |
+---------------------------------------------+

这里， 路径表达式 $.y 对应的值是 [1, 2]，[1, 2] 的长度为 2。这相当先使用 JSON_EXTRACT() 函数提取路径匹配的部分，再计算长度，如下：

SELECT JSON_LENGTH(JSON_EXTRACT('{"x": 1, "y": [1, 2]}', '$.y'));
+-----------------------------------------------------------+
| JSON_LENGTH(JSON_EXTRACT('{"x": 1, "y": [1, 2]}', '$.y')) |
+-----------------------------------------------------------+
|                                                         2 |
+-----------------------------------------------------------+

JSON_OBJECT() 函数

MySQLJSON_OBJECT()函数返回一个包含了由参数指定的所有键值对的JSON对象。JSON_OBJECT()语法这里是MySQLJSON_OBJECT()的语法：JSON_OBJECT(key,value[,key2,value2,…])参数key必需的。对象中的键。value必需的。

MySQL JSON_OBJECT() 函数返回一个包含了由参数指定的所有键值对的 JSON 对象。

JSON_OBJECT() 语法

这里是 MySQL JSON_OBJECT() 的语法：

JSON_OBJECT(key, value[, key2, value2, ...])

参数

• key

  必需的。对象中的键。

• value

  必需的。对象中的 key 的值。

返回值

JSON_OBJECT() 函数评估参数中的所有的键值对，并返回一个包含了所有键值对的 JSON 对象。

由于 JSON 对象中的所有键为字符串，因此 JSON_OBJECT() 会将不是字符串类型的 key 转为字符串类型。为了保证程序的稳定性，我们一般使用字符串类型的 key.

执行过程中可能会出现如下错误：

• 如果 key 是 NULL， MySQL 将返回错误： ERROR 3158 (22032): JSON documents may not contain NULL member names.。
• 如果由奇数个参数， MySQL 将返回错误： ERROR 1582 (42000): Incorrect parameter count in the call to native function 'json_object'。

JSON_OBJECT() 示例

这里列出了几个常见的 JSON_OBJECT() 示例。

示例 1

SELECT JSON_OBJECT('name', 'Jim', 'age', 20);
+---------------------------------------+
| JSON_OBJECT('name', 'Jim', 'age', 20) |
+---------------------------------------+
| {"age": 20, "name": "Jim"}            |
+---------------------------------------+

这里， JSON_OBJECT() 返回的 JSON 对象中由两个成员： name 和 age。其中 name 的值为 'Jim'， age 的值为 20。

重复的键

如果 JSON_OBJECT() 的参数中出现了重复的键值对，那么后面的键值对保留在最终返回的对象中。

SELECT JSON_OBJECT('name', 'Jim', 'age', 20, 'name', 'Tim');
+------------------------------------------------------+
| JSON_OBJECT('name', 'Jim', 'age', 20, 'name', 'Tim') |
+------------------------------------------------------+
| {"age": 20, "name": "Tim"}                           |
+------------------------------------------------------+

包含复杂的对象

复杂的 JSON 对象可以存储更多的信息。

SELECT JSON_OBJECT(
        'name',
        'Tim',
        'age',
        20,
        'friend',
        JSON_OBJECT('name', 'Jim', 'age', 20),
        'hobby',
        JSON_ARRAY('games', 'sports')
    ) AS object;
+------------------------------------------------------------------------------------------------+
| object                                                                                         |
+------------------------------------------------------------------------------------------------+
| {"age": 20, "name": "Tim", "hobby": ["games", "sports"], "friend": {"age": 20, "name": "Jim"}} |
+------------------------------------------------------------------------------------------------+

这里， 我们创建了如下 JSON 对象：

{
  "age": 20,
  "name": "Tim",
  "hobby": ["games", "sports"],
  "friend": { "age": 20, "name": "Jim" }
}

其中：

hobby的值是个数组，通过JSON_ARRAY()` 函数计算得出.

friend的值是个对象，通过JSON_OBJECT()` 函数计算得出.

JSON_OVERLAPS() 函数

MySQLJSON_OVERLAPS()函数检测两个JSON文档是否拥有任何一个相同键值对或数组元素。JSON_OVERLAPS()语法这里是MySQLJSON_OVERLAPS()的语法：JSON_OVERLAPS(json1,json2)参数json1必需的。一个JSON文档。json2必需的。

MySQL JSON_OVERLAPS() 函数检测两个 JSON 文档是否拥有任何一个相同键值对或数组元素。

JSON_OVERLAPS() 语法

这里是 MySQL JSON_OVERLAPS() 的语法：

JSON_OVERLAPS(json1, json2)

参数

• json1

  必需的。一个 JSON 文档。

• json2

  必需的。另一个 JSON 文档。

返回值

JSON_OVERLAPS() 函数检测两个 JSON 文档是否拥有任何一个相同键值对或数组元素。如果两个 JSON 文档有重叠的内容，JSON_OVERLAPS() 函数返回 1，否则返回 0。

JSON_OVERLAPS() 函数按照如下规则比较两个 JSON 文档：

• 比较两个数组时，如果两个数组至少有一个相同的元素返回 1，否则返回 0。
• 比较两个对象时，如果两个对象至少有一个相同的键值对返回 1，否则返回 0。
• 比较两个纯值时，如果两个值相同返回 1，否则返回 0。
• 比较纯值和数组时，如果值是这个数组中的直接元素返回 1，否则返回 0。
• 比较纯值和对象的结果为 0。
• 比较数组和对象的结果为 0。
• JSON_OVERLAPS() 不会对参数的数据类型进行转换。

如果参数为 NULL，此函数将返回 NULL。

如果任意一个参数不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用 JSON_VALID() 验证 JSON 文档的有效性。

JSON_OVERLAPS() 示例

这里列出了几个常见的 JSON_OVERLAPS() 用法示例。

比较数组

SELECT JSON_OVERLAPS('[1, 2, 3]', '[3, 4, 5]');
+-----------------------------------------+
| JSON_OVERLAPS('[1, 2, 3]', '[3, 4, 5]') |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+

这里，由于两个数组都有共同的元素 3，因此 JSON_OVERLAPS() 返回了 1。也就是说 [1, 2, 3] 和 [3, 4, 5] 有重叠。

让我们再看一个例子：

SELECT JSON_OVERLAPS('[1, 2, [3]]', '[3, 4, 5]');
+-------------------------------------------+
| JSON_OVERLAPS('[1, 2, [3]]', '[3, 4, 5]') |
+-------------------------------------------+
|                                         0 |
+-------------------------------------------+

这里，由于 [1, 2, [3]] 中的元素 [3] 和 [3, 4, 5] 中的 3 是不同的，因此 JSON_OVERLAPS() 返回了 0。也就是说 [1, 2, [3]] 和 [3, 4, 5] 没有交集。

比较对象

SELECT
    JSON_OVERLAPS('{"x": 1}', '{"x": 1, "y": 2}'),
    JSON_OVERLAPS('{"x": 1}', '{"y": 2}');
+-----------------------------------------------+---------------------------------------+
| JSON_OVERLAPS('{"x": 1}', '{"x": 1, "y": 2}') | JSON_OVERLAPS('{"x": 1}', '{"y": 2}') |
+-----------------------------------------------+---------------------------------------+
|                                             1 |                                     0 |
+-----------------------------------------------+---------------------------------------+

这里， {"x": 1} 和 {"x": 1, "y": 2} 都有共同的键值对 "x": 1，因此 JSON_OVERLAPS() 返回了 1。 而 {"x": 1} 和 {"y": 2} 没有共同的键值对，因此 JSON_OVERLAPS() 返回了 0。

比较纯值和数组

SELECT
    JSON_OVERLAPS('[1, 2, 3]', '3'),
    JSON_OVERLAPS('[1, 2, [3]]', '3');
+---------------------------------+-----------------------------------+
| JSON_OVERLAPS('[1, 2, 3]', '3') | JSON_OVERLAPS('[1, 2, [3]]', '3') |
+---------------------------------+-----------------------------------+
|                               1 |                                 0 |
+---------------------------------+-----------------------------------+

这里，3 是 [1, 2, 3] 的元素，而不是 [1, 2, [3]] 的元素，因此他们返回了不同的结果。

比较纯值

SELECT JSON_OVERLAPS('1', '1'), JSON_OVERLAPS('1', '"1"');
+-------------------------+---------------------------+
| JSON_OVERLAPS('1', '1') | JSON_OVERLAPS('1', '"1"') |
+-------------------------+---------------------------+
|                       1 |                         0 |
+-------------------------+---------------------------+

MySQLJSON_OVERLAPS()函数

MySQL JSON_OVERLAPS() 函数检测两个 JSON 文档是否拥有任何一个相同键值对或数组元素。

JSON_OVERLAPS() 语法

这里是 MySQL JSON_OVERLAPS() 的语法：

JSON_OVERLAPS(json1, json2)

参数

• json1

  必需的。一个 JSON 文档。

• json2

  必需的。另一个 JSON 文档。

返回值

JSON_OVERLAPS() 函数检测两个 JSON 文档是否拥有任何一个相同键值对或数组元素。如果两个 JSON 文档有重叠的内容，JSON_OVERLAPS() 函数返回 1，否则返回 0。

JSON_OVERLAPS() 函数按照如下规则比较两个 JSON 文档：

• 比较两个数组时，如果两个数组至少有一个相同的元素返回 1，否则返回 0。
• 比较两个对象时，如果两个对象至少有一个相同的键值对返回 1，否则返回 0。
• 比较两个纯值时，如果两个值相同返回 1，否则返回 0。
• 比较纯值和数组时，如果值是这个数组中的直接元素返回 1，否则返回 0。
• 比较纯值和对象的结果为 0。
• 比较数组和对象的结果为 0。
• JSON_OVERLAPS() 不会对参数的数据类型进行转换。

如果参数为 NULL，此函数将返回 NULL。

如果任意一个参数不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用 JSON_VALID() 验证 JSON 文档的有效性。

JSON_OVERLAPS() 示例

这里列出了几个常见的 JSON_OVERLAPS() 用法示例。

比较数组

SELECT JSON_OVERLAPS('[1, 2, 3]', '[3, 4, 5]');
+-----------------------------------------+
| JSON_OVERLAPS('[1, 2, 3]', '[3, 4, 5]') |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+

这里，由于两个数组都有共同的元素 3，因此 JSON_OVERLAPS() 返回了 1。也就是说 [1, 2, 3] 和 [3, 4, 5] 有重叠。

让我们再看一个例子：

SELECT JSON_OVERLAPS('[1, 2, [3]]', '[3, 4, 5]');
+-------------------------------------------+
| JSON_OVERLAPS('[1, 2, [3]]', '[3, 4, 5]') |
+-------------------------------------------+
|                                         0 |
+-------------------------------------------+

这里，由于 [1, 2, [3]] 中的元素 [3] 和 [3, 4, 5] 中的 3 是不同的，因此 JSON_OVERLAPS() 返回了 0。也就是说 [1, 2, [3]] 和 [3, 4, 5] 没有交集。

比较对象

SELECT
    JSON_OVERLAPS('{"x": 1}', '{"x": 1, "y": 2}'),
    JSON_OVERLAPS('{"x": 1}', '{"y": 2}');
+-----------------------------------------------+---------------------------------------+
| JSON_OVERLAPS('{"x": 1}', '{"x": 1, "y": 2}') | JSON_OVERLAPS('{"x": 1}', '{"y": 2}') |
+-----------------------------------------------+---------------------------------------+
|                                             1 |                                     0 |
+-----------------------------------------------+---------------------------------------+

这里， {"x": 1} 和 {"x": 1, "y": 2} 都有共同的键值对 "x": 1，因此 JSON_OVERLAPS() 返回了 1。 而 {"x": 1} 和 {"y": 2} 没有共同的键值对，因此 JSON_OVERLAPS() 返回了 0。

比较纯值和数组

SELECT
    JSON_OVERLAPS('[1, 2, 3]', '3'),
    JSON_OVERLAPS('[1, 2, [3]]', '3');
+---------------------------------+-----------------------------------+
| JSON_OVERLAPS('[1, 2, 3]', '3') | JSON_OVERLAPS('[1, 2, [3]]', '3') |
+---------------------------------+-----------------------------------+
|                               1 |                                 0 |
+---------------------------------+-----------------------------------+

这里，3 是 [1, 2, 3] 的元素，而不是 [1, 2, [3]] 的元素，因此他们返回了不同的结果。

比较纯值

SELECT JSON_OVERLAPS('1', '1'), JSON_OVERLAPS('1', '"1"');
+-------------------------+---------------------------+
| JSON_OVERLAPS('1', '1') | JSON_OVERLAPS('1', '"1"') |
+-------------------------+---------------------------+
|                       1 |                         0 |
+-------------------------+---------------------------+

JSON_PRETTY() 函数

MySQLJSON_PRETTY()函数格式化输出一个JSON文档，以便更易于阅读。JSON_PRETTY()语法这里是MySQLJSON_PRETTY()的语法：JSON_PRETTY(json)参数json必需的。一个JSON文档或JSON类型的值。返回值JSON_PRETTY()函数格式化输出

MySQL JSON_PRETTY() 函数格式化输出一个 JSON 文档，以便更易于阅读。

JSON_PRETTY() 语法

这里是 MySQL JSON_PRETTY() 的语法：

JSON_PRETTY(json)

参数

• json

  必需的。一个 JSON 文档或 JSON 类型的值。

返回值

JSON_PRETTY() 函数格式化输出一个 JSON 文档，以便更易于阅读。

JSON_PRETTY() 函数按照如下美化输出 JSON 文档：

• 每个数组元素或每个对象成员都显示在单独的行上，与其父级相比，缩进一个附加级别。
• 每个缩进级别都会添加两个前导空格。
• 分隔各个数组元素或对象成员的逗号打印在分隔两个元素或成员的换行符之前。
• 对象成员的键和值由冒号后跟空格分隔（: ）。
• 空对象或数组打印在一行上。左括号和右括号之间未打印空格。
• 字符串标量和键名中的特殊字符使用与 JSON_QUOTE() 函数相同的规则进行转义。

如果参数为 NULL，此函数将返回 NULL。

如果任意一个参数不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用 JSON_VALID() 验证 JSON 文档的有效性。

JSON_PRETTY() 示例

这里列出了几个常见的 JSON_PRETTY() 用法示例。

格式化输出数组

SELECT JSON_PRETTY('[1, 2, 3]');
+--------------------------+
| JSON_PRETTY('[1, 2, 3]') |
+--------------------------+
| [
  1,
  2,
  3
]        |
+--------------------------+

格式化输出对象

SELECT JSON_PRETTY('{"x": 1, "y": 2}');
+---------------------------------+
| JSON_PRETTY('{"x": 1, "y": 2}') |
+---------------------------------+
| {
  "x": 1,
  "y": 2
}          |
+---------------------------------+

格式化输出复杂对象

SELECT JSON_PRETTY('{"x": 1, "y": [1, 2, 3], "z": {"a": "a", "b": true}}');
+------------------------------------------------------------------------------------------+
| JSON_PRETTY('{"x": 1, "y": [1, 2, 3], "z": {"a": "a", "b": true}}')                      |
+------------------------------------------------------------------------------------------+
| {
  "x": 1,
  "y": [
    1,
    2,
    3
  ],
  "z": {
    "a": "a",
    "b": true
  }
} |
+--------------------------------------------------------------------------------------

JSON_QUOTE() 函数

MySQLJSON_QUOTE()函数使用双引号包装一个值，使其成为一个JSON字符串值。JSON_QUOTE()语法这里是MySQLJSON_QUOTE()的语法：JSON_QUOTE(str)参数str必需的。一个字符串。返回值JSON_QUOTE()函数返回一个使用双引号包围的JSON字符串值

MySQL JSON_QUOTE() 函数使用双引号包装一个值，使其成为一个 JSON 字符串值。

JSON_QUOTE() 语法

这里是 MySQL JSON_QUOTE() 的语法：

JSON_QUOTE(str)

参数

• str

  必需的。一个字符串。

返回值

JSON_QUOTE() 函数返回一个使用双引号包围的 JSON 字符串值。

如果参数为 NULL，JSON_QUOTE() 函数返回 NULL。

下表中的特殊字符将使用反斜杠转义：

转义序列	序列表示的字符
\"	双引号 "
\b	退格字符
\f	换页符
\n	换行符
\r	回车符
\t	制表符
\\	反斜杠 \
\uXXXX	Unicode 值 XXXX 的 UTF-8 字节

JSON_QUOTE() 示例

这里列出了几个常见的 JSON_QUOTE() 示例。

SELECT
    JSON_QUOTE('123'),
    JSON_QUOTE('NULL'),
    JSON_QUOTE('"NULL"');
+-------------------+--------------------+----------------------+
| JSON_QUOTE('123') | JSON_QUOTE('NULL') | JSON_QUOTE('"NULL"') |
+-------------------+--------------------+----------------------+
| "123"             | "NULL"             | "\"NULL\""           |

JSON_REMOVE() 函数

MySQLJSON_REMOVE()函数从一个JSON文档中删除由路径指定的数据并返回修改后的JSON文档。JSON_REMOVE()语法这里是MySQLJSON_REMOVE()的语法：JSON_REMOVE(json,path[,path]…)参数json必需的。一个JSON文档。path必

MySQL JSON_REMOVE() 函数从一个 JSON 文档中删除由路径指定的数据并返回修改后的 JSON 文档。

JSON_REMOVE() 语法

这里是 MySQL JSON_REMOVE() 的语法：

JSON_REMOVE(json, path[, path] ...)

参数

• json

  必需的。一个 JSON 文档。

• path

  必需的。一个有效的路径表达式，它不能包含 * 或 **。

返回值

JSON_REMOVE() 函数从一个 JSON 文档中删除由路径指定的数据并返回修改后的 JSON 文档。

您可以通过参数提供多个路径表达式以供删除。多个路径参数会从左到右依次被执行。当执行下一个参数的时候，JSON 文档可能已经发生了变化。

如果 JSON 中不存在指定的路径，此函数返回原文档。

如果 JSON 文档或者路径为 NULL，此函数将返回 NULL。

JSON_REMOVE() 函数将在以下情况下返回错误：

• 如果参数 json 不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用 JSON_VALID() 验证 JSON 文档的有效性。
• 如果参数 path 不是有效的路径表达式或者等于 $ 或者其中包含 * 或 **, MySQL 将会给出错误。

JSON_REMOVE() 示例

从数组中删除

下面语句使用 JSON_REMOVE() 从一个 JSON 数组中删除索引为 0 和 3 的元素。

SELECT JSON_REMOVE('[0, 1, 2, [3, 4]]', '$[0]', '$[2]');
+--------------------------------------------------+
| JSON_REMOVE('[0, 1, 2, [3, 4]]', '$[0]', '$[2]') |
+--------------------------------------------------+
| [1, 2]                                           |
+--------------------------------------------------+

这里，您可能会感到迷惑，为什么使用 $[2] 来删除索引位置 3 的数据？这是因为当存在多个路径的时候，JSON_REMOVE() 从左到右依次执行，步骤如下：

1. 首先，执行 JSON_REMOVE('[0, 1, 2, [3, 4]]', '$[0]')，返回了 JSON 文档 [1, 2, [3, 4]]。
2. 然后，执行 JSON_REMOVE('[1, 2, [3, 4]]', '$[2]')，返回了 JSON 文档 [1, 2]。

从对象中删除

下面语句使用 JSON_REMOVE() 从一个 JSON 对象中删除一个成员。

SELECT JSON_REMOVE('{"x": 1, "y": 2}', '$.x');
+----------------------------------------+
| JSON_REMOVE('{"x": 1, "y": 2}', '$.x') |
+----------------------------------------+
| {"y": 2}                               |

JSON_REPLACE() 函数

MySQLJSON_REPLACE()函数在一个JSON文档中替换已存在的数据并返回新的JSON文档。JSON_REPLACE()语法这里是MySQLJSON_REPLACE()的语法：JSON_REPLACE(json,path,value[,path2,value2]…)参数json必需的。

MySQL JSON_REPLACE() 函数在一个 JSON 文档中替换已存在的数据并返回新的 JSON 文档。

JSON_REPLACE() 语法

这里是 MySQL JSON_REPLACE() 的语法：

JSON_REPLACE(json, path, value[, path2, value2] ...)

参数

• json

  必需的。被修改的 JSON 文档。

• path

  必需的。一个有效的路径表达式，它不能包含 * 或 **。

• value

  必需的。新的数据。

返回值

JSON_REPLACE() 函数在一个 JSON 文档中替换已存在的数据并返回新的 JSON 文档。您可以提供多对 path-value 参数，以便一次替换多个数据。

JSON_REPLACE() 函数只能替换已经存在的数据。如果 JSON 文档中不存在指定的路径，则不会插入数据。

如果 value 为字符串， JSON_REPLACE() 函数会将其作为字符串写入到 JSON 文档中。

如果 JSON 文档或者路径为 NULL，此函数将返回 NULL。

JSON_REPLACE() 函数将在以下情况下返回错误：

• 如果参数 json 不是有效的 JSON 文档，MySQL 将会给出错误。您可以使用 JSON_VALID() 验证 JSON 文档的有效性。
• 如果参数 path 不是有效的路径表达式或者其中包含 * 或 **, MySQL 将会给出错误。

JSON_REPLACE() 示例

在数组中替换

让我们首先创建一个 JSON 文档以便于演示下面的示例：

SET @array = '[1, [2, 3]]';

让我们将数组的第一个元素和第三个元素替换为新值：

SELECT JSON_REPLACE(@array, '$[0]', 0, '$[2]', 6);

这里，我们想要把数组的将第一个元素替换为 0，将数组的第三个元素替换为 6。让我们看一下结果：

+--------------------------------------------+
| JSON_REPLACE(@array, '$[0]', 0, '$[2]', 6) |
+--------------------------------------------+
| [0, [2, 3]]                                |
+--------------------------------------------+

我们发现，数组的第一个元素成功的被替换为了 0。然后，数组的最后没有插入 6。这是因为 JSON_REPLACE() 函数只替换已经存在的数据。

写入 JSON 类型数据

让我们首先创建一个包含 JSON 对象的 JSON 文档：

SET @obj = '{"x": 1}';

现在让我们将对象中的 x 的成员修改为 true:

SELECT JSON_REPLACE(@obj, '$.x', 'true');
+-----------------------------------+
| JSON_REPLACE(@obj, '$.x', 'true') |
+-----------------------------------+
| {"x": "true"}                     |
+-----------------------------------+

我们发现， true 变成了 "true"。而不是我们希望的 {"x": true}。

这是因为，如果 value 参数为字符串， JSON_REPLACE() 函数会将其作为字符串写入到 JSON 文档中。我们再看几个相似的例子：

SELECT JSON_REPLACE(@obj, '$.x', '[1, 2]');
+-------------------------------------+
| JSON_REPLACE(@obj, '$.x', '[1, 2]') |
+-------------------------------------+
| {"x": "[1, 2]"}                     |
+-------------------------------------+

或者

SELECT JSON_REPLACE(@obj, '$.x', '{"z": 2}');
+---------------------------------------+
| JSON_REPLACE(@obj, '$.x', '{"z": 2}') |
+---------------------------------------+
| {"x": "{\"z\": 2}"}                   |
+---------------------------------------+

为了解决这个问题，我们可以使用 CAST() 函数将数据转为 JSON 类型，比如：

SELECT JSON_REPLACE(@obj, '$.x', CAST('{"z": 2}' AS JSON));
+-----------------------------------------------------+
| JSON_REPLACE(@obj, '$.x', CAST('{"z": 2}' AS JSON)) |
+-----------------------------------------------------+
| {"x": {"z": 2}}                                     |
+-----------------------------------------------------+

JSON_SCHEMA_VALID() 函数

MySQLJSON_SCHEMA_VALID()函数根据指定的JSON模式验证一个JSON文档，并返回1表是验证通过或者返回0表示验证不通过。JSON_SCHEMA_VALID()语法这里是MySQLJSON_SCHEMA_VALID()的语法：JSON_SCHEMA_VALID(schema,js

MySQL JSON_SCHEMA_VALID() 函数根据指定的 JSON 模式验证一个 JSON 文档，并返回 1 表是验证通过或者返回 0 表示验证不通过。

JSON_SCHEMA_VALID() 语法

这里是 MySQL JSON_SCHEMA_VALID() 的语法：

JSON_SCHEMA_VALID(schema, json_doc)

参数

• schema

  必需的。一个 JSON 模式。它必须是一个有效的 JSON 对象。

• json_doc

  必需的。被验证的 JSON 文档。

返回值

JSON_SCHEMA_VALID() 函数返回 1 或 0； 1 表示 JSON 文档通过了验证， 0 表示 JSON 文档没有通过验证。

如果任何一个参数为 NULL，此函数将返回 NULL。

JSON_SCHEMA_VALID() 示例

本示例展示了如何使用 JSON_SCHEMA_VALID() 函数验证一个 JSON 文档是否符合一个 JSON 模式。

首先，让我们创建一个 JSON 模式：

SET @schema = '{
      "id": "http://json-schema.org/geo",
      "$schema": "http://json-schema.org/draft-04/schema#",
      "description": "A geographical coordinate",
      "type": "object",
      "properties": {
        "x": {
          "type": "number",
          "minimum": -90,
          "maximum": 90
        },
        "y": {
          "type": "number",
          "minimum": -180,
          "maximum": 180
        }
      },
      "required": ["x", "y"]
    }';

这里，我们创建了 JSON 模式，其中：

• "type": "object" 表示 JSON 文档必须是一个 JSON 对象。
• "properties" 中定义了对象中的成员列表，以及每个成员的约束。这里的定义了两个成员：
  • x - 数字类型，最大值是 90，最小值是 -90。
  • y - 数字类型，最大值是 180，最小值是 -180。
• "required": ["x", "y"] 定义了对象中必须有成员 x 和 y。

接着，让我们创建一个 JSON 文档：

SET @json_doc = '{"x": 1, "y": 2}';

然后，让我们验证 JSON 文档是否和 JSON 模式匹配：

SELECT JSON_SCHEMA_VALID(@schema, @json_doc);
+---------------------------------------+
| JSON_SCHEMA_VALID(@schema, @json_doc) |
+---------------------------------------+
|                                     1 |
+---------------------------------------+

这说明 @json_doc 是符合 @schema 定义的。

如果我们改一下 JSON 文档，去掉了对象中的成员 y:

SET @json_doc = '{"x": 1}';

让我们再次验证 JSON 文档是否和 JSON 模式匹配：

SELECT JSON_SCHEMA_VALID(@schema, @json_doc);
+---------------------------------------+
| JSON_SCHEMA_VALID(@schema, @json_doc) |
+---------------------------------------+
|                                     0 |
+---------------------------------------+

这里，因为 JSON 模式中定义了 y 是必须的成员，所以 JSON_SCHEMA_VALID() 函数返回了 0.

除此之外，您还可以将 JSON_SCHEMA_VALID() 用于 JSON 字段的 CHECK 约束上。

JSON_SEARCH() 函数

其它函数参考

ANY_VALUE() 函数

MySQLANY_VALUE()函数返回指定列中的任意一个值，它用在包含了GROUPBY的语句中，以抑制ONLY_FULL_GROUP_BY导致的错误。ANY_VALUE()语法这里是MySQLANY_VALUE()函数的语法：ANY_VALUE(column_name)参数column_name必

MySQL ANY_VALUE() 函数返回指定列中的任意一个值，它用在包含了 GROUP BY 的语句中，以抑制 ONLY_FULL_GROUP_BY 导致的错误。

ANY_VALUE() 语法

这里是 MySQL ANY_VALUE() 函数的语法：

ANY_VALUE(column_name)

参数

• column_name

  必需的。 一个列名。

返回值

MySQL ANY_VALUE() 函数返回指定列中的任意一个值。

ANY_VALUE() 示例

为了演示 ANY_VALUE() 函数的用法，让我们使用如下语句创建一个表 test_any_value，它包含了 id, user, 和 address 三个列：

CREATE TABLE test_any_value (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL
);

然后，让我们再插入几条数据：

INSERT INTO test_any_value (name, address)
VALUES
    ('Tom', 'Address 1 of Tom'),
    ('Tom', 'Address 2 of Tom'),
    ('Adam', 'Address of Adam');

让我们看一下表中的数据：

SELECT * FROM test_any_value;
+----+------+------------------+
| id | name | address          |
+----+------+------------------+
|  1 | Tom  | Address 1 of Tom |
|  2 | Tom  | Address 2 of Tom |
|  3 | Adam | Address of Adam  |
+----+------+------------------+

现在，我们想要统计每个人的地址的数量，并显示每个人的任意一个地址：

SELECT name,
    COUNT(address) AS count,
    address
FROM test_any_value
GROUP BY name;

当我们运行这个语句的时候，MySQL 返回了一个错误：”ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column ’testdb.test_any_value.address’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by“。

这里，让我们使用 ANY_VALUE() 解决这个问题：

SELECT name,
    COUNT(address) AS count,
    ANY_VALUE(address) AS address
FROM test_any_value
GROUP BY name;
+------+-------+------------------+
| name | count | address          |
+------+-------+------------------+
| Tom  |     2 | Address 1 of Tom |
| Adam |     1 | Address of Adam  |
+------+-------+------------------+

现在，MySQL 没有给出错误并返回了我们期望的结果。 这与在不使用 ANY_VALUE() 并将 ONLY_FULL_GROUP_BY 模式禁用掉的结果完全一样。

BIN_TO_UUID() 函数

MySQLBIN_TO_UUID()函数将一个指定的二进制的UUID转为一个字符串UUID并返回结果。BIN_TO_UUID()函数是UUID_TO_BIN()函数的逆行操作。BIN_TO_UUID()语法这里是MySQLBIN_TO_UUID()函数的语法：BIN_TO_UUID(binary_u

MySQL BIN_TO_UUID() 函数将一个指定的二进制的 UUID 转为一个字符串 UUID 并返回结果。

BIN_TO_UUID() 函数是 UUID_TO_BIN() 函数的逆行操作。

BIN_TO_UUID() 语法

这里是 MySQL BIN_TO_UUID() 函数的语法：

BIN_TO_UUID(binary_uuid, swap_flag)

参数

• binary_uuid

  必需的。 一个二进制的 UUID。

• swap_flag

  可选的。 交换标识，可用只为 0 和 1。默认值是 0。

返回值

MySQL BIN_TO_UUID() 函数将一个指定的二进制的 UUID 转为一个字符串 UUID 并返回结果。

如果参数 swap_flag 是 1，BIN_TO_UUID() 函数将交换 UUID 中的时间低位部分和时间高位部分。

如果参数 binary_uuid 为 NULL，该函数将返回 NULL。

如果任意一个参数不合法，将会产生一个错误。

BIN_TO_UUID() 示例

本示例展示了 BIN_TO_UUID() 函数的基本用法。

首先，让我们创建一个二进制 UUID，如下：

set @binary_uuid = UUID_TO_BIN('b45f7406-cf63-11ec-aeab-0242ac110003');

然后，让我们将上面创建的二进制 UUID 转为字符串 UUID：

SELECT BIN_TO_UUID(@binary_uuid);
+--------------------------------------+
| BIN_TO_UUID(@binary_uuid)            |
+--------------------------------------+
| b45f7406-cf63-11ec-aeab-0242ac110003 |
+--------------------------------------+

让我们使用使用 swap_flag = 1 参数将 UUID 中时间低位部分和时间高位部分交换：

SELECT BIN_TO_UUID(@binary_uuid, 1);
+--------------------------------------+
| BIN_TO_UUID(@binary_uuid, 1)         |
+--------------------------------------+
| cf6311ec-7406-b45f-aeab-0242ac110003 |
+--------------------------------------+

这里， cf6311ec 和 b45f 交换了位置。

BINARY 操作符

在MySQL中，BINARY操作符将一个字符串转为二进制字符串。二进制字符串使用binary字符集和binary排序规则。BINARY操作符常用在对字符串进行逐字节比较而不是逐字符比较。BINARY语法这里是MySQLBINARY操作符的语法：BINARYexprBINARYexpr等同于CONVE

在 MySQL 中， BINARY 操作符将一个字符串转为二进制字符串。二进制字符串使用 binary 字符集和 binary 排序规则。 BINARY 操作符常用在对字符串进行逐字节比较而不是逐字符比较。

BINARY 语法

这里是 MySQL BINARY 操作符的语法：

BINARY expr

BINARY expr 等同于 CONVERT(expr, BINARY) 和 CAST(expr AS BINARY) 函数。

参数

• expr

  必需的。 一个需要转为二进制的值或者表达式。

返回值

MySQL BINARY 返回由参数转为的二进制字符串。

如果您没有为 BINARY 提供参数，MySQL 将返回一个错误。

BINARY 示例

基本用法

本示例展示了如何使用 BINARY 操作符将 'Hello' 转为二进制字符串

SELECT BINARY 'Hello';
+--------------------------------+
| BINARY 'Hello'                 |
+--------------------------------+
| 0x48656C6C6F                   |
+--------------------------------+

注意， 在 mysql client 中，二进制字符串默认以十六进制的形式打印出来。

比较字符串

BINARY 操作符经常用在字符串比较中。

SELECT 'hello' = 'HELLO';
+-------------------+
| 'hello' = 'HELLO' |
+-------------------+
|                 1 |
+-------------------+

这里，由于使用的字符集和排序规则，比较字符串的时候是不区分大小写的，因此 'hello' = 'HELLO' 返回了 1。 如果我们想要按照区分大小写比较字符串，可以使用 BINARY 将字符串转为二进制后按字节进行比较。如下：

SELECT BINARY 'hello' = 'HELLO';
+--------------------------+
| BINARY 'hello' = 'HELLO' |
+--------------------------+
|                        0 |
+--------------------------+

结果是显而易见的。

BINARY 操作符也会导致字符串结尾的空格在比较重变得重要。请看下面的例子：

SELECT 'a' = 'a ', BINARY 'a' = 'a ';
+------------+-------------------+
| 'a' = 'a ' | BINARY 'a' = 'a ' |
+------------+-------------------+
|          1 |                 0 |
+------------+-------------------+

这里，

‘a’ = 'a '返回了1，它说明在比较中 'a '` 结尾的空格被忽略了。

BINARY ‘a’ = 'a '返回了0，它说明在比较中 'a '` 结尾的空格没有被忽略。

CAST() 函数

MySQLCAST()函数将任意类型的参数值转为指定的类型的值并返回。CAST()语法这里是MySQLCAST()函数的语法：CAST(exprASdata_type)CAST(exprASdata_type)等同于CONVERT(expr,type)。参数expr必需的。一个需要转换数据类型的值。

MySQL CAST() 函数将任意类型的参数值转为指定的类型的值并返回。

CAST() 语法

这里是 MySQL CAST() 函数的语法：

CAST(expr AS data_type)

CAST(expr AS data_type) 等同于 CONVERT(expr, type)。

参数

• expr

  必需的。 一个需要转换数据类型的值。

• data_type

  必需的。 目标数据类型。您可以使用下面的类型：

  • BINARY[(N)]: 如果参数为空（0 长度），结果是 BINARY(0)，否则结果是 VARBINARY 类型的字符串。
  • CHAR[(N)]: 结果是 VARCHAR 类型的字符串。除非参数为空，结果是 CHAR(0)。
  • DATE: 结果是 DATE 类型的。
  • DATETIME[(M)]: 结果是 DATETIME 类型的，M 是小数秒的位数。
  • DECIMAL[(M[,D])]: 结果是 DECIMAL 类型的。
  • DOUBLE: 结果是 DOUBLE 类型的。它在 MySQL 8.0.17 添加。
  • FLOAT[(p)]: 按如下规则转换数据类型
    • 如果没有指定 p，结果是 FLOAT 类型的。
    • 如果 0 <= p <= 24，结果是 FLOAT 类型的。
    • 如果 25 <= p <= 53，结果是 DOUBLE 类型的。
    • 如果 p < 0 或 p > 53，返回一个错误。
  • JSON: 结果是 JSON 类型的。
  • NCHAR[(N)]: 结果是 NCHAR 类型的。
  • REAL: 结果是 REAL 类型的。实际上，如果启用了 REAL_AS_FLOAT 它是 FLOAT，否则是 DOUBLE。
  • SIGNED [INTEGER]: 结果是一个有符号的 BIGINT 类型。
  • TIME[(M)]: 结果是 TIME 类型的，M 是小数秒的位数。
  • UNSIGNED [INTEGER]: 结果是一个无符号的 BIGINT 类型。
  • YEAR: 结果是 YEAR 类型的，它在 MySQL 8.0.22 添加。

返回值

MySQL CAST() 函数将任意数据类型的值转为指定的数据类型并返回。

CAST() 示例

这里有几个 CAST() 的常用示例

转为二进制

您可以是用 CAST() 函数将一个字符串转为二进制字符串。

SELECT CAST('hello' AS BINARY);
+--------------------------------------------------+
| CAST('hello' AS BINARY)                          |
+--------------------------------------------------+
| 0x68656C6C6F                                     |
+--------------------------------------------------+

注意， 在 mysql client 中，二进制字符串默认以十六进制的形式打印出来。

您也可以使用 BINARY 操作符获取一个字符串的二进制字符串。如下：

SELECT BINARY 'hello';
+--------------------------------+
| BINARY 'hello'                 |
+--------------------------------+
| 0x68656C6C6F                   |
+--------------------------------+

转为 JSON

您可以使用 CAST() 函数将使用字符串表示的 JSON 文档转为 JSON 类型的数据。

SELECT CAST('[1, 2, "a"]' AS JSON);
+-----------------------------+
| CAST('[1, 2, "a"]' AS JSON) |
+-----------------------------+
| [1, 2, "a"]                 |
+-----------------------------+

转为日期时间

您可以使用 CAST() 函数将使用字符串表示的日期/时间值转为 DATE, DATETIME 类型的数据。

SELECT
    CAST('2022-02-28' AS DATE),
    CAST('10:10:10' AS TIME),
    CAST('2022-02-28 10:10:10' AS DATETIME);
+----------------------------+--------------------------+-----------------------------------------+
| CAST('2022-02-28' AS DATE) | CAST('10:10:10' AS TIME) | CAST('2022-02-28 10:10:10' AS DATETIME) |
+----------------------------+--------------------------+-----------------------------------------+
| 2022-02-28                 | 10:10:10                 | 2022-02-28 10:10:10   

COALESCE() 函数

MySQLCOALESCE()函数返回参数列表中第一个不是NULL的值。COALESCE()语法这里是MySQLCOALESCE()函数的语法：COALESCE(value1[,value2…])参数value1[,value2…]必需的。参数列表。您至少应该提供一个参数。返回值MySQLC

MySQL COALESCE() 函数返回参数列表中第一个不是 NULL 的值。

COALESCE() 语法

这里是 MySQL COALESCE() 函数的语法：

COALESCE(value1[, value2 ...])

参数

• value1[, value2 …]

  必需的。 参数列表。您至少应该提供一个参数。

返回值

MySQL COALESCE() 函数返回参数列表中第一个不是 NULL 的值。如果全部参数都是 NULL，该函数将返回 NULL。

如果您没有为 COALESCE() 提供参数，MySQL 将返回一个错误。

COALESCE() 示例

SELECT COALESCE(NULL, 100), COALESCE(NULL, NULL);
+---------------------+----------------------+
| COALESCE(NULL, 100) | COALESCE(NULL, NULL) |
+---------------------+----------------------+
|                 100 |                 NULL |
+---------------------+----------------------+

这里，

COALESCE(NULL, 100) 的参数中 100 是第一个非 NULL 的值，因此返回了 100。

COALESCE(NULL, NULL) 的全部参数都是 NULL，因此返回了 NULL。

CONVERT() 函数

MySQLCONVERT()函数将任意类型的参数值转为指定的类型或者字符集。CONVERT()语法这里是MySQLCONVERT()函数的语法：CONVERT(expr,data_type)或CONVERT(exprUSINGcharset)CONVERT(expr,data_type)等同于CAS

MySQL CONVERT() 函数将任意类型的参数值转为指定的类型或者字符集。

CONVERT() 语法

这里是 MySQL CONVERT() 函数的语法：

CONVERT(expr, data_type)

或

CONVERT(expr USING charset)

CONVERT(expr, data_type) 等同于 CAST(expr AS data_type)。

参数

• expr

  必需的。 一个需要转换数据类型的值。

• data_type

  必需的。 目标数据类型。您可以使用下面的类型：

  • BINARY[(N)]: 如果参数为空（0 长度），结果是 BINARY(0)，否则结果是 VARBINARY 类型的字符串。
  • CHAR[(N)]: 结果是 VARCHAR 类型的字符串。除非参数为空，结果是 CHAR(0)。
  • DATE: 结果是 DATE 类型的。
  • DATETIME[(M)]: 结果是 DATETIME 类型的，M 是小数秒的位数。
  • DECIMAL[(M[,D])]: 结果是 DECIMAL 类型的。
  • DOUBLE: 结果是 DOUBLE 类型的。它在 MySQL 8.0.17 添加。
  • FLOAT[(p)]: 按如下规则转换数据类型
    • 如果没有指定 p，结果是 FLOAT 类型的。
    • 如果 0 <= p <= 24，结果是 FLOAT 类型的。
    • 如果 25 <= p <= 53，结果是 DOUBLE 类型的。
    • 如果 p < 0 或 p > 53，返回一个错误。
  • JSON: 结果是 JSON 类型的。
  • NCHAR[(N)]: 结果是 NCHAR 类型的。
  • REAL: 结果是 REAL 类型的。实际上，如果启用了 REAL_AS_FLOAT 它是 FLOAT，否则是 DOUBLE。
  • SIGNED [INTEGER]: 结果是一个有符号的 BIGINT 类型。
  • TIME[(M)]: 结果是 TIME 类型的，M 是小数秒的位数。
  • UNSIGNED [INTEGER]: 结果是一个无符号的 BIGINT 类型。
  • YEAR: 结果是 YEAR 类型的，它在 MySQL 8.0.22 添加。

• charset

  必需的。 要转换为的字符集。

返回值

MySQL CONVERT() 函数将任意类型的参数值转为指定的类型或者字符集并返回。

CONVERT() 示例

这里有几个 CONVERT() 的常用示例。

转为二进制

您可以是用 CONVERT() 函数将一个字符串转为二进制字符串。

SELECT CONVERT('hello', BINARY);
+----------------------------------------------------+
| CONVERT('hello', BINARY)                           |
+----------------------------------------------------+
| 0x68656C6C6F                                       |
+----------------------------------------------------+

注意， 在 mysql client 中，二进制字符串默认以十六进制的形式打印出来。

您也可以使用 BINARY 操作符获取一个字符串的二进制字符串。如下：

SELECT BINARY 'hello';
+--------------------------------+
| BINARY 'hello'                 |
+--------------------------------+
| 0x68656C6C6F                   |
+--------------------------------+

转为 JSON

您可以使用 CONVERT() 函数将使用字符串表示的 JSON 文档转为 JSON 类型的数据。

SELECT CONVERT('[1, 2, "a"]', JSON);
+------------------------------+
| CONVERT('[1, 2, "a"]', JSON) |
+------------------------------+
| [1, 2, "a"]                  |
+------------------------------+

转为日期时间

您可以使用 CONVERT() 函数将使用字符串表示的日期/时间值转为 DATE, DATETIME 类型的数据。

SELECT
    CONVERT('2022-02-28', DATE),
    CONVERT('10:10:10', TIME),
    CONVERT('2022-02-28 10:10:10', DATETIME);
+-----------------------------+---------------------------+------------------------------------------+
| CONVERT('2022-02-28', DATE) | CONVERT('10:10:10', TIME) | CONVERT('2022-02-28 10:10:10', DATETIME) |
+-----------------------------+---------------------------+------------------------------------------+
| 2022-02-28                  | 10:10:10                  | 2022-02-28 10:10:10                      |
+-----------------------------+---------------------------+------------------------------------------+

转换字符集

本示例将 Hello 的字符集改为 latin1 字符集：

SELECT CONVERT("Hello" USING latin1);
+-------------------------------+
| CONVERT("Hello" USING latin1) |
+-------------------------------+
| Hello                         |
+-------------------------------+

如果采用的字符集不能表示相应的字符串，则会出现乱码。比如：

SELECT
    CONVERT("好" USING latin1),
    CONVERT("好" USING utf8mb4);
+-----------------------------+------------------------------+
| CONVERT("好" USING latin1)  | CONVERT("好" USING utf8mb4)  |
+-----------------------------+------------------------------+
| ?                           | 好                           |
+-----------------------------+------------------------------+

DEFAULT() 函数

MySQLDEFAULT()函数返回指定的列的默认值。MySQLDEFAULT()函数只能返回那些将列的默认值定义为字面量值的列的默认值，不能返回默认值为表达式的列的默认值。DEFAULT()语法这里是MySQLDEFAULT()函数的语法：DEFAULT(column_name)参数column_

MySQL DEFAULT() 函数返回指定的列的默认值。

MySQL DEFAULT() 函数只能返回那些将列的默认值定义为字面量值的列的默认值，不能返回默认值为表达式的列的默认值。

DEFAULT() 语法

这里是 MySQL DEFAULT() 函数的语法：

DEFAULT(column_name)

参数

• column_name

  必需的。 一个列名。

返回值

MySQL DEFAULT() 函数返回指定的列的默认值。如果指定的列没有定义默认值，将会放生错误。

DEFAULT() 示例

为了演示 DEFAULT() 函数的用法，让我们使用如下语句创建一个表 test_default：

CREATE TABLE test_default (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    default_int INT NOT NULL DEFAULT 0,
    default_date DATE NOT NULL DEFAULT (CURRENT_DATE)
);

这里，我们创建了一个表，它包含 4 个列，其中下面的 2 个列具有默认值：

• default_int 的默认值为 0。
• default_date 的默认值由表达式 (CURRENT_DATE) 产生。

然后，让我们再插入几条数据：

INSERT INTO test_default (name, default_int)
VALUES ('Tom', 1);

让我们看一下表中的数据：

SELECT * FROM test_default;
+----+------+-------------+--------------+
| id | name | default_int | default_date |
+----+------+-------------+--------------+
|  1 | Tom  |           1 | 2022-05-09   |
+----+------+-------------+--------------+

现在，让我们使用 DEFAULT() 函数看一下 default_int 列的默认值：

SELECT DEFAULT(default_int) FROM test_default;
+----------------------+
| DEFAULT(default_int) |
+----------------------+
|                    0 |
+----------------------+

让我们在使用 DEFAULT() 函数看一下 default_date 列的默认值：

SELECT DEFAULT(default_date) FROM test_default;

INET_ATON() 函数

MySQLINET_ATON()函数将指定的形式为a.b.c.d的IP地址转为一个网络字节序的数字IP。INET_NTOA()函数是INET_ATON()函数的逆向操作。INET_ATON()语法这里是MySQLINET_ATON()函数的语法：INET_ATON(ip)参数ip必需的。一个形式为a

MySQL INET_ATON() 函数将指定的形式为 a.b.c.d 的 IP 地址转为一个网络字节序的数字 IP。

INET_NTOA() 函数是 INET_ATON() 函数的逆向操作。

INET_ATON() 语法

这里是 MySQL INET_ATON() 函数的语法：

INET_ATON(ip)

参数

• ip

  必需的。 一个形式为 a.b.c.d 的 IPv4 地址。

返回值

MySQL INET_ATON() 函数将指定的形式为 a.b.c.d 的 IP 地址转为一个网络字节序的数字 IP。

MySQL INET_ATON() 函数的算法为：

  a × 256 × 256 × 256
+ b × 256 × 256
+ c × 256
+ d

如果参数为 NULL，该函数将返回 NULL。

INET_ATON() 示例

这里有几个 INET_ATON() 的常用示例。

SELECT INET_ATON('192.168.1.100');
+----------------------------+
| INET_ATON('192.168.1.100') |
+----------------------------+
|                 3232235876 |
+----------------------------+

ISNULL() 函数

MySQLISNULL()函数检查指定的参数是否为NULL。ISNULL()语法这里是MySQLISNULL()函数的语法：ISNULL(expr)参数expr必需的。一个被测试的表达式。返回值MySQLISNULL()函数检查指定的参数是否为NULL。如果参数为NULL，该函数返回1，否则返回0。

MySQL ISNULL() 函数检查指定的参数是否为 NULL。

ISNULL() 语法

这里是 MySQL ISNULL() 函数的语法：

ISNULL(expr)

参数

• expr

  必需的。 一个被测试的表达式。

返回值

MySQL ISNULL() 函数检查指定的参数是否为 NULL。如果参数为 NULL，该函数返回 1，否则返回 0。

如果您没有为 ISNULL() 提供参数，MySQL 将返回一个错误。

ISNULL() 示例

这里有几个 ISNULL() 的常用示例。

SELECT 
    ISNULL(NULL), 
    ISNULL(""), 
    ISNULL("A"), 
    ISNULL(0), 
    ISNULL(1);
+--------------+------------+-------------+-----------+-----------+
| ISNULL(NULL) | ISNULL("") | ISNULL("A") | ISNULL(0) | ISNULL(1) |
+--------------+------------+-------------+-----------+-----------+
|            1 |          0 |           0 |         0 |         0 |
+--------------+------------+-------------+-----------+-----------+

这里，只有 ISNULL(NULL) 返回了 1，其他都返回了 0。

NAME_CONST() 函数

MySQLNAME_CONST()函数仅供MySQL内部使用，它返回给定的常量值并带有指定的列名。NAME_CONST()语法这里是MySQLNAME_CONST()函数的语法：NAME_CONST(column_name,const_value)参数column_name必需的。一个可作为列名的字

MySQL NAME_CONST() 函数仅供 MySQL 内部使用，它返回给定的常量值并带有指定的列名。

NAME_CONST() 语法

这里是 MySQL NAME_CONST() 函数的语法：

NAME_CONST(column_name, const_value)

参数

• column_name

  必需的。 一个可作为列名的字符串。

• const_value

  必需的。 一个常量值。

返回值

MySQL NAME_CONST() 函数返回给定的常量值 const_value。如果用来产生一个结果集列，那么列名为 column_name。

NAME_CONST() 示例

这里展示了 MySQL NAME_CONST() 函数的基本用法。

SELECT NAME_CONST('My Name', 7);
+---------+
| My Name |
+---------+
|       7 |
+---------+

这个语句等同于：

SELECT 7 AS 'My Name';
+---------+
| My Name |
+---------+
|       7 |
+---------+

SLEEP() 函数

MySQLSLEEP()函数将当前查询暂停（睡眠）指定的秒数。SLEEP()语法这里是MySQLSLEEP()函数的语法：SLEEP(duration)参数duration必需的。以秒为单位的睡眠时长。它应该大于或等于0，并且可以带有小数部分。返回值MySQLSLEEP()函数将查询暂停（睡眠）指定

MySQL SLEEP() 函数将当前查询暂停（睡眠）指定的秒数。

SLEEP() 语法

这里是 MySQL SLEEP() 函数的语法：

SLEEP(duration)

参数

• duration

  必需的。 以秒为单位的睡眠时长。它应该大于或等于 0，并且可以带有小数部分。

返回值

MySQL SLEEP() 函数将查询暂停（睡眠）指定的秒数，然后返回 0。如果暂停被中断，则返回 1。

如果参数 duration 为负数或者 NULL，SLEEP() 函数会产生一个警告，或者在严格模式中产生一个错误。

如果 SLEEP() 是一个查询中唯一调用的东西，它被中断后返回 1。

如果 SLEEP() 只是一个查询中的一部分，它被中断后返回一个错误。

SLEEP() 示例

这里结合 SYSDATE() 函数展示了 SLEEP() 的功能。

SELECT
    SYSDATE(),
    SLEEP(10),
    SYSDATE();
+---------------------+-----------+---------------------+
| SYSDATE()           | SLEEP(10) | SYSDATE()           |
+---------------------+-----------+---------------------+
| 2022-05-07 22:03:22 |         0 | 2022-05-07 22:03:32 |
+---------------------+-----------+---------------------+
1 row in set (10.07 sec)

这里，我们看到如下现象：

SLEEP(10)返回了0`。

第二个 SYSDATE() 比第一个 SYSDATE() 函数的返回值晚了 10 秒。这是因为中间的 SLEEP(10) 让查询暂停了 10 秒。

1 row in set (10.07 sec) 告诉我们整个查询共花费了 10.7 秒。

UUID() 函数

MySQLUUID()函数根据RFC4122生成一个通用唯一标识符（UUID）并返回。UUID是一个全局唯一的值。任何两次对UUID()函数的调用都应该生成不同的值。UUID()语法这里是MySQLUUID()函数的语法：UUID()参数MySQLUUID()函数没有任何参数。返回值MySQLUUI

MySQL UUID() 函数根据 RFC 4122 生成一个通用唯一标识符（UUID）并返回。

UUID 是一个全局唯一的值。任何两次对 UUID() 函数的调用都应该生成不同的值。

UUID() 语法

这里是 MySQL UUID() 函数的语法：

UUID()

参数

MySQL UUID() 函数没有任何参数。

返回值

MySQL UUID() 函数返回一个根据 RFC 4122 生成的通用唯一标识符（UUID）。

UUID() 返回的值符合 RFC 4122 中描述的 UUID 版本 1。该值是一个 128 位数字，表示为一个由五个十六进制数字组成的 utf8 字符串， 格式为aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee：

• 前三个数字是从时间戳的低、中和高三部分生成的。高位部分还包括 UUID 版本号。

• 第四个数字保留时间唯一性，以防时间戳值失去单调性（例如，由于夏令时）。

• 第五个数字是 IEEE 802 节点号，它提供空间唯一性。如果后者不可用（例如，因为主机设备没有以太网卡，或者不知道如何在主机操作系统上找到接口的硬件地址），则用随机数代替。在这种情况下，无法保证空间唯一性。然而，碰撞的概率应该 非常低。

  接口的 MAC 地址仅在 FreeBSD、Linux 和 Windows 上被使用。在其他操作系统上，MySQL 使用随机生成的 48 位数字。

请使用 UUID_TO_BIN() 和 BIN_TO_UUID() 函数以在 UUID 的字符串值和二进制值之间进行转换。

请使用 IS_UUID 函数检查一个字符串是否为有效的 UUID 值。

UUID() 示例

这里展示了 MySQL UUID() 函数的基本用法。

SELECT UUID();
+--------------------------------------+
| UUID()                               |
+--------------------------------------+
| d114115a-ce6a-11ec-8607-63ec778e6346 |
+--------------------------------------+