MySQL8-中文参考-五十三-

布客飞龙
63 阅读1小时+

MySQL8 中文参考（五十三）

原文：docs.oracle.com/javase/tutorial/reallybigindex.html

14.17.7 JSON 模式验证函数

原文：dev.mysql.com/doc/refman/8.0/en/json-validation-functions.html

从 MySQL 8.0.17 开始，MySQL 支持根据JSON Schema 规范的 Draft 4对 JSON 文档进行验证。可以使用本节详细介绍的两个函数之一来实现，这两个函数都接受两个参数，一个是 JSON 模式，另一个是根据模式进行验证的 JSON 文档。JSON_SCHEMA_VALID() 如果文档符合模式，则返回 true，否则返回 false；JSON_SCHEMA_VALIDATION_REPORT() 以 JSON 格式提供验证报告。

两个函数处理空值或无效输入如下：

  • 如果至少一个参数为 NULL，则函数返回 NULL。

  • 如果至少一个参数不是有效的 JSON，则函数会引发错误（ER_INVALID_TYPE_FOR_JSON）

  • 如果模式不是有效的 JSON 对象，则该函数返回ER_INVALID_JSON_TYPE。

MySQL 支持 JSON 模式中的 required 属性，以强制包含必需属性（请参阅函数描述中的示例）。

MySQL 支持 JSON 模式中的 id、$schema、description 和 type 属性，但不要求其中任何一个。

MySQL 不支持 JSON 模式中的外部资源；使用 $ref 关键字会导致 JSON_SCHEMA_VALID() 失败，并显示ER_NOT_SUPPORTED_YET。

注意

MySQL 支持 JSON 模式中的正则表达式模式，支持但会静默忽略无效模式（请参阅 JSON_SCHEMA_VALID() 的描述以获取示例）。

这些函数在以下列表中有详细描述：

  • JSON_SCHEMA_VALID(*schema*,*document*)

    验证 JSON 文档 是否符合 JSON 模式。两者都是必需的。模式必须是有效的 JSON 对象；文档必须是有效的 JSON 文档。只要满足这些条件：如果文档符合模式，则函数返回 true（1）；否则返回 false（0）。

    在此示例中，我们将用户变量 @schema 设置为地理坐标的 JSON 模式的值，另一个变量 @document 设置为包含一个这样的坐标的 JSON 文档的值。然后，我们通过将它们用作 JSON_SCHEMA_VALID() 的参数来验证 @document 是否符合 @schema：

    mysql> SET @schema = '{
    '>  "id": "http://json-schema.org/geo",
    '> "$schema": "http://json-schema.org/draft-04/schema#",
    '> "description": "A geographical coordinate",
    '> "type": "object",
    '> "properties": {
    '>   "latitude": {
    '>     "type": "number",
    '>     "minimum": -90,
    '>     "maximum": 90
    '>   },
    '>   "longitude": {
    '>     "type": "number",
    '>     "minimum": -180,
    '>     "maximum": 180
    '>   }
    '> },
    '> "required": ["latitude", "longitude"]
    '>}';
Query OK, 0 rows affected (0.01 sec)

mysql> SET @document = '{
    '> "latitude": 63.444697,
    '> "longitude": 10.445118
    '>}';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT JSON_SCHEMA_VALID(@schema, @document);
+---------------------------------------+
| JSON_SCHEMA_VALID(@schema, @document) |
+---------------------------------------+
|                                     1 |
+---------------------------------------+
1 row in set (0.00 sec)

    由于@schema包含required属性，我们可以将@document设置为一个在其他方面有效但不包含所需属性的值，然后对其进行与@schema的测试，如下所示：

    mysql> SET @document = '{}';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT JSON_SCHEMA_VALID(@schema, @document);
+---------------------------------------+
| JSON_SCHEMA_VALID(@schema, @document) |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (0.00 sec)

    如果现在将@schema的值设置为相同的 JSON 模式，但不包含required属性，@document会验证通过，因为它是有效的 JSON 对象，即使不包含任何属性，如下所示：

    mysql> SET @schema = '{
    '> "id": "http://json-schema.org/geo",
    '> "$schema": "http://json-schema.org/draft-04/schema#",
    '> "description": "A geographical coordinate",
    '> "type": "object",
    '> "properties": {
    '>   "latitude": {
    '>     "type": "number",
    '>     "minimum": -90,
    '>     "maximum": 90
    '>   },
    '>   "longitude": {
    '>     "type": "number",
    '>     "minimum": -180,
    '>     "maximum": 180
    '>   }
    '> }
    '>}';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT JSON_SCHEMA_VALID(@schema, @document);
+---------------------------------------+
| JSON_SCHEMA_VALID(@schema, @document) |
+---------------------------------------+
|                                     1 |
+---------------------------------------+
1 row in set (0.00 sec)

    JSON_SCHEMA_VALID() 和 CHECK 约束。 JSON_SCHEMA_VALID() 也可用于强制执行CHECK约束。

    考虑在此处创建的表geo，其中包含表示地图上纬度和经度点的 JSON 列coordinate，由作为JSON_SCHEMA_VALID()调用参数使用的 JSON 模式控制，该模式作为此表上的CHECK约束的表达式传递：

    mysql> CREATE TABLE geo (
 ->     coordinate JSON,
 ->     CHECK(
 ->         JSON_SCHEMA_VALID(
 ->             '{
    '>                 "type":"object",
    '>                 "properties":{
    '>                       "latitude":{"type":"number", "minimum":-90, "maximum":90},
    '>                       "longitude":{"type":"number", "minimum":-180, "maximum":180}
    '>                 },
    '>                 "required": ["latitude", "longitude"]
    '>             }',
 ->             coordinate
 ->         )
 ->     )
 -> );
Query OK, 0 rows affected (0.45 sec)

    注意

    因为 MySQL 的CHECK约束不能包含对变量的引用，所以在为表指定此类约束时，必须在使用JSON_SCHEMA_VALID()时内联传递 JSON 模式。

    我们将表示坐标的 JSON 值分配给三个变量，如下所示：

    mysql> SET @point1 = '{"latitude":59, "longitude":18}';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @point2 = '{"latitude":91, "longitude":0}';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @point3 = '{"longitude":120}';
Query OK, 0 rows affected (0.00 sec)

    这些值中的第一个是有效的，如下面的INSERT语句中所示：

    mysql> INSERT INTO geo VALUES(@point1);
Query OK, 1 row affected (0.05 sec)

    第二个 JSON 值是无效的，因此未通过约束，如下所示：

    mysql> INSERT INTO geo VALUES(@point2);
ERROR 3819 (HY000): Check constraint 'geo_chk_1' is violated.

    在 MySQL 8.0.19 及更高版本中，您可以通过发出SHOW WARNINGS语句来获取有关失败性质的精确信息，例如，latitude值超过模式中定义的最大值：

    mysql> SHOW WARNINGS\G
*************************** 1\. row ***************************
  Level: Error
   Code: 3934
Message: The JSON document location '#/latitude' failed requirement 'maximum' at
JSON Schema location '#/properties/latitude'.
*************************** 2\. row ***************************
  Level: Error
   Code: 3819
Message: Check constraint 'geo_chk_1' is violated. 2 rows in set (0.00 sec)

    上述定义的第三个坐标值也是无效的，因为缺少必需的latitude属性。与之前一样，您可以通过尝试将该值插入geo表中，然后在之后发出SHOW WARNINGS来看到这一点：

    mysql> INSERT INTO geo VALUES(@point3);
ERROR 3819 (HY000): Check constraint 'geo_chk_1' is violated.
mysql> SHOW WARNINGS\G
*************************** 1\. row ***************************
  Level: Error
   Code: 3934
Message: The JSON document location '#' failed requirement 'required' at JSON
Schema location '#'.
*************************** 2\. row ***************************
  Level: Error
   Code: 3819
Message: Check constraint 'geo_chk_1' is violated. 2 rows in set (0.00 sec)

    有关更多信息，请参见 Section 15.1.20.6, “CHECK Constraints”。

    JSON Schema 支持为字符串指定正则表达式模式，但 MySQL 使用的实现会默默忽略无效模式。这意味着即使正则表达式模式无效，JSON_SCHEMA_VALID()也可能返回 true，如下所示：

    mysql> SELECT JSON_SCHEMA_VALID('{"type":"string","pattern":"("}', '"abc"');
+---------------------------------------------------------------+
| JSON_SCHEMA_VALID('{"type":"string","pattern":"("}', '"abc"') |
+---------------------------------------------------------------+
|                                                             1 |
+---------------------------------------------------------------+
1 row in set (0.04 sec)

  • JSON_SCHEMA_VALIDATION_REPORT(*schema*,*document*)

    针对 JSON document 对 JSON schema 进行验证。两者都是必需的。与 JSON_VALID_SCHEMA() 一样，模式必须是有效的 JSON 对象，文档必须是有效的 JSON 文档。只要满足这些条件，函数就会返回一个报告，作为 JSON 文档，关于验证结果的情况。如果根据 JSON Schema 认为 JSON 文档有效，函数将返回一个具有一个属性valid且值为"true"的 JSON 对象。如果 JSON 文档未通过验证，函数将返回一个包含以下属性的 JSON 对象：

    • valid：对于失败的模式验证始终为"false"

    • reason：包含失败原因的人类可读字符串

    • schema-location：指示验证失败的 JSON 模式中的位置的 JSON 指针 URI 片段标识符（请参见此列表后面的注释）

    • document-location：指示验证失败的 JSON 文档中的位置的 JSON 指针 URI 片段标识符（请参见此列表后面的注释）

    • schema-failed-keyword：包含在违反 JSON 模式的关键字或属性的字符串

    注

    JSON 指针 URI 片段标识符在RFC 6901 - JavaScript Object Notation (JSON) Pointer中定义。（这些与JSON_EXTRACT()和其他 MySQL JSON 函数使用的 JSON 路径表示法不同。）在这种表示法中，#代表整个文档，#/myprop代表包含在名为myprop的顶级属性中的文档部分。有关更多信息，请参阅刚才引用的规范以及本节后面显示的示例。

    在此示例中，我们将一个用户变量@schema设置为地理坐标的 JSON 模式的值，另一个变量@document设置为包含一个这样的坐标的 JSON 文档的值。然后，通过将它们用作JSON_SCHEMA_VALIDATION_REORT()的参数来验证@document是否符合@schema：

    mysql> SET @schema = '{
    '>  "id": "http://json-schema.org/geo",
    '> "$schema": "http://json-schema.org/draft-04/schema#",
    '> "description": "A geographical coordinate",
    '> "type": "object",
    '> "properties": {
    '>   "latitude": {
    '>     "type": "number",
    '>     "minimum": -90,
    '>     "maximum": 90
    '>   },
    '>   "longitude": {
    '>     "type": "number",
    '>     "minimum": -180,
    '>     "maximum": 180
    '>   }
    '> },
    '> "required": ["latitude", "longitude"]
    '>}';
Query OK, 0 rows affected (0.01 sec)

mysql> SET @document = '{
    '> "latitude": 63.444697,
    '> "longitude": 10.445118
    '>}';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT JSON_SCHEMA_VALIDATION_REPORT(@schema, @document);
+---------------------------------------------------+
| JSON_SCHEMA_VALIDATION_REPORT(@schema, @document) |
+---------------------------------------------------+
| {"valid": true}                                   |
+---------------------------------------------------+
1 row in set (0.00 sec)

    现在我们设置@document，使其指定其中一个属性的非法值，如下所示：

    mysql> SET @document = '{
    '> "latitude": 63.444697,
    '> "longitude": 310.445118
    '> }';

    现在，当使用JSON_SCHEMA_VALIDATION_REPORT()测试@document时，验证失败。函数调用的输出包含有关失败的详细信息（使用JSON_PRETTY()包装函数以提供更好的格式化），如下所示：

    mysql> SELECT JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document))\G
*************************** 1\. row ***************************
JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document)): {
  "valid": false,
  "reason": "The JSON document location '#/longitude' failed requirement 'maximum' at JSON Schema location '#/properties/longitude'",
  "schema-location": "#/properties/longitude",
  "document-location": "#/longitude",
  "schema-failed-keyword": "maximum"
} 1 row in set (0.00 sec)

    由于@schema包含required属性，我们可以将@document设置为否则有效但不包含所需属性的值，然后对其进行与@schema的测试。JSON_SCHEMA_VALIDATION_REPORT()的输出显示验证失败，因为缺少必需元素，如下所示：

    mysql> SET @document = '{}';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document))\G
*************************** 1\. row ***************************
JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document)): {
  "valid": false,
  "reason": "The JSON document location '#' failed requirement 'required' at JSON Schema location '#'",
  "schema-location": "#",
  "document-location": "#",
  "schema-failed-keyword": "required"
} 1 row in set (0.00 sec)

    如果我们现在将@schema的值设置为相同的 JSON 模式，但没有required属性，@document会通过验证，因为它是一个有效的 JSON 对象，即使它不包含任何属性，如下所示：

    mysql> SET @schema = '{
    '> "id": "http://json-schema.org/geo",
    '> "$schema": "http://json-schema.org/draft-04/schema#",
    '> "description": "A geographical coordinate",
    '> "type": "object",
    '> "properties": {
    '>   "latitude": {
    '>     "type": "number",
    '>     "minimum": -90,
    '>     "maximum": 90
    '>   },
    '>   "longitude": {
    '>     "type": "number",
    '>     "minimum": -180,
    '>     "maximum": 180
    '>   }
    '> }
    '>}';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT JSON_SCHEMA_VALIDATION_REPORT(@schema, @document);
