12.21 辅助功能_MySQL 5.7 参考手册

扩展 MySQL 8.0 / 第 6 章向 MySQL 添加函数 / 12.21 辅助功能

12.21 辅助功能

表 12.26 辅助功能

姓名	描述
`ANY_VALUE()`	抑制 ONLY_FULL_GROUP_BY 值拒绝
`DEFAULT()`	返回表列的默认值
`INET_ATON()`	返回 IP 地址的数值
`INET_NTOA()`	从数值返回 IP 地址
`INET6_ATON()`	返回 IPv6 地址的数值
`INET6_NTOA()`	从数值返回 IPv6 地址
`IS_IPV4()`	参数是否为 IPv4 地址
`IS_IPV4_COMPAT()`	参数是否为 IPv4 兼容地址
`IS_IPV4_MAPPED()`	参数是否为 IPv4 映射地址
`IS_IPV6()`	参数是否为 IPv6 地址
`MASTER_POS_WAIT()`	阻塞直到副本已读取并将所有更新应用到指定位置
`NAME_CONST()`	使列具有给定的名称
`SLEEP()`	睡眠数秒
`UUID()`	返回通用唯一标识符 (UUID)
`UUID_SHORT()`	返回一个整数值的通用标识符
`VALUES()`	定义要在 INSERT 期间使用的值

ANY_VALUE(arg)
此功能对于启用 SQL 模式GROUP BY 时的查询很有用ONLY_FULL_GROUP_BY，适用于 MySQL 拒绝您知道有效的查询但 MySQL 无法确定的原因的情况。函数返回值和类型与其参数的返回值和类型相同，但不检查ONLY_FULL_GROUP_BY SQL模式的函数结果。
例如，如果name是非索引列，则以下查询将失败并 ONLY_FULL_GROUP_BY启用：
```
mysql> SELECT name, address, MAX(age) FROM t GROUP BY name;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP
BY clause and contains nonaggregated column 'mydb.t.address' which
is not functionally dependent on columns in GROUP BY clause; this
is incompatible with sql_mode=only_full_group_by
```
发生故障是因为address它是一个非聚合列，它既没有在 GROUP BY列中命名，也没有在功能上依赖于它们。因此，address每个组中行的值name是不确定的。有多种方法可以使 MySQL 接受查询：
- 更改表以创建name主键或唯一NOT NULL列。这使 MySQL 能够确定address 在功能上依赖于name; 也就是说，address由唯一确定 name。NULL（如果必须允许作为有效值，则此技术不适用name。）
- 用于ANY_VALUE()指代 address：
```
SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;
```
  在这种情况下，MySQL 忽略 address每个 name组内值的不确定性并接受查询。如果您根本不关心为每个组选择了哪个非聚合列的值，这可能很有用。 ANY_VALUE()不是聚合函数，不像 SUM()or 之类的函数COUNT()。它只是用来抑制非确定性测试。
- 禁用 ONLY_FULL_GROUP_BY。这相当于使用 ANY_VALUE()with ONLY_FULL_GROUP_BY enabled，如前一项所述。
ANY_VALUE()如果列之间存在功能依赖但 MySQL 无法确定它，这也是有用的。以下查询是有效的，因为 age它在功能上依赖于分组列age-1，但 MySQL 无法判断并拒绝 ONLY_FULL_GROUP_BY启用的查询：
```
SELECT age FROM t GROUP BY age-1;
```
要使 MySQL 接受查询，请使用 ANY_VALUE()：
```
SELECT ANY_VALUE(age) FROM t GROUP BY age-1;
```
ANY_VALUE()可用于在没有 GROUP BY子句的情况下引用聚合函数的查询：
```
mysql> SELECT name, MAX(age) FROM t;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression
#1 of SELECT list contains nonaggregated column 'mydb.t.name'; this
is incompatible with sql_mode=only_full_group_by
```
如果没有GROUP BY，则只有一个组，并且不确定name为该组选择哪个值。 ANY_VALUE()告诉 MySQL 接受查询：
```
SELECT ANY_VALUE(name), MAX(age) FROM t;
```
可能是因为给定数据集的某些属性，您知道选定的非聚合列在功能上有效地依赖于GROUP BY 列。例如，应用程序可能强制一列相对于另一列的唯一性。在这种情况下，使用 ANY_VALUE()有效功能依赖列可能有意义。
有关其他讨论，请参阅第 12.20.3 节，“MySQL 对 GROUP BY 的处理”。
DEFAULT(col_name)
返回表列的默认值。如果该列没有默认值，则会产生错误。
```
mysql> UPDATE t SET i = DEFAULT(i)+1 WHERE id < 100;
```
FORMAT(X,D)
将数字X格式化为类似于的格式'#,###,###.##'，四舍五入到 D小数位，并将结果作为字符串返回。有关详细信息，请参阅第 12.8 节，“字符串函数和运算符”。
INET_ATON(expr)
给定 IPv4 网络地址的点分四边形表示形式的字符串，返回一个整数，该整数表示网络字节顺序（大端）中地址的数值。如果它不理解其参数，则 INET_ATON()返回。NULL
```
mysql> SELECT INET_ATON('10.0.5.9');
        -> 167773449
```
对于此示例，返回值计算为 10×256 ³ + 0×256 ² + 5×256 + 9。
INET_ATON()可能会也可能不会返回短格式 IP 地址的非NULL结果（例如'127.1'的表示'127.0.0.1'）。因此，INET_ATON()不应将 a 用于此类地址。

