12.21 其他功能

table12.27 其他功能

NameDescription
ANY_VALUE()禁止拒绝 ONLY_FULL_GROUP_BY 值
DEFAULT()返回 table 列的默认值
INET_ATON()返回 IP 地址的数值
INET_NTOA()从数值返回 IP 地址
INET6_ATON()返回 IPv6 地址的数值
INET6_NTOA()从数值返回 IPv6 地址
IS_IPV4()参数是否为 IPv4 地址
IS_IPV4_COMPAT()参数是否为 IPv4 兼容地址
IS_IPV4_MAPPED()参数是否为 IPv4Map 的地址
IS_IPV6()参数是否为 IPv6 地址
MASTER_POS_WAIT()阻塞直到从站读取并应用所有更新到指定位置
NAME_CONST()使列具有给定名称
SLEEP()睡觉几秒钟
UUID()返回通用唯一标识符(UUID)
UUID_SHORT()返回一个整数通用标识符
VALUES()定义在 INSERT 期间要使用的值

ANY_VALUE(arg)

启用ONLY_FULL_GROUP_BY SQL 模式时,此功能对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列中命名,也未在功能上依赖于它们。结果,每个name组中的行的address值是不确定的。有多种方法可以使 MySQL 接受查询:

  • 更改 table 以使name主键或唯一NOT NULL列。这使 MySQL 能够确定address在功能上取决于name;也就是说,addressname唯一确定。 (如果必须允许NULL作为有效的name值,则此技术不适用.)

SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

在这种情况下,MySQL 会忽略每个name组中address值的不确定性,并接受查询。如果您根本不在乎为每个组选择未聚合列的哪个值,则这可能很有用。 ANY_VALUE()不是集合函数,与SUM()COUNT()之类的函数不同。它只是起到抑制不确定性测试的作用。

如果列之间存在功能依赖关系,但 MySQL 无法确定,则ANY_VALUE()也很有用。以下查询是有效的,因为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 BY GROUP BY 的处理”

DEFAULT(col_name)

返回 table 列的默认值。如果该列没有默认值,则会导致错误。

mysql> UPDATE t SET i = DEFAULT(i)+1 WHERE id < 100;

将数字* X *格式化为'#,###,###.##'的格式,四舍五入到D *小数位,然后将结果作为字符串返回。有关详细信息,请参见第 12.7 节“字符串函数和运算符”

INET_ATON(expr)

给定 IPv4 网络地址的点分四进制 table 示形式为字符串,将返回一个整数,该整数 table 示网络字节 Sequences(大端)的地址数值。如果INET_ATON()不理解其参数,则返回NULL

mysql> SELECT INET_ATON('10.0.5.9');
        -> 167773449

对于此示例,返回值计算为 10×2563 0×2562 5×256 9.

对于简短的 IP 地址(例如'127.1'代 table'127.0.0.1'),INET_ATON()可能会或可能不会返回非NULL的结果。因此,INET_ATON() a 不应用于此类地址。

Note

要存储INET_ATON()生成的值,请使用INT UNSIGNED列而不是带有符号的INT。如果使用带符号的列,则不能正确存储与第一个八位位组大于 127 的 IP 地址相对应的值。参见第 11.1.7 节“超出范围和溢出处理”

INET_NTOA(expr)

给定网络字节 Sequences 的数字 IPv4 网络地址,将地址的点分四进制字符串 table 示形式作为连接字符集中的字符串返回。如果INET_NTOA()不理解其参数,则返回NULL

mysql> SELECT INET_NTOA(167773449);
        -> '10.0.5.9'

INET6_ATON(expr)

在给定 IPv6 或 IPv4 网络地址作为字符串的情况下,返回一个二进制字符串,该二进制字符串以网络字节 Sequences(big endian)table 示地址的数值。因为数字格式的 IPv6 地址比最大的整数类型需要更多的字节,所以此函数返回的 table 示形式具有VARBINARY数据类型:VARBINARY(16)table 示 IPv6 地址,VARBINARY(4)table 示 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()遵守有效参数的几个约束。下面的列 table 中提供了这些示例。

  • 不允许使用尾随区域 ID,例如fe80::3%1fe80::3%eth0

    • 不允许使用尾随网络掩码,如2001:45f:3:ba::/64198.51.100.0/24

    • 对于 table 示 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 兼容或 IPv4Map 的地址。

要将以数字形式 table 示为INT值的 IPv4 地址* expr *转换为以数字形式 table 示为VARBINARY值的 IPv6 地址,请使用以下 table 达式:

INET6_ATON(INET_NTOA(expr))

For example:

mysql> SELECT HEX(INET6_ATON(INET_NTOA(167773449)));
        -> '0A000509'

INET6_NTOA(expr)

给定以数字形式 table 示为二进制字符串的 IPv6 或 IPv4 网络地址,以连接字符集中的字符串形式返回地址的字符串 table 示形式。如果参数不是有效地址,则INET6_NTOA()返回NULL

INET6_NTOA()具有以下属性:

  • 它不使用 os 功能执行转换,因此输出字符串与平台无关。

    • 返回字符串的最大长度为 39(4 x 8 7)。鉴于此声明:
CREATE TABLE t AS SELECT INET6_NTOA(expr) AS c1;

结果 table 将具有以下定义:

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'

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。相反的说法不正确:在某些情况下,当IS_IPV4()返回 0 时,INET_ATON()返回非NULL

正如前面的言论所暗示的,IS_IPV4()INET_ATON()在构成有效 IPv4 地址方面要严格一些,因此它对于需要对无效值进行严格检查的应用程序可能很有用。或者,使用INET6_ATON()将 IPv4 地址转换为内部格式,并检查NULL结果(指示无效地址)。 INET6_ATON()IS_IPV4()在检查 IPv4 地址方面同样强大。

IS_IPV4_COMPAT(expr)

此函数采用以数字形式 table 示为二进制字符串的 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 部分也可以使用十六进制 table 示法 table 示。例如,198.51.100.1具有此原始十六进制值:

mysql> SELECT HEX(INET6_ATON('198.51.100.1'));
        -> 'C6336401'

::198.51.100.1以 IPv4 兼容格式 table 示,等效于::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)

此函数采用以数字形式 table 示为二进制字符串的 ipv6 地址,由INET6_ATON()返回。如果参数是有效的 IPv4Map 的 IPv6 地址,则返回 1,否则返回 0. IPv4Map 的地址的格式为::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()一样,也可以使用十六进制 table 示法 table 示 IPv4Map 地址的 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()将返回非NULL

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

此功能对于控制主/从同步非常有用。它会阻塞,直到从站已读取并应用所有更新到主日志中的指定位置为止。返回值是从站必须 await 前进到指定位置的日志事件数。如果未启动从属 SQL 线程,未初始化从属的主信息,参数不正确或发生错误,则函数返回NULL。如果已超过超时,它将返回-1。如果在MASTER_POS_WAIT()await 时从 SQL 线程停止,则该函数返回NULL。如果从站经过指定位置,则该函数立即返回。

在多线程从站上,当调用检查点操作以更新从站的状态时,该函数将 await 直到slave_checkpoint_groupslave_checkpoint_period系统变量设置的限制到期。因此,取决于系统变量的设置,该功能可能会在到达指定位置后返回一些时间。

如果指定* timeout 值,则在 timeout *秒过去之后MASTER_POS_WAIT()将停止 await。 * timeout 必须大于 0;零或负 timeout *table 示没有超时。

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

此功能对于基于语句的复制不安全。如果在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.持续时间可能有小数部分。如果参数为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时使用此功能,则会记录一条警告。

UUID()

返回根据 RFC 4122“通用唯一标识符(UUID)URN 命名空间”(http://www.ietf.org/rfc/rfc4122.txt)生成的通用唯一标识符(UUID)。

UUID 被设计为在空间和时间上全局唯一的数字。即使对两个未连接的独立设备执行了对UUID()的两次调用,它们也会产生两个不同的值。

Warning

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

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

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

    • 万一时间戳值失去单调性(例如,由于夏时制),第四个数字将保留时间唯一性。

    • 第五个数字是提供空间唯一性的 IEEE 802 节点号。如果后者不可用(例如,因为主机设备没有以太网卡,或者不知道如何在主机 os 上找到接口的硬件地址),则将替换为一个随机数。在这种情况下,不能保证空间唯一性。但是,碰撞应该非常低。

仅在 FreeBSD,Linux 和 Windows 上才考虑接口的 MAC 地址。在其他 os 上,MySQL 使用随机生成的 48 位数字。

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

此功能对于基于语句的复制不安全。如果在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

Note

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

VALUES(col_name)

插入...在重复的密钥更新上语句中,可以使用UPDATE子句中的VALUES(col_name)函数来引用该语句INSERT部分中的列值。换句话说,UPDATE子句中的VALUES(col_name)table 示在没有重复密钥冲突的情况下将要插入的* col_name *的值。此功能在多行插入中特别有用。 VALUES()函数仅在INSERT语句的ON DUPLICATE KEY UPDATE子句中有意义,否则返回NULL。参见第 13.2.5.2 节“在重复密钥更新语句上插入...”

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