+---------------------------------------------------+
| JSON_SCHEMA_VALIDATION_REPORT(@schema, @document) |
+---------------------------------------------------+
| {"valid": true}                                   |
+---------------------------------------------------+
1 row in set (0.00 sec)

14.17.8 JSON 实用函数

原文：dev.mysql.com/doc/refman/8.0/en/json-utility-functions.html

本节记录了作用于 JSON 值或可解析为 JSON 值的字符串的实用函数。JSON_PRETTY() 以易于阅读的格式打印出 JSON 值。JSON_STORAGE_SIZE() 和 JSON_STORAGE_FREE() 分别显示给定 JSON 值使用的存储空间量以及部分更新后 JSON 列中剩余空间量。

  • JSON_PRETTY(*json_val*)

    提供类似于 PHP 和其他语言和数据库系统中实现的 JSON 值的漂亮打印。提供的值必须是 JSON 值或 JSON 值的有效字符串表示。该值中存在的多余空格和换行符对输出没有影响。对于 NULL 值，该函数返回 NULL。如果值不是 JSON 文档，或者无法解析为 JSON 文档，则函数将出错。

    此函数的输出格式遵循以下规则：

    • 每个数组元素或对象成员显示在单独的一行上，相对于其父级缩进一个额外级别。

    • 每个缩进级别增加两个前导空格。

    • 在分隔两个元素或成员的换行符之前打印分隔单个数组元素或对象成员的逗号。

    • 对象成员的键和值由冒号后跟一个空格（': '）分隔。

    • 空对象或数组打印在一行上。在开放和闭合大括号之间不打印空格。

    • 字符串标量和键名中的特殊字符使用与 JSON_QUOTE() 函数相同的规则进行转义。

    mysql> SELECT JSON_PRETTY('123'); # scalar
+--------------------+
| JSON_PRETTY('123') |
+--------------------+
| 123                |
+--------------------+

mysql> SELECT JSON_PRETTY("[1,3,5]"); # array
+------------------------+
| JSON_PRETTY("[1,3,5]") |
+------------------------+
| [
  1,
  3,
  5
]      |
+------------------------+

mysql> SELECT JSON_PRETTY('{"a":"10","b":"15","x":"25"}'); # object
+---------------------------------------------+
| JSON_PRETTY('{"a":"10","b":"15","x":"25"}') |
+---------------------------------------------+
| {
  "a": "10",
  "b": "15",
  "x": "25"
}   |
+---------------------------------------------+

mysql> SELECT JSON_PRETTY('["a",1,{"key1":
    '>    "value1"},"5",     "77" ,
    '>       {"key2":["value3","valueX",
    '> "valueY"]},"j", "2"   ]')\G  # nested arrays and objects
*************************** 1\. row ***************************
JSON_PRETTY('["a",1,{"key1":
             "value1"},"5",     "77" ,
                {"key2":["value3","valuex",
          "valuey"]},"j", "2"   ]'): [
  "a",
  1,
  {
    "key1": "value1"
  },
  "5",
  "77",
  {
    "key2": [
      "value3",
      "valuex",
      "valuey"
    ]
  },
  "j",
  "2"
]

  • JSON_STORAGE_FREE(*json_val*)

    对于JSON列值，此函数显示了在使用JSON_SET()、JSON_REPLACE()或JSON_REMOVE()进行原地更新后，其二进制表示中释放了多少存储空间。参数也可以是有效的 JSON 文档或可以解析为 JSON 的字符串——无论是作为文字值还是作为用户变量的值——在这种情况下，函数返回 0。如果参数是已根据前述描述更新的JSON列值，使其二进制表示占用的空间少于更新之前，则返回一个正的非零值。对于已更新的JSON列，其二进制表示与之前相同或更大，或者更新无法利用部分更新的情况，返回 0；如果参数为NULL，则返回NULL。

    如果*json_val*不为NULL，且既不是有效的 JSON 文档，也无法成功解析为 JSON 文档，则会产生错误。

    在此示例中，我们创建一个包含JSON列的表，然后插入一个包含 JSON 对象的行：

    mysql> CREATE TABLE jtable (jcol JSON);
Query OK, 0 rows affected (0.38 sec)

