MySQL :: MySQL 8.4 参考手册 :: 14.18.2 与全局事务标识符（GTID）一起使用的函数

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 14 函数和运算符 / 14.18 复制函数 / 14.18.2 与全局事务标识符（GTID）一起使用的函数

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 84%

14.18.2 与全局事务标识符 (GTID) 一起使用的函数14.18.2 Functions Used with Global Transaction Identifiers (GTIDs)

本节描述的函数用于基于 GTID 的复制。重要的是要记住，所有这些函数都以 GTID 集的字符串表示作为参数。因此，在使用它们时，GTID 集必须始终加引号。有关更多信息，请参见 GTID 集。The functions described in this section are used with GTID-based replication. It is important to keep in mind that all of these functions take string representations of GTID sets as arguments. As such, the GTID sets must always be quoted when used with them. See GTID Sets for more information.

两个 GTID 集合的并集只是它们作为字符串的表示，通过一个逗号连接在一起。换句话说，你可以定义一个非常简单的函数来获得两个 GTID 集合的并集，类似于这里创建的函数：The union of two GTID sets is simply their representations as strings, joined together with an interposed comma. In other words, you can define a very simple function for obtaining the union of two GTID sets, similar to that created here:

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

有关 GTID 以及这些 GTID 函数在实际中的使用方法的更多信息，请参见第 19.1.3 节，“使用全局事务标识符的复制”。For more information about GTIDs and how these GTID functions are used in practice, see Section 19.1.3, “Replication with Global Transaction Identifiers”.

表 14.26 GTID 函数Table 14.26 GTID Functions

名称Name	说明Description
`WAIT_FOR_EXECUTED_GTID_SET()`	等待给定的 GTID 在副本上执行完毕。Wait until the given GTIDs have executed on the replica.

GTID_SUBSET(set1,set2)

给定两个全局事务标识集合 set1 和 set2，如果 set1 中的所有 GTID 也都在 set2 中，则返回 true。如果 set1 或 set2 为 NULL，则返回 NULL。否则返回 false。Given two sets of global transaction identifiers set1 and set2, returns true if all GTIDs in set1 are also in set2. Returns NULL if set1 or set2 is NULL. Returns false otherwise.

此函数使用的 GTID 集合表示为字符串，如以下示例所示：The GTID sets used with this function are represented as strings, as shown in the following examples:

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。Given two sets of global transaction identifiers set1 and set2, returns only those GTIDs from set1 that are not in set2. Returns NULL if set1 or set2 is NULL.

与此函数一起使用的所有 GTID 集都以字符串形式表示，必须加引号，如以下示例所示：All GTID sets used with this function are represented as strings and must be quoted, as shown in these examples:

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 集会产生一个空集，如下所示：Subtracting a GTID set from itself produces an empty set, as shown here:

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 格式与存储”。Wait until the server has applied all of the transactions whose global transaction identifiers are contained in gtid_set; that is, until the condition GTID_SUBSET(gtid_subset, @@GLOBAL.gtid_executed) holds. See Section 19.1.3.1, “GTID Format and Storage” for a definition of GTID sets.
如果指定了超时，并且在 GTID 集中的所有事务都已应用之前经过了 timeout 秒，该函数将停止等待。timeout 是可选的，默认超时为 0 秒，在这种情况下，函数将始终等待直到 GTID 集中的所有事务都已应用。timeout 必须大于或等于 0；在严格 SQL 模式下，负的 timeout 值会立即被拒绝并报错 (ER_WRONG_ARGUMENTS)；否则，函数返回 NULL，并发出警告。If a timeout is specified, and timeout seconds elapse before all of the transactions in the GTID set have been applied, the function stops waiting. timeout is optional, and the default timeout is 0 seconds, in which case the function always waits until all of the transactions in the GTID set have been applied. timeout must be greater than or equal to 0; when running in strict SQL mode, a negative timeout value is immediately rejected with an error (ER_WRONG_ARGUMENTS); otherwise the function returns NULL, and raises a warning.
WAIT_FOR_EXECUTED_GTID_SET() 监视服务器上应用的所有 GTID，包括来自所有复制通道和用户客户端的事务。它不考虑复制通道是否已启动或停止。WAIT_FOR_EXECUTED_GTID_SET() monitors all the GTIDs that are applied on the server, including transactions that arrive from all replication channels and user clients. It does not take into account whether replication channels have been started or stopped.
欲了解更多信息，请参见第 19.1.3 节，“使用全局事务标识符的复制”。For more information, see Section 19.1.3, “Replication with Global Transaction Identifiers”.
与此函数一起使用的 GTID 集表示为字符串，因此必须像以下示例所示那样加引号：GTID sets used with this function are represented as strings and so must be quoted as shown in the following example:
```
mysql> SELECT WAIT_FOR_EXECUTED_GTID_SET('3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5');
        -> 0
```
关于 GTID 集的语法描述，请参见第 19.1.3.1 节，“GTID 格式与存储”。For a syntax description for GTID sets, see Section 19.1.3.1, “GTID Format and Storage”.
对于 WAIT_FOR_EXECUTED_GTID_SET()，返回值表示查询的状态，其中 0 表示成功，1 表示超时。其他任何失败都会生成错误。For WAIT_FOR_EXECUTED_GTID_SET(), the return value is the state of the query, where 0 represents success, and 1 represents timeout. Any other failures generate an error.
gtid_mode 在任意客户端使用此函数等待 GTID 应用时，不能更改为 OFF。gtid_mode cannot be changed to OFF while any client is using this function to wait for GTIDs to be applied.

首页上一级下一节