MySQL的函数和运算符 - 杂项函数（3）

数据库杂货铺 2021-10-07

377

杂项函数

● 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。如果超过了 timeout，则返回 -1。如果复制 SQL 线程在 MASTER_POS_WAIT() 等待时停止，则该函数返回 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，零或负 timeout 表示没有超时。

可选的 channel 值用于指定应用此函数的复制通道。

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

● NAME_CONST(name,value)

返回给定的值。当用于生成结果集列时，NAME_CONST() 使列具有指定的名称。参数应该是常量。

mysql> SELECT NAME_CONST('myname', 14);
+--------+
| myname |
+--------+
|     14 |
+--------+

此函数仅供内部使用。服务器在从存储程序中编写包含对本地程序变量的引用的语句时使用它。你可能会在 mysqlbinlog 的输出中看到这个函数。

对于你的应用程序，可以通过使用简单的别名得到与刚才示例中完全相同的结果，像这样：

mysql> SELECT 14 AS myname;
+--------+
| myname |
+--------+
|     14 |
+--------+
1 row in set (0.00 sec)

● SLEEP(duration)

根据 duration 参数给出的秒数休眠(暂停)，然后返回 0。持续时间可能有小数部分。如果参数为 NULL 或负数，SLEEP() 将产生警告，在严格的 SQL 模式下将产生错误。

当睡眠正常返回(没有中断)，它返回 0：

mysql> SELECT SLEEP(1000);
+-------------+
| SLEEP(1000) |
+-------------+
|           0 |
+-------------+

当 SLEEP() 是被中断的查询调用的唯一对象时，它返回 1，查询本身没有返回错误。无论查询是否被终止或超时，都为如此：

■ 语句被来自另一个会话的 KILL QUERY 中断：

mysql> SELECT SLEEP(1000);
+-------------+
| SLEEP(1000) |
+-------------+
|           1 |
+-------------+

■ 语句被超时中断：

mysql> SELECT *+ MAX_EXECUTION_TIME(1) */ SLEEP(1000);
+-------------+
| SLEEP(1000) |
+-------------+
|           1 |
+-------------+

当 SLEEP() 只是被中断查询的一部分时，查询返回一个错误：

■ 语句被来自另一个会话的 KILL QUERY 中断：

mysql> SELECT 1 FROM t1 WHERE SLEEP(1000);
ERROR 1317 (70100): Query execution was interrupted

■ 语句被超时中断：

mysql> SELECT /*+ MAX_EXECUTION_TIME(1000) */ 1 FROM t1 WHERE SLEEP(1000);
ERROR 3024 (HY000): Query execution was interrupted, maximum statement
execution time exceeded

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

● SOURCE_POS_WAIT(log_name,log_pos[,timeout][,channel])

本功能用于控制源/副本同步。它会阻塞，直到副本读取并应用了源二进制日志中指定位置的所有更新。从 MySQL 8.0.26 开始，使用 SOURCE_POS_WAIT() 来代替 MASTER_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 系统变量设置的限制到期。根据系统变量的设置，函数可能会在到达指定位置后的一段时间返回。

如果指定了 timeout 值，SOURCE_POS_WAIT() 将在 timeout 秒数过去后停止等待。timeout 必须大于0，零或负 timeout 表示没有超时。

可选的 channel 值用于指定函数应用的复制通道。

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

● UUID()