笔记

要存储由生成的值 INET_ATON()，请使用已签名的INT UNSIGNED列而不是。INT如果使用带符号的列，则无法正确存储与第一个八位字节大于 127 的 IP 地址对应的值。请参阅第 11.1.7 节，“超出范围和溢出处理”。
INET_NTOA(expr)
给定网络字节顺序的数字 IPv4 网络地址，返回地址的点分四字符串表示形式作为连接字符集中的字符串。如果它不理解其参数，则 INET_NTOA()返回。NULL
```
mysql> SELECT INET_NTOA(167773449);
        -> '10.0.5.9'
```
INET6_ATON(expr)
给定一个字符串形式的 IPv6 或 IPv4 网络地址，返回一个二进制字符串，该字符串表示网络字节顺序（大端）中地址的数值。因为数字格式的 IPv6 地址需要比最大整数类型更多的字节，所以此函数返回的表示具有 VARBINARY数据类型： VARBINARY(16)用于 IPv6 地址和VARBINARY(4)用于 IPv4 地址。如果参数不是有效地址，则 INET6_ATON()返回 NULL。
以下示例用于以可打印形式 HEX()显示结果：INET6_ATON()
```
mysql> SELECT HEX(INET6_ATON('fdfe::5a55:caff:fefa:9089'));
        -> 'FDFE0000000000005A55CAFFFEFA9089'
mysql> SELECT HEX(INET6_ATON('10.0.5.9'));
        -> '0A000509'
```
INET6_ATON()观察对有效参数的几个约束。这些在下面的列表中连同示例一起给出。
- 尾随区域 ID 是不允许的，如 fe80::3%1或 fe80::3%eth0。
- 不允许使用尾随网络掩码，如 2001:45f:3:ba::/64或 198.51.100.0/24。
- 对于表示 IPv4 地址的值，仅支持无类地址。诸如此类的地址 198.51.1被拒绝。不允许使用尾随端口号，如 198.51.100.2:8080. 地址组件中不允许使用十六进制数字，例如 198.0xa0.1.2. 不支持八进制数：198.51.010.1被视为 198.51.10.1，而不是 198.51.8.1。这些 IPv4 约束也适用于具有 IPv4 地址部分的 IPv6 地址，例如 IPv4 兼容地址或 IPv4 映射地址。
要将以数字形式表示为值的 IPv4 地址转换为expr 以数字形式表示为 INT值的 IPv6 地址 VARBINARY，请使用以下表达式：
```
INET6_ATON(INET_NTOA(expr))
```
例如：
```
mysql> SELECT HEX(INET6_ATON(INET_NTOA(167773449)));
        -> '0A000509'
```
如果INET6_ATON()从mysql客户端中调用，则二进制字符串使用十六进制表示法显示，具体取决于--binary-as-hex. 有关该选项的更多信息，请参阅第 4.5.1 节，“mysql — MySQL 命令行客户端”。
INET6_NTOA(expr)
给定以数字形式表示为二进制字符串的 IPv6 或 IPv4 网络地址，返回地址的字符串表示形式作为连接字符集中的字符串。如果参数不是有效地址，则 INET6_NTOA()返回 NULL。
INET6_NTOA()具有以下属性：
- 它不使用操作系统函数来执行转换，因此输出字符串与平台无关。
- 返回字符串的最大长度为 39 (4 x 8 + 7)。鉴于此声明：
```
CREATE TABLE t AS SELECT INET6_NTOA(expr) AS c1;
```
  结果表将具有以下定义：