mysql> INSERT INTO jtable VALUES
 ->     ('{"a": 10, "b": "wxyz", "c": "[true, false]"}');
Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM jtable;
+----------------------------------------------+
| jcol                                         |
+----------------------------------------------+
| {"a": 10, "b": "wxyz", "c": "[true, false]"} |
+----------------------------------------------+
1 row in set (0.00 sec)

    现在我们使用JSON_SET()更新列值，以便执行部分更新；在这种情况下，我们用占用空间更少的值（整数1）替换了由c键指向的值（数组`[true, false]）：

    mysql> UPDATE jtable
 ->     SET jcol = JSON_SET(jcol, "$.a", 10, "$.b", "wxyz", "$.c", 1);
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM jtable;
+--------------------------------+
| jcol                           |
+--------------------------------+
| {"a": 10, "b": "wxyz", "c": 1} |
+--------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_STORAGE_FREE(jcol) FROM jtable;
+-------------------------+
| JSON_STORAGE_FREE(jcol) |
+-------------------------+
|                      14 |
+-------------------------+
1 row in set (0.00 sec)

    连续部分更新对此空闲空间的影响是累积的，如此示例中使用JSON_SET()减少具有键b的值占用的空间（并不进行其他更改）所示：

    mysql> UPDATE jtable
 ->     SET jcol = JSON_SET(jcol, "$.a", 10, "$.b", "wx", "$.c", 1);
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT JSON_STORAGE_FREE(jcol) FROM jtable;
+-------------------------+
| JSON_STORAGE_FREE(jcol) |
+-------------------------+
|                      16 |
+-------------------------+
1 row in set (0.00 sec)

    在不使用JSON_SET()、JSON_REPLACE()或JSON_REMOVE()更新列的情况下，意味着优化器无法原地执行更新；在这种情况下，JSON_STORAGE_FREE()返回 0，如下所示：

    mysql> UPDATE jtable SET jcol = '{"a": 10, "b": 1}';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT JSON_STORAGE_FREE(jcol) FROM jtable;
+-------------------------+
| JSON_STORAGE_FREE(jcol) |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set (0.00 sec)

    只能对列值执行 JSON 文档的部分更新。对于存储 JSON 值的用户变量，值总是完全替换，即使使用JSON_SET()执行更新时也是如此：

    mysql> SET @j = '{"a": 10, "b": "wxyz", "c": "[true, false]"}';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @j = JSON_SET(@j, '$.a', 10, '$.b', 'wxyz', '$.c', '1');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @j, JSON_STORAGE_FREE(@j) AS Free;
+----------------------------------+------+
| @j                               | Free |
+----------------------------------+------+
| {"a": 10, "b": "wxyz", "c": "1"} |    0 |
+----------------------------------+------+
1 row in set (0.00 sec)

    对于 JSON 文本，此函数始终返回 0：

    mysql> SELECT JSON_STORAGE_FREE('{"a": 10, "b": "wxyz", "c": "1"}') AS Free;
+------+
| Free |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

  • JSON_STORAGE_SIZE(*json_val*)

    此函数返回用于存储 JSON 文档的二进制表示所使用的字节数。当参数为JSON列时，这是用于存储 JSON 文档的空间，即插入到列中之前的空间，之后可能对其执行的任何部分更新。*json_val*必须是有效的 JSON 文档或可以成功解析为 JSON 的字符串。如果它是字符串，则函数返回通过将字符串解析为 JSON 并将其转换为二进制而创建的 JSON 二进制表示中的存储空间量。如果参数为NULL，则返回NULL。

    当*json_val*不是NULL，并且不是或无法成功解析为 JSON 文档时，会产生错误。

    为了说明当使用JSON列作为参数时，此函数的行为，我们创建一个名为jtable的表，其中包含一个JSON列jcol，将一个 JSON 值插入表中，然后使用JSON_STORAGE_SIZE()获取此列使用的存储空间，如下所示：

    mysql> CREATE TABLE jtable (jcol JSON);
Query OK, 0 rows affected (0.42 sec)

mysql> INSERT INTO jtable VALUES
 ->     ('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}');
Query OK, 1 row affected (0.04 sec)

mysql> SELECT
 ->     jcol,
 ->     JSON_STORAGE_SIZE(jcol) AS Size,
 ->     JSON_STORAGE_FREE(jcol) AS Free
 -> FROM jtable;
+-----------------------------------------------+------+------+
| jcol                                          | Size | Free |
+-----------------------------------------------+------+------+
| {"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"} |   47 |    0 |
+-----------------------------------------------+------+------+
1 row in set (0.00 sec)

    根据JSON_STORAGE_SIZE()的输出，插入到列中的 JSON 文档占用了 47 字节的空间。我们还使用JSON_STORAGE_FREE()检查了通过任何先前的列部分更新释放的空间量；由于尚未执行任何更新，因此这个值是 0，符合预期。

    接下来我们对表执行一个UPDATE，这应该会导致存储在jcol中的文档的部分更新，然后测试结果如下：

    mysql> UPDATE jtable SET jcol = 
 ->     JSON_SET(jcol, "$.b", "a");
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT
 ->     jcol,
 ->     JSON_STORAGE_SIZE(jcol) AS Size,
 ->     JSON_STORAGE_FREE(jcol) AS Free
 -> FROM jtable;
+--------------------------------------------+------+------+
| jcol                                       | Size | Free |
+--------------------------------------------+------+------+
| {"a": 1000, "b": "a", "c": "[1, 3, 5, 7]"} |   47 |    3 |
+--------------------------------------------+------+------+
1 row in set (0.00 sec)

    前一个查询中JSON_STORAGE_FREE()返回的值表明对 JSON 文档进行了部分更新，并且这释放了 3 字节的用于存储它的空间。JSON_STORAGE_SIZE()返回的结果不受部分更新的影响。

    使用JSON_SET()、JSON_REPLACE()或JSON_REMOVE()进行更新支持部分更新。不能对JSON列直接赋值进行部分更新；在此类更新之后，JSON_STORAGE_SIZE()始终显示新设置值的存储使用情况：

    mysql> UPDATE jtable
mysql>     SET jcol = '{"a": 4.55, "b": "wxyz", "c": "[true, false]"}';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT
 ->     jcol,
 ->     JSON_STORAGE_SIZE(jcol) AS Size,
 ->     JSON_STORAGE_FREE(jcol) AS Free
 -> FROM jtable;
+------------------------------------------------+------+------+
| jcol                                           | Size | Free |
+------------------------------------------------+------+------+
| {"a": 4.55, "b": "wxyz", "c": "[true, false]"} |   56 |    0 |
+------------------------------------------------+------+------+
1 row in set (0.00 sec)

    无法对 JSON 用户变量进行部分更新。这意味着此函数始终显示当前用于存储用户变量中 JSON 文档的空间：

    mysql> SET @j = '[100, "sakila", [1, 3, 5], 425.05]';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @j, JSON_STORAGE_SIZE(@j) AS Size;
+------------------------------------+------+
| @j                                 | Size |
+------------------------------------+------+
| [100, "sakila", [1, 3, 5], 425.05] |   45 |
+------------------------------------+------+
1 row in set (0.00 sec)

mysql> SET @j = JSON_SET(@j, '$[1]', "json");
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @j, JSON_STORAGE_SIZE(@j) AS Size;
+----------------------------------+------+
| @j                               | Size |
+----------------------------------+------+
| [100, "json", [1, 3, 5], 425.05] |   43 |
+----------------------------------+------+
1 row in set (0.00 sec)

mysql> SET @j = JSON_SET(@j, '$[2][0]', JSON_ARRAY(10, 20, 30));
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @j, JSON_STORAGE_SIZE(@j) AS Size;
+---------------------------------------------+------+
| @j                                          | Size |
+---------------------------------------------+------+
| [100, "json", [[10, 20, 30], 3, 5], 425.05] |   56 |
+---------------------------------------------+------+
1 row in set (0.00 sec)

    对于 JSON 文字，此函数始终返回当前使用的存储空间：

    mysql> SELECT
 ->     JSON_STORAGE_SIZE('[100, "sakila", [1, 3, 5], 425.05]') AS A,
 ->     JSON_STORAGE_SIZE('{"a": 1000, "b": "a", "c": "[1, 3, 5, 7]"}') AS B,
 ->     JSON_STORAGE_SIZE('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}') AS C,
 ->     JSON_STORAGE_SIZE('[100, "json", [[10, 20, 30], 3, 5], 425.05]') AS D;
+----+----+----+----+
| A  | B  | C  | D  |
+----+----+----+----+
| 45 | 44 | 47 | 56 |
+----+----+----+----+
1 row in set (0.00 sec)

14.18 复制函数

原文：dev.mysql.com/doc/refman/8.0/en/replication-functions.html

14.18.1 组复制函数

14.18.2 与全局事务标识符（GTID）一起使用的函数

14.18.3 异步复制通道故障转移函数

14.18.4 基于位置的同步函数

下面描述的函数与 MySQL 复制一起使用。

表 14.24 复制函数

名称描述引入版本废弃版本
asynchronous_connection_failover_add_managed()将组成员源服务器配置信息添加到复制通道源列表8.0.23
asynchronous_connection_failover_add_source()将源服务器配置信息添加到复制通道源列表8.0.22
asynchronous_connection_failover_delete_managed()从复制通道源列表中删除受管理的组8.0.23
asynchronous_connection_failover_delete_source()从复制通道源列表中删除源服务器8.0.22
asynchronous_connection_failover_reset()删除与组复制异步故障转移相关的所有设置8.0.27
group_replication_disable_member_action()禁用指定事件的成员操作8.0.26
group_replication_enable_member_action()启用指定事件的成员操作8.0.26
group_replication_get_communication_protocol()获取当前使用的组复制通信协议版本8.0.16
group_replication_get_write_concurrency()获取当前为组设置的最大一致性实例数8.0.13
group_replication_reset_member_actions()将所有成员操作重置为默认值，并将配置版本号设置为 18.0.26
group_replication_set_as_primary()将特定组成员设为主节点8.0.29
group_replication_set_communication_protocol()设置用于组复制通信协议的版本8.0.16
group_replication_set_write_concurrency()设置可以并行执行的最大一致性实例数8.0.13
group_replication_switch_to_multi_primary_mode()将运行在单主模式下的组的模式更改为多主模式8.0.13
group_replication_switch_to_single_primary_mode()将运行在多主模式下的组的模式更改为单主模式8.0.13
GTID_SUBSET()如果子集中的所有 GTIDs 也在集合中，则返回 true；否则返回 false。
GTID_SUBTRACT()返回集合中不在子集中的所有 GTIDs。
MASTER_POS_WAIT()阻塞，直到副本读取并应用到指定位置的所有更新8.0.26
SOURCE_POS_WAIT()阻塞，直到副本读取并应用到指定位置的所有更新8.0.26
WAIT_FOR_EXECUTED_GTID_SET()等待给定的 GTIDs 在副本上执行。
WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS()使用WAIT_FOR_EXECUTED_GTID_SET()。8.0.18
名称描述引入版本废弃版本

14.18.1 组复制函数

原文：dev.mysql.com/doc/refman/8.0/en/group-replication-functions.html

14.18.1.1 配置组复制主要成员的函数

14.18.1.2 配置组复制模式的函数

14.18.1.3 检查和配置组的最大一致性实例数的函数

14.18.1.4 检查和设置组复制通信协议版本的函数

14.18.1.5 设置和重置组复制成员操作的函数

以下部分描述的函数与组复制一起使用。

表 14.25 组复制函数

名称描述引入版本
group_replication_disable_member_action()禁用指定事件的成员操作8.0.26
group_replication_enable_member_action()启用指定事件的成员操作8.0.26
group_replication_get_communication_protocol()获取当前使用的组复制通信协议版本8.0.16
group_replication_get_write_concurrency()获取当前为组设置的最大一致性实例数8.0.13
group_replication_reset_member_actions()将所有成员操作重置为默认值，并将配置版本号设置为 18.0.26
group_replication_set_as_primary()将特定组成员设为主要成员8.0.29
group_replication_set_communication_protocol()设置要使用的组复制通信协议版本8.0.16
group_replication_set_write_concurrency()设置可以并行执行的最大一致性实例数8.0.13
group_replication_switch_to_multi_primary_mode()将运行在单主模式下的组的模式更改为多主模式8.0.13
group_replication_switch_to_single_primary_mode()将运行在多主模式下的组的模式更改为单主模式8.0.13
名称描述引入版本

原文：dev.mysql.com/doc/refman/8.0/en/group-replication-functions-for-new-primary.html

14.18.1.1 配置组复制主服务器的函数

以下函数使您能够设置单一主复制组的成员接管为主服务器。当前主服务器变为只读辅助服务器，指定的组成员成为读写主服务器。该函数可用于在单一主模式下运行的复制组的任何成员。此函数替换了通常的主服务器选举过程；有关更多信息，请参见 Section 20.5.1.1, “Changing the Primary”。

如果标准源到副本复制通道在现有主成员上运行，除了 Group Replication 通道之外，您必须在更改主成员之前停止该复制通道。您可以使用性能模式表replication_group_members中的MEMBER_ROLE列，或group_replication_primary_member状态变量来识别当前主成员。

组正在等待的任何未提交事务必须在操作完成之前提交、回滚或终止。在 MySQL 8.0.29 之前，该函数会等待现有主服务器上的所有活动事务结束，包括在使用该函数后启动的传入事务。从 MySQL 8.0.29 开始，您可以为在使用该函数时运行的事务指定超时。要使超时起作用，组的所有成员必须运行在 MySQL 8.0.29 或更高版本。

当超时到期时，对于尚未达到提交阶段的任何事务，客户端会断开会话，以阻止事务继续进行。已达到提交阶段的事务允许完成。设置超时还会阻止从那时起在主服务器上启动的新事务。明确定义的事务（使用START TRANSACTION或BEGIN语句）也会受到超时、断开连接和传入事务阻止的影响，即使它们不修改任何数据。为了允许在函数操作时检查主服务器，允许执行不修改数据的单个语句，如一致性规则下允许的查询中列出的语句。

  • group_replication_set_as_primary()

    指定组中的特定成员作为新的主服务器，覆盖任何选举过程。

    语法：

    STRING group_replication_set_as_primary(*member_uuid*[, *timeout*])

    参数：

    • member_uuid: 一个包含您希望成为新主服务器的组成员的 UUID 的字符串。

    • timeout: 一个整数，指定在使用该函数时在现有主服务器上运行的事务的超时时间（以秒为单位）。您可以设置超时时间从 0 秒（立即）到 3600 秒（60 分钟）。当您设置超时时，从那时起，主服务器上将无法启动新的事务。超时没有默认设置，因此如果您不设置它，等待时间没有上限，并且在此期间可以启动新的事务。此选项从 MySQL 8.0.29 版本开始提供。

    返回值：

    一个包含操作结果的字符串，例如操作是否成功。

    示例：

    SELECT group_replication_set_as_primary(‘00371d66-3c45-11ea-804b-080027337932’, 300);

    欲了解更多信息，请参阅 Section 20.5.1.1, “Changing the Primary”。

原文：dev.mysql.com/doc/refman/8.0/en/group-replication-functions-for-mode.html

14.18.1.2 配置组复制模式的函数

以下函数使您能够控制复制组运行的模式，即单一主节点模式或多主节点模式。

  • group_replication_switch_to_multi_primary_mode()

    将运行在单一主节点模式下的组更改为多主节点模式。必须在运行在单一主节点模式下的复制组的成员上发出。

    语法：

    STRING group_replication_switch_to_multi_primary_mode()

    此函数没有参数。

    返回值：

    包含操作结果的字符串，例如操作是否成功。

    示例：

    SELECT group_replication_switch_to_multi_primary_mode()

    所有属于该组的成员都变为主节点。

    有关更多信息，请参见第 20.5.1.2 节，“更改组模式”

  • group_replication_switch_to_single_primary_mode()

    将运行在多主节点模式下的组更改为单一主节点模式，无需停止组复制。必须在运行在多主节点模式下的复制组的成员上发出。当您更改为单一主节点模式时，所有组成员上也会禁用严格的一致性检查，如单一主节点模式所需的（group_replication_enforce_update_everywhere_checks=OFF）。

    语法：

    STRING group_replication_switch_to_single_primary_mode([*str*])

    参数：

    • str: 包含应成为新单一主节点的组成员的 UUID 的字符串。组的其他成员将成为辅助节点。

    返回值：

    包含操作结果的字符串，例如操作是否成功。

    示例：

    SELECT group_replication_switch_to_single_primary_mode(*member_uuid*);

    有关更多信息，请参见第 20.5.1.2 节，“更改组模式”

原文：dev.mysql.com/doc/refman/8.0/en/group-replication-functions-for-maximum-consensus.html

14.18.1.3 检查和配置组的最大共识实例的函数

以下函数使您能够检查和配置组可以并行执行的最大共识实例数。

  • group_replication_get_write_concurrency()

    检查组可以并行执行的最大共识实例数。

    语法：

    INT group_replication_get_write_concurrency()

    此函数没有参数。

    返回值：

    当前为组设置的最大共识实例数。

    示例：

    SELECT group_replication_get_write_concurrency()

    欲了解更多信息，请参阅第 20.5.1.3 节，“使用组复制组写共识”。

  • group_replication_set_write_concurrency()

    配置组可以并行执行的最大共识实例数。需要GROUP_REPLICATION_ADMIN权限才能使用此函数。

    语法：

    STRING group_replication_set_write_concurrency(*instances*)

    参数：

    • members: 设置组可以并行执行的最大共识实例数。默认值为 10，有效值为 10 到 200 之间的整数。

    返回值：

    任何导致的错误都会以字符串形式返回。

    示例：

    SELECT group_replication_set_write_concurrency(*instances*);

    欲了解更多信息，请参阅第 20.5.1.3 节，“使用组复制组写共识”。

原文：dev.mysql.com/doc/refman/8.0/en/group-replication-functions-for-communication-protocol.html

14.18.1.4 检查和设置 Group Replication 通信协议版本的函数

以下函数使您能够检查和配置复制组使用的 Group Replication 通信协议版本。

  • MySQL 5.7.14 版本开始允许消息压缩（参见第 20.7.4 节，“消息压缩”）。

  • MySQL 8.0.16 版本还允许消息分段（参见第 20.7.5 节，“消息分段”）。

  • MySQL 8.0.27 版本还允许在组处于单主模式且 group_replication_paxos_single_leader 设置为 true 时，组通信引擎操作具有单一共识领导者（参见第 20.7.3 节，“单一共识领导者”）。

  • group_replication_get_communication_protocol()

    检查当前组正在使用的 Group Replication 通信协议版本。

    语法：

    STRING group_replication_get_communication_protocol()

    此函数没有参数。

    返回值：

    可加入此组并使用组通信协议的最旧 MySQL Server 版本。请注意，group_replication_get_communication_protocol() 函数返回组支持的最低 MySQL 版本，这可能与传递给 group_replication_set_communication_protocol() 的版本号不同，并且可能与使用该函数的成员上安装的 MySQL Server 版本不同。

    如果无法检查协议，因为此服务器实例不属于复制组，则返回错误字符串。

    示例：

    SELECT group_replication_get_communication_protocol();
+------------------------------------------------+
| group_replication_get_communication_protocol() |
+------------------------------------------------+
| 8.0.36                                          |
+------------------------------------------------+

    有关更多信息，请参见第 20.5.1.4 节，“设置组的通信协议版本”。

  • group_replication_set_communication_protocol()

    降级组复制通信协议版本，以便较早版本的成员可以加入，或者在所有成员升级 MySQL 服务器后升级组复制通信协议版本。使用此功能需要GROUP_REPLICATION_ADMIN权限，并且在发出语句时，所有现有组成员必须在线，没有多数成员丢失。

    注意：

    对于 MySQL InnoDB 集群，通信协议版本在使用 AdminAPI 操作更改集群拓扑时会自动管理。您不必自己为 InnoDB 集群使用这些功能。

    语法：

    STRING group_replication_set_communication_protocol(*version*)

    参数：

    • version：对于降级，指定具有最旧安装服务器版本的潜在组成员的 MySQL 服务器版本。在这种情况下，如果可能的话，该命令会使组回退到与该服务器版本兼容的通信协议。您可以指定的最小服务器版本是 MySQL 5.7.14。对于升级，请指定现有组成员已升级到的新 MySQL 服务器版本。

    返回值：

    包含操作结果的字符串，例如操作是否成功。

    示例：

    SELECT group_replication_set_communication_protocol("5.7.25");

    更多信息，请参见 Section 20.5.1.4, “Setting a Group's Communication Protocol Version”。

原文：dev.mysql.com/doc/refman/8.0/en/group-replication-functions-for-member-actions.html

14.18.1.5 设置和重置组复制成员操作的函数

以下函数可用于启用和禁用组成员在指定情况下执行的操作，并将所有成员操作的配置重置为默认设置。只有具有GROUP_REPLICATION_ADMIN权限或已弃用的SUPER权限的管理员才能使用这些函数。

您可以使用group_replication_enable_member_action和group_replication_disable_member_action函数在组的主服务器上配置成员操作。然后，成员操作配置（包括所有成员操作以及它们是否启用或禁用）通过组复制的组消息传播到其他组成员和加入成员。这意味着当处于指定情况时，组成员将以相同的方式行事，您只需在主服务器上使用该函数。

只要安装了组复制插件，这些函数也可以用于不属于任何组的服务器。在这种情况下，成员操作配置不会传播到任何其他服务器。

group_replication_reset_member_actions函数只能在不属于任何组的服务器上使用。它将成员操作配置重置为默认设置，并重置其版本号。服务器必须是可写的（使用read_only系统变量设置为OFF），并且安装了组复制插件。

可用的成员操作如下：

mysql_disable_super_read_only_if_primary

该成员操作从 MySQL 8.0.26 版本开始提供。它在成员被选为组的主服务器后执行，这是事件AFTER_PRIMARY_ELECTION。该成员操作默认启用。您可以使用group_replication_disable_member_action()函数禁用它，并使用group_replication_enable_member_action()重新启用它。

当启用并执行此成员操作时，主节点上的超级只读模式将被禁用，因此主节点变为读写，并接受来自复制源服务器和客户端的更新。这是正常情况。

当禁用并未执行此成员操作时，选举后主节点仍处于超级只读模式。在此状态下，它不接受来自任何客户端的更新，即使是具有CONNECTION_ADMIN或SUPER权限的用户也不行。它仍然会接受由复制线程执行的更新。这种设置意味着当一个群组的目的是为另一个群组提供灾难容忍的次要备份时，您可以确保次要群组与第一个群组保持同步。

mysql_start_failover_channels_if_primary

此成员操作从 MySQL 8.0.27 版本开始提供。它在成员被选举为群组主节点后（即事件AFTER_PRIMARY_ELECTION）执行。该成员操作默认启用。您可以使用group_replication_disable_member_action()函数来禁用它，并使用group_replication_enable_member_action()函数重新启用它。

当启用此成员操作时，在群组复制主节点上的复制通道上设置SOURCE_CONNECTION_AUTO_FAILOVER=1语句时，副本的异步连接故障转移处于活动状态。当功能处于活动状态并正确配置时，如果正在复制的主节点下线或进入错误状态，则新的主节点在选举时会在同一通道上开始复制。这是正常情况。有关配置该功能的说明，请参见第 19.4.9.2 节，“副本的异步连接故障转移”。

当禁用此成员操作时，副本的异步连接故障转移不会发生。如果主节点下线或进入错误状态，则通道的复制将停止。请注意，如果有多个具有SOURCE_CONNECTION_AUTO_FAILOVER=1的通道，则成员操作将覆盖所有通道，因此不能通过此方法单独启用或禁用它们。设置SOURCE_CONNECTION_AUTO_FAILOVER=0以禁用单个通道。

有关成员操作及如何查看成员操作配置的更多信息，请参见第 20.5.1.5 节，“配置成员操作”。

  • group_replication_disable_member_action()

    禁用成员动作，以便成员在指定情况下不执行它。如果您使用该函数的服务器是组的一部分，则必须是单主模式下的当前主服务器，并且必须是大多数成员。更改的设置会传播到其他组成员和加入成员，因此当它们处于指定情况时，它们将以相同的方式行动，您只需在主服务器上使用该函数。

    语法：

    STRING group_replication_disable_member_action(*name*, *event*)

    参数：

    • name：要禁用的成员动作的名称。

    • event：触发成员动作的事件。

    返回值：

    包含操作结果的字符串，例如是否成功。

    示例：

    SELECT group_replication_disable_member_action("mysql_disable_super_read_only_if_primary", "AFTER_PRIMARY_ELECTION");

    有关更多信息，请参见 Section 20.5.1.5, “Configuring Member Actions”。

  • group_replication_enable_member_action()

    启用成员在指定情况下执行的动作。如果您使用该函数的服务器是组的一部分，则必须是单主模式下的当前主服务器，并且必须是大多数成员。更改的设置会传播到其他组成员和加入成员，因此当它们处于指定情况时，它们将以相同的方式行动，您只需在主服务器上使用该函数。

    语法：

    STRING group_replication_enable_member_action(*name*, *event*)

    参数：

    • name：要启用的成员动作的名称。

    • event：触发成员动作的事件。

    返回值：

    包含操作结果的字符串，例如是否成功。

    示例：

    SELECT group_replication_enable_member_action("mysql_disable_super_read_only_if_primary", "AFTER_PRIMARY_ELECTION");

    有关更多信息，请参见 Section 20.5.1.5, “Configuring Member Actions”。

  • group_replication_reset_member_actions()

    将成员动作配置重置为默认设置，并将其版本号重置为 1。

    group_replication_reset_member_actions()函数只能在当前不是组成员的服务器上使用。服务器必须是可写的（使用read_only系统变量设置为OFF），并且安装了 Group Replication 插件。如果您打算将其用作没有成员动作或不同成员动作的独立服务器，则可以使用此函数删除服务器在成为组成员时使用的成员动作配置。

    语法：

    STRING group_replication_reset_member_actions()

    参数：

    无。

    返回值：

    包含操作结果的字符串，例如是否成功。

    示例：

    SELECT group_replication_reset_member_actions();

    欲了解更多信息，请参阅第 20.5.1.5 节，“配置成员操作”。

14.18.2 与全局事务标识符（GTID）一起使用的函数

原文：dev.mysql.com/doc/refman/8.0/en/gtid-functions.html

本节描述的函数用于基于 GTID 的复制。重要的是要记住，所有这些函数都将 GTID 集的字符串表示作为参数。因此，在使用它们时，GTID 集必须始终用引号括起来。有关更多信息，请参见GTID Sets。

两个 GTID 集的并集只是它们作为字符串的表示，用逗号连接在一起。换句话说，您可以定义一个非常简单的函数来获取两个 GTID 集的并集，类似于此处创建的函数：

CREATE FUNCTION GTID_UNION(g1 TEXT, g2 TEXT)
    RETURNS TEXT DETERMINISTIC
    RETURN CONCAT(g1,',',g2);

有关 GTID 及这些 GTID 函数在实践中的使用方式的更多信息，请参见第 19.1.3 节，“具有全局事务标识符的复制”。

表 14.26 GTID 函数

名称描述已弃用
GTID_SUBSET()如果子集中的所有 GTID 也在集合中，则返回 true；否则返回 false。
GTID_SUBTRACT()返回集合中不在子集中的所有 GTID。
WAIT_FOR_EXECUTED_GTID_SET()等待给定的 GTID 在副本上执行。
WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS()使用WAIT_FOR_EXECUTED_GTID_SET()。8.0.18

  • GTID_SUBSET(*set1*,*set2*)

    给定两组全局事务标识符 set1 和 set2，如果 set1 中的所有 GTID 也在 set2 中，则返回 true。如果 set1 或 set2 为 NULL，则返回 NULL。否则返回 false。

    与此函数一起使用的 GTID 集表示为字符串，如以下示例所示：

    mysql> SELECT GTID_SUBSET('3E11FA47-71CA-11E1-9E33-C80AA9429562:23',
 ->     '3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57')\G
*************************** 1\. row ***************************
GTID_SUBSET('3E11FA47-71CA-11E1-9E33-C80AA9429562:23',
    '3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57'): 1 1 row in set (0.00 sec)

mysql> SELECT GTID_SUBSET('3E11FA47-71CA-11E1-9E33-C80AA9429562:23-25',
 ->     '3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57')\G
*************************** 1\. row ***************************
GTID_SUBSET('3E11FA47-71CA-11E1-9E33-C80AA9429562:23-25',
    '3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57'): 1 1 row in set (0.00 sec)

mysql> SELECT GTID_SUBSET('3E11FA47-71CA-11E1-9E33-C80AA9429562:20-25',
 ->     '3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57')\G
*************************** 1\. row ***************************
GTID_SUBSET('3E11FA47-71CA-11E1-9E33-C80AA9429562:20-25',
    '3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57'): 0 1 row in set (0.00 sec)

  • GTID_SUBTRACT(*set1*,*set2*)

    给定两组全局事务标识符 set1 和 set2，仅返回 set1 中不在 set2 中的 GTID。如果 set1 或 set2 为 NULL，则返回 NULL。

    与此函数一起使用的所有 GTID 集表示为字符串，并且必须用引号括起来，如下例所示：

    mysql> SELECT GTID_SUBTRACT('3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57',
 ->     '3E11FA47-71CA-11E1-9E33-C80AA9429562:21')\G
*************************** 1\. row ***************************
GTID_SUBTRACT('3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57',
    '3E11FA47-71CA-11E1-9E33-C80AA9429562:21'): 3e11fa47-71ca-11e1-9e33-c80aa9429562:22-57 1 row in set (0.00 sec)

mysql> SELECT GTID_SUBTRACT('3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57',
 ->     '3E11FA47-71CA-11E1-9E33-C80AA9429562:20-25')\G
*************************** 1\. row ***************************
GTID_SUBTRACT('3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57',
    '3E11FA47-71CA-11E1-9E33-C80AA9429562:20-25'): 3e11fa47-71ca-11e1-9e33-c80aa9429562:26-57 1 row in set (0.00 sec)

mysql> SELECT GTID_SUBTRACT('3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57',
 ->     '3E11FA47-71CA-11E1-9E33-C80AA9429562:23-24')\G
*************************** 1\. row ***************************
GTID_SUBTRACT('3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57',
    '3E11FA47-71CA-11E1-9E33-C80AA9429562:23-24'): 3e11fa47-71ca-11e1-9e33-c80aa9429562:21-22:25-57 1 row in set (0.01 sec)

    从一个 GTID 集中减去它自身会产生一个空集，如下所示：

    mysql> SELECT GTID_SUBTRACT('3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57',
 ->     '3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57')\G
*************************** 1\. row ***************************
GTID_SUBTRACT('3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57',
    '3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57'): 1 row in set (0.00 sec)

  • WAIT_FOR_EXECUTED_GTID_SET(*gtid_set*[, *timeout*])

    等待服务器应用所有全局事务标识符包含在*gtid_set中的事务，即直到条件 GTID_SUBSET(gtid_subset*, @@GLOBAL.gtid_executed)成立为止。有关 GTID 集的定义，请参见第 19.1.3.1 节，“GTID 格式和存储”。

    如果指定了超时时间，并且在所有 GTID 集中的事务被应用之前经过*timeout秒，函数将停止等待。timeout是可选的，默认超时时间为 0 秒，在这种情况下，函数始终等待直到所有 GTID 集中的事务被应用。timeout必须大于或等于 0；在严格 SQL 模式下运行时，负的timeout*值会立即被拒绝并显示错误（ER_WRONG_ARGUMENTS）；否则函数返回NULL并发出警告。

    WAIT_FOR_EXECUTED_GTID_SET()监视服务器上应用的所有 GTID，包括从所有复制通道和用户客户端到达的事务。它不考虑复制通道是否已启动或停止。

    更多信息，请参见第 19.1.3 节，“使用全局事务标识符进行复制”。

    与此函数一起使用的 GTID 集表示为字符串，因此必须像以下示例中所示进行引用：

    mysql> SELECT WAIT_FOR_EXECUTED_GTID_SET('3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5');
 -> 0

    有关 GTID 集的语法描述，请参见第 19.1.3.1 节，“GTID 格式和存储”。

    对于WAIT_FOR_EXECUTED_GTID_SET()，返回值是查询的状态，其中 0 表示成功，1 表示超时。任何其他失败都会生成错误。

    gtid_mode在任何客户端使用此函数等待 GTID 被应用时，不能被更改为 OFF。

  • WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS(*gtid_set*[, *timeout*][,*channel*])

    WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS()已被弃用。请改用WAIT_FOR_EXECUTED_GTID_SET()，它可以工作，无论复制通道或用户客户端通过哪个指定事务到达服务器。

14.18.3 异步复制通道故障转移函数

原文：dev.mysql.com/doc/refman/8.0/en/replication-functions-async-failover.html

以下函数从 MySQL 8.0.22 开始适用于标准源到副本复制，从 MySQL 8.0.23 开始适用于 Group Replication，使您能够向复制通道的源列表中添加和删除复制源服务器。从 MySQL 8.0.27 开始，您还可以清除服务器的源列表。

表 14.27 故障转移通道函数

名称描述引入版本
asynchronous_connection_failover_add_managed()将组成员源服务器配置信息添加到复制通道源列表8.0.23
asynchronous_connection_failover_add_source()将源服务器配置信息添加到复制通道源列表8.0.22
asynchronous_connection_failover_delete_managed()从复制通道源列表中删除托管组8.0.23
asynchronous_connection_failover_delete_source()从复制通道源列表中删除源服务器8.0.22
asynchronous_connection_failover_reset()删除与组复制异步故障转移相关的所有设置8.0.27

异步连接故障转移机制在复制连接（源到副本）失败后，自动从适当列表中为新源建立异步连接。从 MySQL 8.0.23 开始，如果当前连接的源不是组中具有最高加权优先级的源，则连接也会更改。对于作为托管组的一部分定义的 Group Replication 源服务器，如果当前连接的源离开组或不再占多数，连接也会切换到另一个组成员。有关该机制的更多信息，请参见第 19.4.9 节，“使用异步连接故障转移切换源和副本”。

源列表存储在mysql.replication_asynchronous_connection_failover和mysql.replication_asynchronous_connection_failover_managed表中，并且可以在性能模式replication_asynchronous_connection_failover表中查看。

如果复制通道位于启用了副本之间故障切换的组的 Group Replication 主服务器上，则当它们加入或通过任何方法更新时，源列表将广播给所有组成员。副本之间的故障切换由mysql_start_failover_channels_if_primary成员操作控制，默认情况下启用，并且可以使用group_replication_disable_member_action函数禁用。

  • asynchronous_connection_failover_add_managed()

    为受管组（Group Replication 组成员）的复制源服务器添加配置信息到复制通道的源列表中。您只需要添加一个组成员。副本会自动从当前组成员中添加其余成员，然后根据成员变化保持源列表更新。

    语法：

    asynchronous_connection_failover_add_managed(*channel*, *managed_type*, *managed_name*, *host*, *port*, *network_namespace*, *primary_weight*, *secondary_weight*)

    参数：

    • channel: 此复制源服务器所属的复制通道。

    • managed_type: 异步连接故障转移机制必须为此服务器提供的受管服务类型。当前唯一接受的值是GroupReplication。

    • managed_name: 服务器所属的受管组的标识符。对于GroupReplication受管服务，标识符是group_replication_group_name系统变量的值。

    • host: 此复制源服务器的主机名。

    • port: 此复制源服务器的端口号。

    • network_namespace: 此复制源服务器的网络命名空间。指定空字符串，因为此参数保留供将来使用。

    • primary_weight: 当作为受管组的主服务器时，此复制源服务器在复制通道源列表中的优先级。权重范围为 1 到 100，100 为最高。对于主服务器，80 是一个合适的权重。如果当前连接的源不是组中权重最高的源，则异步连接故障转移机制会激活。假设您设置了受管组，为主服务器分配较高的权重，为次要服务器分配较低的权重，当主服务器更改时，其权重增加，副本会切换到连接到主服务器。

    • secondary_weight: 当作为托管组中的次要角色时，此复制源服务器在复制通道源列表中的优先级。权重范围为 1 到 100，100 为最高。对于次要角色，60 是一个合适的权重。

    返回值：

    包含操作结果的字符串，例如操作是否成功。

    示例：

    SELECT asynchronous_connection_failover_add_managed('channel2', 'GroupReplication', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', '127.0.0.1', 3310, '', 80, 60);
+----------------------------------------------------------------------------------------------------------------------------------------------------+
| asynchronous_connection_failover_add_source('channel2', 'GroupReplication', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', '127.0.0.1', 3310, '', 80, 60) |
+----------------------------------------------------------------------------------------------------------------------------------------------------+
| Source managed configuration details successfully inserted.                                                                                        |
+----------------------------------------------------------------------------------------------------------------------------------------------------+

    更多信息，请参见 Section 19.4.9, “使用异步连接故障转移切换源和副本”。

  • asynchronous_connection_failover_add_source()

    为复制通道的源列表添加复制源服务器的配置信息。

    语法：

    asynchronous_connection_failover_add_source(*channel*, *host*, *port*, *network_namespace*, *weight*)

    参数：

    • channel: 此复制源服务器所属源列表的复制通道。

    • host: 此复制源服务器的主机名。

    • port: 此复制源服务器的端口号。

    • network_namespace: 此复制源服务器的网络命名空间。请指定一个空字符串，因为此参数保留供将来使用。

    • weight: 此复制源服务器在复制通道源列表中的优先级。优先级范围为 1 到 100，100 为最高，50 为默认值。当异步连接故障转移机制激活时，通道源列表中具有最高优先级设置的备用源将被选择用于第一次连接尝试。如果此尝试不起作用，则副本将按优先级降序尝试所有列出的源，然后从最高优先级源重新开始。如果多个源具有相同的优先级，则副本会随机排序它们。从 MySQL 8.0.23 开始，如果当前连接的源不是组中权重最高的源，则异步连接故障转移机制将激活。

    返回值：

    包含操作结果的字符串，例如操作是否成功。

    示例：

    SELECT asynchronous_connection_failover_add_source('channel2', '127.0.0.1', 3310, '', 80);
+-------------------------------------------------------------------------------------------------+
| asynchronous_connection_failover_add_source('channel2', '127.0.0.1', 3310, '', 80)              |
+-------------------------------------------------------------------------------------------------+
| Source configuration details successfully inserted.                                             |
+-------------------------------------------------------------------------------------------------+

    更多信息，请参见 Section 19.4.9, “使用异步连接故障转移切换源和副本”。

  • asynchronous_connection_failover_delete_managed()

    从复制通道的源列表中删除整个托管组。使用此函数时，托管组中定义的所有复制源服务器都将从通道的源列表中移除。

    语法：

    asynchronous_connection_failover_delete_managed(*channel*, *managed_name*)

    参数：

    • channel: 此复制源服务器曾经是其所属源列表的复制通道。

    • managed_name：服务器所属的托管组的标识符。对于GroupReplication托管服务，标识符是group_replication_group_name系统变量的值。

    返回值：

    包含操作结果的字符串，例如操作是否成功。

    示例：

    SELECT asynchronous_connection_failover_delete_managed('channel2', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa');
+-----------------------------------------------------------------------------------------------------+
| asynchronous_connection_failover_delete_managed('channel2', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa') |
+-----------------------------------------------------------------------------------------------------+
| Source managed configuration details successfully deleted.                                          |
+-----------------------------------------------------------------------------------------------------+

    有关更多信息，请参见第 19.4.9 节，“使用异步连接故障转移切换源和副本”。

  • asynchronous_connection_failover_delete_source()

    从复制通道的源列表中删除复制源服务器的配置信息。

    语法：

    asynchronous_connection_failover_delete_source(*channel*, *host*, *port*, *network_namespace*)

    参数：

    • channel：此复制源服务器所属源列表的复制通道。

    • host：此复制源服务器的主机名。

    • port：此复制源服务器的端口号。

    • network_namespace：此复制源服务器的网络命名空间。指定空字符串，因为此参数保留供将来使用。

    返回值：

    包含操作结果的字符串，例如操作是否成功。

    示例：

    SELECT asynchronous_connection_failover_delete_source('channel2', '127.0.0.1', 3310, '');
+------------------------------------------------------------------------------------------------+
| asynchronous_connection_failover_delete_source('channel2', '127.0.0.1', 3310, '')              |
+------------------------------------------------------------------------------------------------+
| Source configuration details successfully deleted.                                             |
+------------------------------------------------------------------------------------------------+

    有关更多信息，请参见第 19.4.9 节，“使用异步连接故障转移切换源和副本”。

  • asynchronous_connection_failover_reset()

    删除与异步连接故障转移机制相关的所有设置。该函数清除性能模式表replication_asynchronous_connection_failover和replication_asynchronous_connection_failover_managed。

    asynchronous_connection_failover_reset()只能在当前不属于任何组且没有任何复制通道运行的服务器上使用。您可以使用此函数清理不再在托管组中使用的服务器。

    语法：

    STRING asynchronous_connection_failover_reset()

    参数：

    无。

    返回值：

    包含操作结果的字符串，例如操作是否成功。

    示例：

    mysql> SELECT asynchronous_connection_failover_reset();
+-------------------------------------------------------------------------+
| asynchronous_connection_failover_reset()                                |
+-------------------------------------------------------------------------+
| The UDF asynchronous_connection_failover_reset() executed successfully. |
+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

    欲了解更多信息，请参阅第 19.4.9 节，“使用异步连接故障转移切换源和副本”。

14.18.4 基于位置的同步函数

原文：dev.mysql.com/doc/refman/8.0/en/replication-functions-synchronization.html

此部分列出的函数用于控制 MySQL 复制中源和副本服务器的基于位置的同步。

表 14.28 位置同步函数

名称描述引入版本弃用版本
MASTER_POS_WAIT()阻塞，直到副本已读取并应用了指定位置之前的所有更新8.0.26
SOURCE_POS_WAIT()阻塞，直到副本已读取并应用了指定位置之前的所有更新8.0.26

  • MASTER_POS_WAIT(*log_name*,*log_pos*[,*timeout*][,*channel*])

    这个函数用于控制源和副本同步。它会阻塞，直到副本已经读取并应用了源二进制日志中指定位置之前的所有更新。从 MySQL 8.0.26 开始，MASTER_POS_WAIT()已被弃用，应该使用别名SOURCE_POS_WAIT()。在 MySQL 8.0.26 之前的版本中，请使用MASTER_POS_WAIT()。

    返回值是副本需要等待的日志事件数量，以便前进到指定位置。如果复制 SQL 线程未启动、副本的源信息未初始化、参数不正确或发生错误，则函数返回NULL。如果超过超时时间，则返回-1。如果MASTER_POS_WAIT()等待时复制 SQL 线程停止，则函数返回NULL。如果副本已经超过指定位置，则函数立即返回。

    如果二进制日志文件位置被标记为无效，函数会等待直到知道有效的文件位置。当为复制通道设置了CHANGE REPLICATION SOURCE TO选项GTID_ONLY，并且服务器重新启动或复制停止时，二进制日志文件位置可以被标记为无效。在成功应用超过给定文件位置的事务后，文件位置变为有效。如果应用程序未达到指定位置，则函数会等待直到超时。使用SHOW REPLICA STATUS语句检查二进制日志文件位置是否被标记为无效。

    在多线程复制中，该函数会等待直到达到由replica_checkpoint_group、slave_checkpoint_group、replica_checkpoint_period或slave_checkpoint_period系统变量设置的限制时间，当调用检查点操作更新复制品状态时。根据系统变量的设置，该函数可能会在指定位置到达后的一段时间后返回。

    如果使用了二进制日志事务压缩，并且指定位置处的事务负载已被压缩（作为Transaction_payload_event），该函数会等待直到整个事务被读取和应用，并且位置已更新。

    如果指定了*timeout值，MASTER_POS_WAIT()在经过timeout秒后停止等待。timeout必须大于或等于 0。（当服务器运行在严格 SQL 模式下时，负的timeout值会立即被拒绝，并显示ER_WRONG_ARGUMENTS；否则函数返回*NULL**，并发出警告。）

    可选的*channel*值使您能够命名函数应用于哪个复制通道。有关更多信息，请参见第 19.2.2 节，“复制通道”。

    此函数对于基于语句的复制是不安全的。如果在binlog_format设置为STATEMENT时使用此函数，将记录警告。

  • SOURCE_POS_WAIT(*log_name*,*log_pos*[,*timeout*][,*channel*])

    此函数用于控制源-复制品同步。它会阻塞，直到复制品已读取并应用源二进制日志中指定位置的所有更新。从 MySQL 8.0.26 开始，使用SOURCE_POS_WAIT()代替从该版本开始弃用的MASTER_POS_WAIT()。在 MySQL 8.0.26 之前的版本中，请使用MASTER_POS_WAIT()。

    返回值是复制必须等待的日志事件数以前进到指定位置。如果复制 SQL 线程未启动，复制源信息未初始化，参数不正确或发生错误，则函数返回NULL。如果超时已超过，则返回-1。如果SOURCE_POS_WAIT()在等待时复制 SQL 线程停止，则函数返回NULL。如果复制已超过指定位置，则函数立即返回。

    如果二进制日志文件位置被标记为无效，该函数将等待直到已知有效文件位置。当为复制通道设置了CHANGE REPLICATION SOURCE TO选项GTID_ONLY，并且服务器重新启动或复制停止时，二进制日志文件位置可以被标记为无效。在成功应用超过给定文件位置的事务后，文件位置变为有效。如果应用程序未达到指定位置，则函数将等待超时。使用SHOW REPLICA STATUS语句检查二进制日志文件位置是否已标记为无效。

    在多线程复制中，当调用检查点操作以更新复制状态时，该函数将等待直到达到replica_checkpoint_group或replica_checkpoint_period系统变量设置的限制。根据系统变量的设置，因此该函数可能在达到指定位置后的一段时间后返回。

    如果使用二进制日志事务压缩，并且指定位置处的事务有效载荷已压缩（作为Transaction_payload_event），则函数将等待直到整个事务已被读取和应用，并且位置已更新。

    如果指定了*timeout值，则SOURCE_POS_WAIT()在timeout秒已过时停止等待。timeout必须大于或等于 0。 （在严格 SQL 模式下，负的timeout*值将立即被ER_WRONG_ARGUMENTS拒绝；否则函数返回NULL，并引发警告。）

    可选的*channel*值使您能够命名函数应用于哪个复制通道。有关更多信息，请参见 Section 19.2.2, “Replication Channels”。

    当binlog_format设置为STATEMENT时，此函数不适用于基于语句的复制，如果您在这种情况下使用此函数，将会记录警告。

14.19 聚合函数

原文：dev.mysql.com/doc/refman/8.0/en/aggregate-functions-and-modifiers.html

14.19.1 聚合函数描述

14.19.2 GROUP BY 修饰符

14.19.3 MySQL 对 GROUP BY 的处理

14.19.4 功能依赖的检测

聚合函数操作在一组值上。它们通常与GROUP BY子句一起使用，将值分组为子集。本节描述了大多数聚合函数。有关操作几何值的聚合函数的信息，请参见第 14.16.12 节，“空间聚合函数”。

14.19.1 聚合函数描述

原文：dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html

本节描述了对值集合进行操作的聚合函数。它们通常与GROUP BY子句一起使用，将值分组为子集。

表 14.29 聚合函数

名称描述
AVG()返回参数的平均值
BIT_AND()返回按位与
BIT_OR()返回按位或
BIT_XOR()返回按位异或
COUNT()返回返回的行数计数
COUNT(DISTINCT)返回不同值的数量
GROUP_CONCAT()返回连接的字符串
JSON_ARRAYAGG()将结果集作为单个 JSON 数组返回
JSON_OBJECTAGG()将结果集作为单个 JSON 对象返回
MAX()返回最大值
MIN()返回最小值
STD()返回总体标准偏差
STDDEV()返回总体标准偏差
STDDEV_POP()返回总体标准偏差
STDDEV_SAMP()返回样本标准偏差
SUM()返回总和
VAR_POP()返回总体标准方差
VAR_SAMP()返回样本方差
VARIANCE()返回总体标准方差
名称描述

除非另有说明，聚合函数会忽略NULL值。

如果在不包含GROUP BY子句的语句中使用聚合函数，则相当于对所有行进行分组。有关更多信息，请参见第 14.19.3 节，“MySQL GROUP BY 的处理”。

大多数聚合函数都可以作为窗口函数使用。可以这样使用的函数在其语法描述中通过[*over_clause*]表示，表示一个可选的OVER子句。*over_clause*在第 14.20.2 节，“窗口函数概念和语法”中描述，该节还包括有关窗口函数使用的其他信息。

对于数值参数，方差和标准差函数返回一个DOUBLE值。SUM()和AVG()函数对于精确值参数（整数或DECIMAL）返回一个DECIMAL值，对于近似值参数（FLOAT或DOUBLE）返回一个DOUBLE值。

SUM()和AVG()聚合函数不适用于时间值。（它们将值转换为数字，丢失第一个非数字字符后的所有内容。）为解决此问题，将值转换为数字单位，执行聚合操作，然后将其转换回时间值。示例：

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(*time_col*))) FROM *tbl_name*;
SELECT FROM_DAYS(SUM(TO_DAYS(*date_col*))) FROM *tbl_name*;

诸如SUM()或AVG()这样期望数值参数的函数，如有必要会将参数转换为数字。对于SET或ENUM值，转换操作会使用底层的数值。

BIT_AND()、BIT_OR()和BIT_XOR()聚合函数执行位操作。在 MySQL 8.0 之前，位函数和运算符需要BIGINT（64 位整数）参数，并返回BIGINT值，因此它们的最大范围为 64 位。非BIGINT参数在执行操作之前被转换为BIGINT，可能会发生截断。

在 MySQL 8.0 中，位函数和运算符允许二进制字符串类型参数（BINARY、VARBINARY和BLOB类型），并返回相同类型的值，这使它们能够接受参数并生成大于 64 位的返回值。有关位操作的参数评估和结果类型的讨论，请参见第 14.12 节“位函数和运算符”中的介绍性讨论。

  • AVG([DISTINCT] *expr*) [*over_clause*]

    返回*expr*的平均值。DISTINCT选项可用于返回*expr*的不同值的平均值。

    如果没有匹配的行，AVG()返回NULL。如果*expr*为NULL，该函数也返回NULL。

    如果*over_clause存在，则此函数作为窗口函数执行。over_clause*如第 14.20.2 节“窗口函数概念和语法”中所述；它不能与DISTINCT一起使用。

    mysql> SELECT student_name, AVG(test_score)
       FROM student
       GROUP BY student_name;

  • BIT_AND(*expr*) [*over_clause*]

    返回*expr*中所有位的按位AND。

    结果类型取决于函数参数值是作为二进制字符串还是数字进行评估：

    • 当参数值具有二进制字符串类型且参数不是十六进制文字、位文字或NULL文字时，进行二进制字符串评估。否则进行数值评估，必要时将参数值转换为无符号 64 位整数。

    • 二进制字符串评估会产生与参数值相同长度的二进制字符串。如果参数值长度不相等，则会出现ER_INVALID_BITWISE_OPERANDS_SIZE错误。如果参数大小超过 511 字节，则会出现ER_INVALID_BITWISE_AGGREGATE_OPERANDS_SIZE错误。数值评估会产生一个无符号 64 位整数。

    如果没有匹配的行，BIT_AND()返回一个中性值（所有位设置为 1），其长度与参数值相同。

    NULL值不会影响结果，除非所有值都是NULL。在这种情况下，结果是一个中性值，其长度与参数值相同。

    有关参数评估和结果类型的更多信息，请参见第 14.12 节，“位函数和运算符”中的介绍性讨论。

    如果从mysql客户端中调用BIT_AND()，二进制字符串结果将根据--binary-as-hex的值使用十六进制表示。有关该选项的更多信息，请参见第 6.5.1 节，“mysql — MySQL 命令行客户端”。

    从 MySQL 8.0.12 开始，如果存在*over_clause，此函数将作为窗口函数执行。over_clause*的描述如第 14.20.2 节，“窗口函数概念和语法”中所述。

  • BIT_OR(*expr*) [*over_clause*]

    ��回*expr*中所有位的按位OR。

    结果类型取决于函数参数值是作为二进制字符串还是数字进行评估：

    • 当参数值具有二进制字符串类型且参数不是十六进制文字、位文字或NULL文字时，会发生二进制字符串评估。否则会发生数值评估，必要时将参数值转换为无符号 64 位整数。

    • 二进制字符串评估会产生与参数值相同长度的二进制字符串。如果参数值长度不相等，则会出现ER_INVALID_BITWISE_OPERANDS_SIZE错误。如果参数大小超过 511 字节，则会出现ER_INVALID_BITWISE_AGGREGATE_OPERANDS_SIZE错误。数值评估会产生一个无符号 64 位整数。

    如果没有匹配的行，BIT_OR() 返回一个中性值（所有位设置为 0），其长度与参数值相同。

    NULL 值不会影响结果，除非所有值都是 NULL。在这种情况下，结果是一个中性值，其长度与参数值相同。

    有关参数评估和结果类型的更多信息，请参见 Section 14.12, “Bit Functions and Operators” 中的介绍性讨论。

    如果从 mysql 客户端内调用 BIT_OR()，二进制字符串结果将根据 --binary-as-hex 的值以十六进制表示。有关该选项的更多信息，请参见 Section 6.5.1, “mysql — The MySQL Command-Line Client”。

    从 MySQL 8.0.12 开始，如果存在 over_clause，此函数将作为窗口函数执行。over_clause 如 Section 14.20.2, “Window Function Concepts and Syntax” 中所述。

  • BIT_XOR(*expr*) [*over_clause*]

    返回 expr 中所有位的按位 XOR。

    结果类型取决于函数参数值是作为二进制字符串还是���字进行评估：

    • 当参数值具有二进制字符串类型且参数不是十六进制文字、位文字或 NULL 文字时，进行二进制字符串评估。否则进行数字评估，必要时将参数值转换为无符号 64 位整数。

    • 二进制字符串评估产生与参数值相同长度的二进制字符串。如果参数值长度不相等，则会出现 ER_INVALID_BITWISE_OPERANDS_SIZE 错误。如果参数大小超过 511 字节，则会出现 ER_INVALID_BITWISE_AGGREGATE_OPERANDS_SIZE 错误。数字评估产生一个无符号 64 位整数。

    如果没有匹配的行，BIT_XOR() 返回一个中性值（所有位设置为 0），其长度与参数值相同。

    NULL 值不会影响结果，除非所有值都是 NULL。在这种情况下，结果是一个中性值，其长度与参数值相同。

    有关参数评估和结果类型的更多信息，请参见 Section 14.12, “Bit Functions and Operators” 中的介绍性讨论。

    如果从 mysql 客户端调用 BIT_XOR()，二进制字符串结果将使用十六进制表示，取决于 --binary-as-hex 的值。有关该选项的更多信息，请参见 Section 6.5.1, “mysql — The MySQL Command-Line Client”。

    截至 MySQL 8.0.12，如果存在 over_clause，此函数将作为窗口函数执行。over_clause 如 Section 14.20.2, “Window Function Concepts and Syntax” 中所述。

  • COUNT(*expr*) [*over_clause*]

    返回 SELECT 语句检索的行中 expr 的非 NULL 值的计数。结果是一个 BIGINT - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT") 值。

    如果没有匹配的行，COUNT() 返回 0。COUNT(NULL) 返回 0。

    如果存在 over_clause，此函数将作为窗口函数执行。over_clause 如 Section 14.20.2, “Window Function Concepts and Syntax” 中所述。

    mysql> SELECT student.student_name,COUNT(*)
       FROM student,course
       WHERE student.student_id=course.student_id
       GROUP BY student_name;

    COUNT(*) 有些不同，它返回检索到的行数，无论它们是否包含 NULL 值。

    对于像 InnoDB 这样的事务性存储引擎，存储精确的行计数是有问题的。可能同时发生多个事务，每个事务可能会影响计数。

    InnoDB 不会保留表中行的内部计数，因为并发事务可能同时“看到”不同数量的行。因此，SELECT COUNT(*) 语句仅计算当前事务可见的行数。

    截至 MySQL 8.0.13，对于 InnoDB 表，SELECT COUNT(*) FROM *tbl_name* 查询性能在没有额外子句（如 WHERE 或 GROUP BY）的情况下针对单线程工作负载进行了优化。

    InnoDB 通过遍历最小可用的次要索引来处理 SELECT COUNT(*) 语句，除非索引或优化器提示指示优化器使用不同的索引。如果不存在次要索引，则 InnoDB 通过扫描聚簇索引来处理 SELECT COUNT(*) 语句。

    处理 SELECT COUNT(*) 语句需要一些时间，如果索引记录不完全在缓冲池中。为了更快地计数，创建一个计数器表，并让您的应用程序根据其执行的插入和删除更新它。然而，在数千个并发事务启动对同一计数器表的更新的情况下，这种方法可能不会很好地扩展。如果近似行数足够，使用 SHOW TABLE STATUS。

    InnoDB 处理 SELECT COUNT(*) 和 SELECT COUNT(1) 操作的方式相同。没有性能差异。

    对于 MyISAM 表，如果 SELECT 从一个表中检索，没有检索到其他列，并且没有 WHERE 子句，则 COUNT(*) 被优化为非常快速地返回。例如：

    mysql> SELECT COUNT(*) FROM student;

    这种优化仅适用于 MyISAM 表，因为对于这种存储引擎存储了精确的行数计数，并且可以非常快速地访问。如果第一列被定义为 NOT NULL，COUNT(1) 也仅受到相同优化的影响。

  • COUNT(DISTINCT *expr*,[*expr*...])

    返回具有不同非NULL expr 值的行数计数。

    如果没有匹配的行，COUNT(DISTINCT) 返回 0。

    mysql> SELECT COUNT(DISTINCT results) FROM student;

    在 MySQL 中，您可以通过给出表达式列表来获取不包含 NULL 的不同表达式组合的数量。在标准 SQL 中，您将不得不对 COUNT(DISTINCT ...) 中的所有表达式进行串联。

  • GROUP_CONCAT(*expr*)

    此函数返回一个字符串结果，其中包含来自一组的连接的非NULL 值。如果没有非NULL 值，则返回 NULL。完整语法如下：

    GROUP_CONCAT([DISTINCT] *expr* [,*expr* ...]
             [ORDER BY {*unsigned_integer* | *col_name* | *expr*}
                 [ASC | DESC] [,*col_name* ...]]
             [SEPARATOR *str_val*])

    mysql> SELECT student_name,
         GROUP_CONCAT(test_score)
       FROM student
       GROUP BY student_name;

    或：

    mysql> SELECT student_name,
         GROUP_CONCAT(DISTINCT test_score
                      ORDER BY test_score DESC SEPARATOR ' ')
       FROM student
       GROUP BY student_name;

    在 MySQL 中，您可以获取表达式组合的连接值。要消除重复值，请使用 DISTINCT 子句。要对结果中的值进行排序，请使用 ORDER BY 子句。要以相反顺序排序，请在 ORDER BY 子句中按照您要排序的列的名称后添加 DESC（降序）关键字。默认是升序；这可以通过使用 ASC 关键字明确指定。在组中值之间的默认分隔符是逗号（,）。要明确指定分隔符，请使用 SEPARATOR，后跟应在组值之间插入的字符串文字值。要完全消除分隔符，请指定 SEPARATOR ''。

    结果被截断为由group_concat_max_len系统变量给出的最大长度，其默认值为 1024。该值可以设置更高，尽管返回值的有效最大长度受max_allowed_packet的值限制。在运行时更改group_concat_max_len值的语法如下，其中*val*是无符号整数：

    SET [GLOBAL | SESSION] group_concat_max_len = *val*;

    返回值是一个非二进制或二进制字符串，取决于参数是非二进制还是二进制字符串。结果类型是TEXT或BLOB，除非group_concat_max_len小于或等于 512，此时结果类型为VARCHAR或VARBINARY。

    如果从mysql客户端内调用GROUP_CONCAT()，二进制字符串结果将使用十六进制表示，取决于--binary-as-hex的值。有关该选项的更多信息，请参见第 6.5.1 节，“mysql — MySQL 命令行客户端”。

    另请参阅CONCAT()和CONCAT_WS()：第 14.8 节，“字符串函数和运算符”。

  • JSON_ARRAYAGG(*col_or_expr*) [*over_clause*]

    将结果集聚合为一个单一的JSON数组，其元素由行组成。此数组中元素的顺序是未定义的。该函数作用于一个列或评估为单个值的表达式。如果结果不包含行或出现错误，则返回NULL。如果*col_or_expr*为NULL，则函数返回一个 JSON 数组，其中包含[null]元素。

    从 MySQL 8.0.14 开始，如果存在*over_clause，此函数将作为窗口函数执行。over_clause*的描述如第 14.20.2 节，“窗口函数概念和语法”中所述。

    mysql> SELECT o_id, attribute, value FROM t3;
+------+-----------+-------+
| o_id | attribute | value |
+------+-----------+-------+
|    2 | color     | red   |
|    2 | fabric    | silk  |
|    3 | color     | green |
|    3 | shape     | square|
+------+-----------+-------+
4 rows in set (0.00 sec)

mysql> SELECT o_id, JSON_ARRAYAGG(attribute) AS attributes
 -> FROM t3 GROUP BY o_id;
+------+---------------------+
| o_id | attributes          |
+------+---------------------+
|    2 | ["color", "fabric"] |
|    3 | ["color", "shape"]  |
+------+---------------------+
2 rows in set (0.00 sec)

  • JSON_OBJECTAGG(*key*, *value*) [*over_clause*]

    接受两个列名或表达式作为参数，第一个用作键，第二个用作值，并返回一个包含键值对的 JSON 对象。如果结果不包含行，则返回NULL，或者在出现错误时返回。如果任何键名为NULL或参数数量不等于 2，则会发生错误。

    从 MySQL 8.0.14 开始，如果存在*over_clause，此函数将作为窗口函数执行。over_clause*如第 14.20.2 节，“窗口函数概念和语法”中所述。

    mysql> SELECT o_id, attribute, value FROM t3;
+------+-----------+-------+
| o_id | attribute | value |
+------+-----------+-------+
|    2 | color     | red   |
|    2 | fabric    | silk  |
|    3 | color     | green |
|    3 | shape     | square|
+------+-----------+-------+
4 rows in set (0.00 sec)

mysql> SELECT o_id, JSON_OBJECTAGG(attribute, value)
 -> FROM t3 GROUP BY o_id;
+------+---------------------------------------+
| o_id | JSON_OBJECTAGG(attribute, value)      |
+------+---------------------------------------+
|    2 | {"color": "red", "fabric": "silk"}    |
|    3 | {"color": "green", "shape": "square"} |
+------+---------------------------------------+
2 rows in set (0.00 sec)

    重复键处理。 当此函数的结果被规范化时，具有重复键的值将被丢弃。遵循 MySQL JSON 数据类型规范，不允许重复键，只使用遇到的最后一个值与该键在返回对象中（“最后重复键获胜”）。这意味着在从SELECT中的列使用此函数的结果可能取决于返回行的顺序，这是不被保证的。

    当作为窗口函数使用时，如果帧内存在重复键，结果中只有键的最后一个值。如果ORDER BY规范保证值具有特定顺序，则帧中最后一行的键的值是确定的。如果没有，则键的结果值是不确定的。

    考虑以下内容：

    mysql> CREATE TABLE t(c VARCHAR(10), i INT);
Query OK, 0 rows affected (0.33 sec)

mysql> INSERT INTO t VALUES ('key', 3), ('key', 4), ('key', 5);
Query OK, 3 rows affected (0.10 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT c, i FROM t;
+------+------+
| c    | i    |
+------+------+
| key  |    3 |
| key  |    4 |
| key  |    5 |
+------+------+
3 rows in set (0.00 sec)

mysql> SELECT JSON_OBJECTAGG(c, i) FROM t;
+----------------------+
| JSON_OBJECTAGG(c, i) |
+----------------------+
| {"key": 5}           |
+----------------------+
1 row in set (0.00 sec)

mysql> DELETE FROM t;
Query OK, 3 rows affected (0.08 sec)

mysql> INSERT INTO t VALUES ('key', 3), ('key', 5), ('key', 4);
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT c, i FROM t;
+------+------+
| c    | i    |
+------+------+
| key  |    3 |
| key  |    5 |
| key  |    4 |
+------+------+
3 rows in set (0.00 sec)

mysql> SELECT JSON_OBJECTAGG(c, i) FROM t;
+----------------------+
| JSON_OBJECTAGG(c, i) |
+----------------------+
| {"key": 4}           |
+----------------------+
1 row in set (0.00 sec)

    从上次查询中选择的键是不确定的。如果查询不使用GROUP BY（通常会强加自己的排序），并且您希望特定键的顺序，您可以通过在OVER子句中包含一个ORDER BY规范来将JSON_OBJECTAGG()作为窗口函数调用，以对帧行施加特定顺序。以下示例展示了对于几种不同帧规范，使用和不使用ORDER BY会发生什么。

    没有ORDER BY，帧是整个分区：

    mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER () AS json_object FROM t;
+-------------+
| json_object |
+-------------+
| {"key": 4}  |
| {"key": 4}  |
| {"key": 4}  |
+-------------+

    使用ORDER BY，其中帧是默认的RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（无论升序还是降序）：

    mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER (ORDER BY i) AS json_object FROM t;
+-------------+
| json_object |
+-------------+
| {"key": 3}  |
| {"key": 4}  |
| {"key": 5}  |
+-------------+
mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER (ORDER BY i DESC) AS json_object FROM t;
+-------------+
| json_object |
+-------------+
| {"key": 5}  |
| {"key": 4}  |
| {"key": 3}  |
+-------------+

    使用ORDER BY和整个分区的显式帧：

    mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER (ORDER BY i
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
        AS json_object
       FROM t;
+-------------+
| json_object |
+-------------+
| {"key": 5}  |
| {"key": 5}  |
| {"key": 5}  |
+-------------+

    要返回特定键值（例如最小值或最大值），请在适当查询中包含一个LIMIT子句。例如：

    mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER (ORDER BY i) AS json_object FROM t LIMIT 1;
+-------------+
| json_object |
+-------------+
| {"key": 3}  |
+-------------+
mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER (ORDER BY i DESC) AS json_object FROM t LIMIT 1;
+-------------+
| json_object |
+-------------+
| {"key": 5}  |
+-------------+

    有关 JSON 值的规范化、合并和自动包装，请参阅 JSON 值的规范化、合并和自动包装，获取更多信息和示例。

  • MAX([DISTINCT] *expr*) [*over_clause*]

    返回*expr的最大值。MAX()可能接受一个字符串参数；在这种情况下，它返回最大的字符串值。参见第 10.3.1 节，“MySQL 如何使用索引”。DISTINCT关键字可用于查找expr*的不同值的最大值，但是，这与省略DISTINCT产生相同的结果。

    如果没有匹配的行，或者*expr*为NULL，MAX()返回NULL。

    如果*over_clause存在，则此函数作为窗口函数执行。over_clause*如第 14.20.2 节，“窗口函数概念和语法”中所述；它不能与DISTINCT一起使用。

    mysql> SELECT student_name, MIN(test_score), MAX(test_score)
       FROM student
       GROUP BY student_name;

    对于MAX()，MySQL 当前通过它们的字符串值而不是字符串在集合中的相对位置来比较ENUM和SET列。这与ORDER BY比较它们的方式不同。

  • MIN([DISTINCT] *expr*) [*over_clause*]

    返回*expr的最小值。MIN()可能接受一个字符串参数；在这种情况下，它返回最小的字符串值。参见第 10.3.1 节，“MySQL 如何使用索引”。DISTINCT关键字可用于查找expr*的不同值的最小值，但是，这与省略DISTINCT产生相同的结果。

    如果没有匹配的行，或者*expr*为NULL，MIN()返回NULL。

    如果*over_clause存在，则此函数作为窗口函数执行。over_clause*如第 14.20.2 节，“窗口函数概念和语法”中所述；它不能与DISTINCT一起使用。

    mysql> SELECT student_name, MIN(test_score), MAX(test_score)
       FROM student
       GROUP BY student_name;

    对于MIN()，MySQL 当前通过它们的字符串值而不是字符串在集合中的相对位置来比较ENUM和SET列。这与ORDER BY比较它们的方式不同。

  • STD(*expr*) [*over_clause*]

    返回*expr*的总体标准偏差。STD()是标准 SQL 函数STDDEV_POP()的同义词，作为 MySQL 的扩展提供。

    如果没有匹配的行，或者*expr*为NULL，STD()返回NULL。

    如果存在*over_clause，此函数将作为窗口函数执行。over_clause*的描述如第 14.20.2 节，“窗口函数概念和语法”中所述。

  • STDDEV(*expr*) [*over_clause*]

    返回*expr*的总体标准偏差。STDDEV()是标准 SQL 函数STDDEV_POP()的同义词，用于与 Oracle 兼容。

    如果没有匹配的行，或者*expr*为NULL，STDDEV()返回NULL。

    如果存在*over_clause，此函数将作为窗口函数执行。over_clause*的描述如第 14.20.2 节，“窗口函数概念和语法”中所述。

  • STDDEV_POP(*expr*) [*over_clause*]

    返回*expr*的总体标准偏差（VAR_POP()的平方根）。您还可以使用STD()或STDDEV()，它们是等效的但不是标准 SQL。

    如果没有匹配的行，或者*expr*为NULL，STDDEV_POP()返回NULL。

    如果存在*over_clause，此函数将作为窗口函数执行。over_clause*的描述如第 14.20.2 节，“窗口函数概念和语法”中所述。

  • STDDEV_SAMP(*expr*) [*over_clause*]

    返回*expr*的样本标准偏差（VAR_SAMP()的平方根）。

    如果没有匹配的行，或者*expr*为NULL，STDDEV_SAMP()返回NULL。

    如果存在*over_clause，此函数将作为窗口函数执行。over_clause*的描述如第 14.20.2 节，“窗口函数概念和语法”中所述。

  • SUM([DISTINCT] *expr*) [*over_clause*]

    返回*expr的总和。如果返回集没有行，则SUM()返回NULL。可以使用DISTINCT关键字仅对expr*的不同值求和。

    如果没有匹配的行，或者*expr*为NULL，SUM()返回NULL。

    如果存在*over_clause，此函数将作为窗口函数执行。over_clause*如第 14.20.2 节，“窗口函数概念和语法”中描述的那样；它不能与DISTINCT一起使用。

  • VAR_POP(*expr*) [*over_clause*]

    返回*expr*的总体标准方差。它将行视为整体总体，而不是样本，因此分母是行数。您也可以使用VARIANCE()，它是等效的但不是标准 SQL。

    如果没有匹配的行，或者*expr*为NULL，VAR_POP()返回NULL。

    如果存在*over_clause，此函数将作为窗口函数执行。over_clause*如第 14.20.2 节，“窗口函数概念和语法”中描述的那样。

  • VAR_SAMP(*expr*) [*over_clause*]

    返回*expr*的样本方差。也就是说，分母是行数减一。

    如果没有匹配的行，或者*expr*为NULL，VAR_SAMP()返回NULL。

    如果存在*over_clause，此函数将作为窗口函数执行。over_clause*如第 14.20.2 节，“窗口函数概念和语法”中描述的那样。

  • VARIANCE(*expr*) [*over_clause*]

    返回*expr*的总体标准方差。VARIANCE()是标准 SQL 函数VAR_POP()的同义词，作为 MySQL 扩展提供。

    如果没有匹配的行，或者*expr*为NULL，VARIANCE()返回NULL。

    如果存在*over_clause，此函数将作为窗口函数执行。over_clause*如第 14.20.2 节，“窗口函数概念和语法”中描述的那样。

14.19.2 GROUP BY 修饰符

原文：dev.mysql.com/doc/refman/8.0/en/group-by-modifiers.html

GROUP BY子句允许使用WITH ROLLUP修饰符，导致摘要输出包括代表更高级别（即超级聚合）摘要操作的额外行。因此，ROLLUP使您能够使用单个查询回答多个分析级别的问题。例如，ROLLUP可用于支持 OLAP（在线分析处理）操作。

假设一个sales表有year、country、product和profit列用于记录销售利润：

CREATE TABLE sales
(
    year    INT,
    country VARCHAR(20),
    product VARCHAR(32),
    profit  INT
);

要按年份总结表内容，请使用简单的GROUP BY，如下所示：

mysql> SELECT year, SUM(profit) AS profit
       FROM sales
       GROUP BY year;
+------+--------+
| year | profit |
+------+--------+
| 2000 |   4525 |
| 2001 |   3010 |
+------+--------+

输出显示每年的总利润。要确定所有年份总利润的总和，您必须自己加总各个值或运行另一个查询。或者您可以使用ROLLUP，它通过单个查询提供了两个级别的分析。在GROUP BY子句中添加WITH ROLLUP修饰符会导致查询生成另一行（超级聚合行），显示所有年份值的总计：

mysql> SELECT year, SUM(profit) AS profit
       FROM sales
       GROUP BY year WITH ROLLUP;
+------+--------+
| year | profit |
+------+--------+
| 2000 |   4525 |
| 2001 |   3010 |
| NULL |   7535 |
+------+--------+

year列中的NULL值标识总计超级聚合行。

ROLLUP在有多个GROUP BY列时具有更复杂的效果。在这种情况下，每当除最后一个分组列之外的任何列的值发生变化时，查询都会生成一个额外的超级聚合摘要行。

例如，没有ROLLUP，基于year、country和product的sales表摘要可能如下所示，其中输出仅指示年/国家/产品分析级别的摘要值：

mysql> SELECT year, country, product, SUM(profit) AS profit
       FROM sales
       GROUP BY year, country, product;
+------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2000 | Finland | Phone      |    100 |
| 2000 | India   | Calculator |    150 |
| 2000 | India   | Computer   |   1200 |
| 2000 | USA     | Calculator |     75 |
| 2000 | USA     | Computer   |   1500 |
| 2001 | Finland | Phone      |     10 |
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   2700 |
| 2001 | USA     | TV         |    250 |
+------+---------+------------+--------+

添加了ROLLUP后，查询会生成几行额外的行：

mysql> SELECT year, country, product, SUM(profit) AS profit
       FROM sales
       GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2000 | Finland | Phone      |    100 |
| 2000 | Finland | NULL       |   1600 |
| 2000 | India   | Calculator |    150 |
| 2000 | India   | Computer   |   1200 |
| 2000 | India   | NULL       |   1350 |
| 2000 | USA     | Calculator |     75 |
| 2000 | USA     | Computer   |   1500 |
| 2000 | USA     | NULL       |   1575 |
| 2000 | NULL    | NULL       |   4525 |
| 2001 | Finland | Phone      |     10 |
| 2001 | Finland | NULL       |     10 |
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   2700 |
| 2001 | USA     | TV         |    250 |
| 2001 | USA     | NULL       |   3000 |
| 2001 | NULL    | NULL       |   3010 |
| NULL | NULL    | NULL       |   7535 |
+------+---------+------------+--------+

现在输出包括四个层次的分析摘要信息，而不仅仅是一个：

  • 对于给定年份和国家的每组产品行之后，会出现一个额外的超级聚合摘要行，显示所有产品的总计。这些行的product列设置为NULL。

  • 对于给定年份的每组行之后，会出现一个额外的超级聚合摘要行，显示所有国家和产品的总计。这些行的country和products列设置为NULL。

  • 最后，在所有其他行之后，会出现一个额外的超级聚合摘要行，显示所有年份、国家和产品的总计。此行的year、country和products列设置为NULL。

每个超级聚合行中的NULL指示符在将行发送到客户端时生成。服务器查看在第一个发生值变化的最左边的GROUP BY子句中命名的列。对于结果集中的任何列，其名称与这些名称中的任何一个匹配，其值都设置为NULL。（如果按列位置指定分组列，则服务器通过位置确定要设置为NULL的列。）

因为在超级聚合行中的NULL值是在查询处理的最后阶段放入结果集中的，所以你只能在选择列表或HAVING子句中将它们作为NULL值进行测试。你不能在连接条件或WHERE子句中将它们作为NULL值进行测试，以确定选择哪些行。例如，你不能在查询中添加WHERE product IS NULL来从输出中消除除了超级聚合行之外的所有行。

NULL值在客户端显示为NULL，可以使用任何 MySQL 客户端编程接口进行测试。然而，在这一点上，你无法区分NULL是代表常规分组值还是超级聚合值。要测试区分，使用稍后描述的GROUPING()函数。

以前，MySQL 不允许在具有WITH ROLLUP选项的查询中使用DISTINCT或ORDER BY。这个限制在 MySQL 8.0.12 及更高版本中被取消。（Bug #87450，Bug #86311，Bug #26640100，Bug #26073513）

对于GROUP BY ... WITH ROLLUP查询，为了测试结果中的NULL值是否代表超级聚合值，可以在选择列表、HAVING子句和（从 MySQL 8.0.12 开始）ORDER BY子句中使用GROUPING()函数。例如，GROUPING(year)在year列中的NULL出现在超级聚合行时返回 1，否则返回 0。类似地，GROUPING(country)和GROUPING(product)分别在country和product列中的超级聚合NULL值时返回 1：

mysql> SELECT
         year, country, product, SUM(profit) AS profit,
         GROUPING(year) AS grp_year,
         GROUPING(country) AS grp_country,
         GROUPING(product) AS grp_product
       FROM sales
       GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+--------+----------+-------------+-------------+
| year | country | product    | profit | grp_year | grp_country | grp_product |
+------+---------+------------+--------+----------+-------------+-------------+
| 2000 | Finland | Computer   |   1500 |        0 |           0 |           0 |
| 2000 | Finland | Phone      |    100 |        0 |           0 |           0 |
| 2000 | Finland | NULL       |   1600 |        0 |           0 |           1 |
| 2000 | India   | Calculator |    150 |        0 |           0 |           0 |
| 2000 | India   | Computer   |   1200 |        0 |           0 |           0 |
| 2000 | India   | NULL       |   1350 |        0 |           0 |           1 |
| 2000 | USA     | Calculator |     75 |        0 |           0 |           0 |
| 2000 | USA     | Computer   |   1500 |        0 |           0 |           0 |
| 2000 | USA     | NULL       |   1575 |        0 |           0 |           1 |
| 2000 | NULL    | NULL       |   4525 |        0 |           1 |           1 |
| 2001 | Finland | Phone      |     10 |        0 |           0 |           0 |
| 2001 | Finland | NULL       |     10 |        0 |           0 |           1 |
| 2001 | USA     | Calculator |     50 |        0 |           0 |           0 |
| 2001 | USA     | Computer   |   2700 |        0 |           0 |           0 |
| 2001 | USA     | TV         |    250 |        0 |           0 |           0 |
| 2001 | USA     | NULL       |   3000 |        0 |           0 |           1 |
| 2001 | NULL    | NULL       |   3010 |        0 |           1 |           1 |
| NULL | NULL    | NULL       |   7535 |        1 |           1 |           1 |
+------+---------+------------+--------+----------+-------------+-------------+

你可以使用GROUPING()来替换超级聚合NULL值的标签，而不是直接显示GROUPING()的结果：

mysql> SELECT
         IF(GROUPING(year), 'All years', year) AS year,
         IF(GROUPING(country), 'All countries', country) AS country,
         IF(GROUPING(product), 'All products', product) AS product,
         SUM(profit) AS profit
       FROM sales
       GROUP BY year, country, product WITH ROLLUP;
+-----------+---------------+--------------+--------+
| year      | country       | product      | profit |
+-----------+---------------+--------------+--------+
| 2000      | Finland       | Computer     |   1500 |
| 2000      | Finland       | Phone        |    100 |
| 2000      | Finland       | All products |   1600 |
| 2000      | India         | Calculator   |    150 |
| 2000      | India         | Computer     |   1200 |
| 2000      | India         | All products |   1350 |
| 2000      | USA           | Calculator   |     75 |
| 2000      | USA           | Computer     |   1500 |
| 2000      | USA           | All products |   1575 |
| 2000      | All countries | All products |   4525 |
| 2001      | Finland       | Phone        |     10 |
| 2001      | Finland       | All products |     10 |
| 2001      | USA           | Calculator   |     50 |
| 2001      | USA           | Computer     |   2700 |
| 2001      | USA           | TV           |    250 |
| 2001      | USA           | All products |   3000 |
| 2001      | All countries | All products |   3010 |
| All years | All countries | All products |   7535 |
+-----------+---------------+--------------+--------+

带有多个表达式参数的GROUPING()函数返回一个结果，表示将每个表达式的结果组合在一起的位掩码，最低位对应最右边表达式的结果。例如，GROUPING(year, country, product)的计算如下：

 result for GROUPING(*product*)
+ result for GROUPING(*country*) << 1
+ result for GROUPING(*year*) << 2

这样的GROUPING()的结果非零，如果任何表达式代表超级聚合NULL，那么你可以只返回超级聚合行并过滤掉常规分组行，如下所示：

mysql> SELECT year, country, product, SUM(profit) AS profit
       FROM sales
       GROUP BY year, country, product WITH ROLLUP
       HAVING GROUPING(year, country, product) <> 0;
+------+---------+---------+--------+
| year | country | product | profit |
+------+---------+---------+--------+
| 2000 | Finland | NULL    |   1600 |
| 2000 | India   | NULL    |   1350 |
| 2000 | USA     | NULL    |   1575 |
| 2000 | NULL    | NULL    |   4525 |
| 2001 | Finland | NULL    |     10 |
| 2001 | USA     | NULL    |   3000 |
| 2001 | NULL    | NULL    |   3010 |
| NULL | NULL    | NULL    |   7535 |
+------+---------+---------+--------+

sales 表中不包含 NULL 值，因此 ROLLUP 结果中的所有 NULL 值都代表超级聚合值。当数据集包含 NULL 值时，ROLLUP 汇总可能不仅在超级聚合行中包含 NULL 值，还可能在常规分组行中包含 NULL 值。GROUPING() 可以帮助区分它们。假设表 t1 包含一个简单的数据集，其中有两个用于一组数量值的分组因素，其中 NULL 表示类似于“其他”或“未知”：

mysql> SELECT * FROM t1;
+------+-------+----------+
| name | size  | quantity |
+------+-------+----------+
| ball | small |       10 |
| ball | large |       20 |
| ball | NULL  |        5 |
| hoop | small |       15 |
| hoop | large |        5 |
| hoop | NULL  |        3 |
+------+-------+----------+

简单的 ROLLUP 操作会产生这些结果，在其中很难区分超级聚合行中的 NULL 值和常规分组行中的 NULL 值：

mysql> SELECT name, size, SUM(quantity) AS quantity
       FROM t1
       GROUP BY name, size WITH ROLLUP;
+------+-------+----------+
| name | size  | quantity |
+------+-------+----------+
| ball | NULL  |        5 |
| ball | large |       20 |
| ball | small |       10 |
| ball | NULL  |       35 |
| hoop | NULL  |        3 |
| hoop | large |        5 |
| hoop | small |       15 |
| hoop | NULL  |       23 |
| NULL | NULL  |       58 |
+------+-------+----------+

使用 GROUPING() 来替换超级聚合 NULL 值的标签使结果更容易解释：

mysql> SELECT
         IF(GROUPING(name) = 1, 'All items', name) AS name,
         IF(GROUPING(size) = 1, 'All sizes', size) AS size,
         SUM(quantity) AS quantity
       FROM t1
       GROUP BY name, size WITH ROLLUP;
+-----------+-----------+----------+
| name      | size      | quantity |
+-----------+-----------+----------+
| ball      | NULL      |        5 |
| ball      | large     |       20 |
| ball      | small     |       10 |
| ball      | All sizes |       35 |
| hoop      | NULL      |        3 |
| hoop      | large     |        5 |
| hoop      | small     |       15 |
| hoop      | All sizes |       23 |
| All items | All sizes |       58 |
+-----------+-----------+----------+

使用 ROLLUP 时的其他考虑事项

以下讨论列出了 MySQL 实现 ROLLUP 的一些特定行为。

在 MySQL 8.0.12 之前，当使用 ROLLUP 时，不能同时使用 ORDER BY 子句对结果进行排序。换句话说，在 MySQL 中，ROLLUP 和 ORDER BY 是互斥的。然而，你仍然可以在排序顺序上有一定的控制。为了绕过不能将 ROLLUP 与 ORDER BY 结合使用的限制，并实现对分组结果的特定排序顺序，可以将分组结果集生成为派生表，并对其应用 ORDER BY。例如：

mysql> SELECT * FROM
         (SELECT year, SUM(profit) AS profit
         FROM sales GROUP BY year WITH ROLLUP) AS dt
       ORDER BY year DESC;
+------+--------+
| year | profit |
+------+--------+
| 2001 |   3010 |
| 2000 |   4525 |
| NULL |   7535 |
+------+--------+

截至 MySQL 8.0.12，ORDER BY 和 ROLLUP 可以一起使用，这使得可以使用 ORDER BY 和 GROUPING() 来实现对分组结果的特定排序顺序。例如：

mysql> SELECT year, SUM(profit) AS profit
       FROM sales
       GROUP BY year WITH ROLLUP
       ORDER BY GROUPING(year) DESC;
+------+--------+
| year | profit |
+------+--------+
| NULL |   7535 |
| 2000 |   4525 |
| 2001 |   3010 |
+------+--------+

在这两种情况下，超级聚合摘要行与它们计算出的行一起排序，并且它们的放置取决于排序顺序（升序排序时在末尾，降序排序时在开头）。

LIMIT 可以用于限制返回给客户端的行数。LIMIT 应用于 ROLLUP 之后，因此限制适用于 ROLLUP 添加的额外行。例如：

mysql> SELECT year, country, product, SUM(profit) AS profit
       FROM sales
       GROUP BY year, country, product WITH ROLLUP
       LIMIT 5;
+------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2000 | Finland | Phone      |    100 |
| 2000 | Finland | NULL       |   1600 |
| 2000 | India   | Calculator |    150 |
| 2000 | India   | Computer   |   1200 |
+------+---------+------------+--------+

使用 LIMIT 与 ROLLUP 可能会产生更难解释的结果，因为对于理解超级聚合行来说，上下文更少。

MySQL 的一个扩展允许在选择列表中命名不在 GROUP BY 列表中出现的列。（有关非聚合列和 GROUP BY 的信息，请参阅 第 14.19.3 节，“MySQL 对 GROUP BY 的处理”。）在这种情况下，服务器可以自由选择摘要行中来自此非聚合列的任何值，包括 WITH ROLLUP 添加的额外行。例如，在以下查询中，country 是一个非聚合列，不在 GROUP BY 列表中出现，为此列选择的值是不确定的：

mysql> SELECT year, country, SUM(profit) AS profit
       FROM sales
       GROUP BY year WITH ROLLUP;
+------+---------+--------+
| year | country | profit |
+------+---------+--------+
| 2000 | India   |   4525 |
| 2001 | USA     |   3010 |
| NULL | USA     |   7535 |
+------+---------+--------+

当未启用ONLY_FULL_GROUP_BY SQL 模式时，允许这种行为。如果启用了该模式，服务器会因为country未在GROUP BY子句中列出而拒绝查询。启用ONLY_FULL_GROUP_BY后，您仍可以通过对非确定性值列使用ANY_VALUE()函数来执行查询：

mysql> SELECT year, ANY_VALUE(country) AS country, SUM(profit) AS profit
       FROM sales
       GROUP BY year WITH ROLLUP;
+------+---------+--------+
| year | country | profit |
+------+---------+--------+
| 2000 | India   |   4525 |
| 2001 | USA     |   3010 |
| NULL | USA     |   7535 |
+------+---------+--------+

在 MySQL 8.0.28 及更高版本中，rollup 列不能作为MATCH()的参数（并将被拒绝并显示错误），除非在WHERE子句中调用。有关更多信息，请参见第 14.9 节，“全文搜索函数”。

avatar
文章
阅读
粉丝