返回根据 RFC 4122 “a Universally Unique Identifier (UUID) URN Namespace”(http://www.ietf.org/rfc/rfc4122.txt) 生成的统一唯一标识符(UUID)。

UUID 被设计为一个在空间和时间上全局唯一的数字。对 UUID() 的两次调用预计将生成两个不同的值，即使这些调用是在两个相互没有连接的独立设备上执行的。

警告

尽管 UUID() 值被设计为是惟一的，但它们不一定是不可猜测的或不可预测的。如果需要不可预测性，则应该以其他方式生成UUID值。

UUID() 返回一个符合 RFC 4122 中描述的 UUID 版本 1 的值。该值是一个128位的数字，格式为 aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeee，用 5 个 16 进制的 utf8 字符串表示。

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

■ 第四个数字保持时间唯一性，以防时间戳值失去单调性(例如，由于夏令时)。

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

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

mysql> SELECT UUID();
        -> '6ccd780c-baba-1026-9564-5b8c656024db'

要在字符串和二进制 UUID 值之间进行转换，请使用 UUID_TO_BIN() 和 BIN_TO_UUID() 函数。要检查一个字符串是否为有效的 UUID 值，请使用 IS_UUID() 函数。

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

● UUID_SHORT()

返回一个64位无符号整数的“短”通用标识符。UUID_SHORT() 返回的值与 UUID() 函数返回的 128 位字符串格式标识符不同，并且具有不同的唯一性属性。如果满足以下条件，UUID_SHORT() 的值保证是唯一的：

■ 当前服务器的 server_id 值在0到255之间，并且在源服务器和副本服务器集合中是唯一的

■ 在 mysqld 重启之间，没有设置服务器主机的系统时间

■ 在 mysqld 重新启动之间，平均每秒调用 UUID_SHORT() 少于1600万次

UUID_SHORT() 的返回值是这样构造的：

  (server_id & 255) << 56
+ (server_startup_time_in_seconds << 24)
+ incremented_variable++;

mysql> SELECT UUID_SHORT();
        -> 92395783831158784

注意

UUID_SHORT() 不能用于基于语句的复制。

● UUID_TO_BIN(string_uuid), UUID_TO_BIN(string_uuid, swap_flag)

将字符串 UUID 转换为二进制 UUID 并返回结果。返回的二进制 UUID 是一个 VARBINARY(16) 值。如果 UUID 参数为 NULL，则返回值为 NULL。如果任何参数无效，则会发生错误。

UUID_TO_BIN() 有一个或两个参数：

■ 单参数形式接受一个字符串 UUID 值。二进制结果与字符串参数的顺序相同。

■ 双参数形式接受一个字符串 UUID 值和一个标志值：

○ 如果 swap_flag 为 0，则双参数形式等同于单参数形式。二进制结果与字符串参数的顺序相同。

○ 如果 swap_flag 为 1，则返回值的格式不同：time-low和time-high部分(分别是第1组和第3组16进制数字)被交换。这将变化较快的部分向右移动，如果结果存储在已索引的列中，则可以提高索引效率。

时间部分交换假设使用 UUID 版本1的值，例如由 UUID() 函数生成的值。对于不遵循版本 1 格式的其他方法生成的 UUID 值，时间部分交换没有任何好处。

假设有以下字符串 UUID 值：

mysql> SET @uuid = '6ccd780c-baba-1026-9564-5b8c656024db';

要将字符串 UUID 转换为二进制（带或不带时间部分交换），请使用 UUID_TO_BIN()：

mysql> SELECT HEX(UUID_TO_BIN(@uuid));
+----------------------------------+
| HEX(UUID_TO_BIN(@uuid))          |
+----------------------------------+
| 6CCD780CBABA102695645B8C656024DB |
+----------------------------------+
mysql> SELECT HEX(UUID_TO_BIN(@uuid, 0));
+----------------------------------+
| HEX(UUID_TO_BIN(@uuid, 0))       |
+----------------------------------+
| 6CCD780CBABA102695645B8C656024DB |
+----------------------------------+
mysql> SELECT HEX(UUID_TO_BIN(@uuid, 1));
+----------------------------------+
| HEX(UUID_TO_BIN(@uuid, 1))       |
+----------------------------------+
| 1026BABA6CCD780C95645B8C656024DB |
+----------------------------------+

要将 UUID_TO_BIN() 返回的二进制 UUID 转换为字符串 UUID，请使用 BIN_TO_UUID()。如果用第二个参数 1 来交换时间部分，调用 UUID_TO_BIN() 来生成一个二进制 UUID，也应该传递第二个参数 1 给 BIN_TO_UUID() 来在将二进制 UUID 转换回字符串 UUID 时取消交换时间部分：

mysql> SELECT BIN_TO_UUID(UUID_TO_BIN(@uuid));
+--------------------------------------+
| BIN_TO_UUID(UUID_TO_BIN(@uuid))      |
+--------------------------------------+
| 6ccd780c-baba-1026-9564-5b8c656024db |
+--------------------------------------+
mysql> SELECT BIN_TO_UUID(UUID_TO_BIN(@uuid,0),0);
+--------------------------------------+
| BIN_TO_UUID(UUID_TO_BIN(@uuid,0),0)  |
+--------------------------------------+
| 6ccd780c-baba-1026-9564-5b8c656024db |
+--------------------------------------+
mysql> SELECT BIN_TO_UUID(UUID_TO_BIN(@uuid,1),1);
+--------------------------------------+
| BIN_TO_UUID(UUID_TO_BIN(@uuid,1),1)  |
+--------------------------------------+
| 6ccd780c-baba-1026-9564-5b8c656024db |
+--------------------------------------+

如果两方向转换时使用的时间部分交换方式不相同，则无法正常恢复原来的 UUID：

mysql> SELECT BIN_TO_UUID(UUID_TO_BIN(@uuid,0),1);
+--------------------------------------+
| BIN_TO_UUID(UUID_TO_BIN(@uuid,0),1)  |
+--------------------------------------+
| baba1026-780c-6ccd-9564-5b8c656024db |
+--------------------------------------+
mysql> SELECT BIN_TO_UUID(UUID_TO_BIN(@uuid,1),0);
+--------------------------------------+
| BIN_TO_UUID(UUID_TO_BIN(@uuid,1),0)  |
+--------------------------------------+
| 1026baba-6ccd-780c-9564-5b8c656024db |
+--------------------------------------+

如果从 mysql 客户端中调用 UUID_TO_BIN()，二进制字符串将使用十六进制表法显示，具体取决于 --binary-as-hex 的值。

● VALUES(col_name)

在 NSERT ... ON DUPLICATE KEY UPDATE 语句中，可以在 UPDATE 子句中使用 VALUES(col_name) 函数引用语句的 INSERT 部分的列值。换句话说，UPDATE 子句中的 VALUES(col_name) 引用将要插入的 col_name 值，没有发生重复键冲突。这个函数在多行插入中特别有用。VALUES() 函数只有在 INSERT 语句的 ON DUPLICATE KEY UPDATE 子句中才有意义，否则返回 NULL。

mysql> INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
    -> ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

重要

这种用法在 MySQL 8.0.20 中已弃用，在 MySQL 的未来版本中可能会被删除。请使用行别名或行和列别名。

官方网址：

https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html

mysql

文章转载自数据库杂货铺，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的函数和运算符 - 杂项函数（3）

评论