```
CREATE TABLE t (c1 VARCHAR(39) CHARACTER SET utf8 DEFAULT NULL);
```
- 返回字符串对 IPv6 地址使用小写字母。
```
mysql> SELECT INET6_NTOA(INET6_ATON('fdfe::5a55:caff:fefa:9089'));
        -> 'fdfe::5a55:caff:fefa:9089'
mysql> SELECT INET6_NTOA(INET6_ATON('10.0.5.9'));
        -> '10.0.5.9'

mysql> SELECT INET6_NTOA(UNHEX('FDFE0000000000005A55CAFFFEFA9089'));
        -> 'fdfe::5a55:caff:fefa:9089'
mysql> SELECT INET6_NTOA(UNHEX('0A000509'));
        -> '10.0.5.9'
```
如果INET6_NTOA()从mysql客户端中调用，则二进制字符串使用十六进制表示法显示，具体取决于--binary-as-hex. 有关该选项的更多信息，请参阅第 4.5.1 节，“mysql — MySQL 命令行客户端”。
IS_IPV4(expr)
如果参数是指定为字符串的有效 IPv4 地址，则返回 1，否则返回 0。
```
mysql> SELECT IS_IPV4('10.0.5.9'), IS_IPV4('10.0.5.256');
        -> 1, 0
```
对于给定的参数，如果 IS_IPV4()返回 1， INET_ATON()（并且 INET6_ATON()）返回一个不是的值NULL。相反的说法是不正确的：在某些情况下，返回 INET_ATON()一个不同于返回 0 的值。 NULLIS_IPV4()
正如前面的评论所暗示的， IS_IPV4()比 INET_ATON()什么构成有效的 IPv4 地址更严格，因此它可能对需要对无效值执行严格检查的应用程序很有用。或者，使用 INET6_ATON()将 IPv4 地址转换为内部形式并检查 NULL结果（指示无效地址）。与检查 IPv4 地址 INET6_ATON()同样强大。IS_IPV4()
IS_IPV4_COMPAT(expr)
此函数将以数字形式表示的 IPv6 地址作为二进制字符串，由返回 INET6_ATON()。如果参数是有效的 IPv4 兼容 IPv6 地址，则返回 1，否则返回 0。IPv4 兼容地址的格式为 . ::ipv4_address
```
mysql> SELECT IS_IPV4_COMPAT(INET6_ATON('::10.0.5.9'));
        -> 1
mysql> SELECT IS_IPV4_COMPAT(INET6_ATON('::ffff:10.0.5.9'));
        -> 0
```
IPv4 兼容地址的 IPv4 部分也可以使用十六进制表示法表示。例如， 198.51.100.1具有以下原始十六进制值：
```
mysql> SELECT HEX(INET6_ATON('198.51.100.1'));
        -> 'C6336401'
```
以 IPv4 兼容形式表示， ::198.51.100.1等同于 ::c0a8:0001或（不带前导零） ::c0a8:1
```
mysql> SELECT
    ->   IS_IPV4_COMPAT(INET6_ATON('::198.51.100.1')),
    ->   IS_IPV4_COMPAT(INET6_ATON('::c0a8:0001')),
    ->   IS_IPV4_COMPAT(INET6_ATON('::c0a8:1'));
        -> 1, 1, 1
```
IS_IPV4_MAPPED(expr)
此函数将以数字形式表示的 IPv6 地址作为二进制字符串，由返回 INET6_ATON()。如果参数是有效的 IPv4 映射的 IPv6 地址，则返回 1，否则返回 0。IPv4 映射地址的格式为 . ::ffff:ipv4_address
```
mysql> SELECT IS_IPV4_MAPPED(INET6_ATON('::10.0.5.9'));
        -> 0
mysql> SELECT IS_IPV4_MAPPED(INET6_ATON('::ffff:10.0.5.9'));
        -> 1
```
与IS_IPV4_COMPAT()IPv4 映射地址的 IPv4 部分一样，也可以使用十六进制表示法表示：
```
mysql> SELECT
    ->   IS_IPV4_MAPPED(INET6_ATON('::ffff:198.51.100.1')),
    ->   IS_IPV4_MAPPED(INET6_ATON('::ffff:c0a8:0001')),
    ->   IS_IPV4_MAPPED(INET6_ATON('::ffff:c0a8:1'));
        -> 1, 1, 1
```
IS_IPV6(expr)
如果参数是指定为字符串的有效 IPv6 地址，则返回 1，否则返回 0。此函数不将 IPv4 地址视为有效的 IPv6 地址。
```
mysql> SELECT IS_IPV6('10.0.5.9'), IS_IPV6('::1');
        -> 0, 1
```
对于给定的参数，如果 IS_IPV6()返回 1，则INET6_ATON()返回一个值 tht si not NULL。
MASTER_POS_WAIT(log_name,log_pos[,timeout][,channel])
此功能对于控制源/副本同步很有用。它会阻塞，直到副本已读取并将所有更新应用到源日志中的指定位置。返回值是副本必须等待才能前进到指定位置的日志事件数。NULL如果副本 SQL 线程未启动、副本的源信息未初始化、参数不正确或发生错误，则该函数返回。-1如果超过超时，它会返回。如果副本 SQL 线程在 MASTER_POS_WAIT()等待时停止，则函数返回NULL. 如果副本超过指定位置，该函数将立即返回。
在多线程副本上，该函数会一直等到 slave_checkpoint_groupor slave_checkpoint_period 系统变量设置的限制到期，此时调用检查点操作来更新副本的状态。根据系统变量的设置，该函数可能会在到达指定位置后一段时间后返回。
如果timeout指定了值，则在秒数过去后MASTER_POS_WAIT()停止等待。必须大于 0；零或负数表示没有超时。 timeouttimeouttimeout
可选channel值使您能够命名该函数适用于哪个复制通道。有关详细信息，请参阅第 16.2.2 节，“复制通道”。
此函数对于基于语句的复制是不安全的。如果您在 binlog_format设置为时使用此功能，则会记录一条警告STATEMENT。
NAME_CONST(name,value)
返回给定的值。当用于生成结果集列时，NAME_CONST()使该列具有给定的名称。参数应该是常量。
```
mysql> SELECT NAME_CONST('myname', 14);
+--------+
| myname |
+--------+
|     14 |
+--------+
```
此函数仅供内部使用。服务器在从包含对本地程序变量的引用的存储程序中编写语句时使用它，如第 23.7 节，“存储程序二进制日志记录”中所述。您可能会在mysqlbinlog的输出中看到此函数。
对于您的应用程序，您可以获得与刚才显示的示例完全相同的结果，方法是使用简单的别名，如下所示：
```
mysql> SELECT 14 AS myname;
+--------+
| myname |
+--------+
|     14 |
+--------+
1 row in set (0.00 sec)
```
有关列别名的更多信息，请参阅第 13.2.9 节，“SELECT 语句” 。
SLEEP(duration)
休眠（暂停）参数给定的秒数 duration，然后返回 0。持续时间可能有小数部分。如果参数为 NULLor 负数，则SLEEP()在严格 SQL 模式下产生警告或错误。
当 sleep 正常返回时（没有中断），它返回 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。
UUID()
返回根据 RFC 4122 “通用唯一标识符 (UUID) URN 命名空间” ( http://www.ietf.org/rfc/rfc4122.txt ) 生成的通用唯一标识符 (UUID )。
UUID 被设计为在空间和时间上全球唯一的数字。两次调用 UUID()预计会生成两个不同的值，即使这些调用是在两个未相互连接的独立设备上执行的。

警告

尽管UUID()值旨在是唯一的，但它们不一定是不可猜测或不可预测的。如果需要不可预测性，UUID 值应该以其他方式生成。

UUID()返回一个符合 UUID 版本 1 的值，如 RFC 4122 中所述。该值是一个 128 位数字，表示为utf8 五个十六进制数字的字符串， aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee 格式如下：
- 前三个数字是从时间戳的低、中和高部分生成的。高位部分还包括 UUID 版本号。
- 第四个数字保留时间唯一性，以防时间戳值失去单调性（例如，由于夏令时）。
- 第五个数字是提供空间唯一性的 IEEE 802 节点号。如果后者不可用（例如，因为主机设备没有以太网卡，或者不知道如何在主机操作系统上找到接口的硬件地址），则用一个随机数代替。在这种情况下，无法保证空间唯一性。尽管如此，碰撞的概率应该 非常低。
  仅在 FreeBSD、Linux 和 Windows 上考虑接口的 MAC 地址。在其他操作系统上，MySQL 使用随机生成的 48 位数字。
```
mysql> SELECT UUID();
        -> '6ccd780c-baba-1026-9564-5b8c656024db'
```
此函数对于基于语句的复制是不安全的。如果您在 binlog_format设置为时使用此功能，则会记录一条警告STATEMENT。
UUID_SHORT()
返回一个“短”通用标识符作为 64 位无符号整数。返回的值 UUID_SHORT()不同于函数返回的字符串格式的 128 位标识符， UUID()并且具有不同的唯一性属性。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()不适用于基于语句的复制。
VALUES(col_name)
在 INSERT ... ON DUPLICATE KEY UPDATE语句中，您可以使用子句中的函数来引用语句部分中的列值。换句话说，在子句中指的是将被插入的值，没有重复键冲突发生。此功能在多行插入中特别有用。该函数仅在语句的子句中有意义，否则返回。参见第 13.2.5.2 节，“INSERT ... ON DUPLICATE KEY UPDATE 语句”。 VALUES(col_name)UPDATEINSERTVALUES(col_name)UPDATEcol_nameVALUES()ON DUPLICATE KEY UPDATEINSERTNULL
```
mysql> INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
    -> ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
```