ANY_VALUE(arg)ANY_VALUE(arg)

当启用 ONLY_FULL_GROUP_BY SQL 模式时，对于 MySQL 拒绝执行但你知道在 MySQL 无法判断其有效性的情况下有效的查询，此函数对 GROUP BY 查询非常有用。函数的返回值和类型与其参数的返回值和类型相同，但函数结果不会受到 ONLY_FULL_GROUP_BY SQL 模式的检查。This function is useful for GROUP BY queries when the ONLY_FULL_GROUP_BY SQL mode is enabled, for cases when MySQL rejects a query that you know is valid for reasons that MySQL cannot determine. The function return value and type are the same as the return value and type of its argument, but the function result is not checked for the ONLY_FULL_GROUP_BY SQL mode.

例如，如果 name 是非索引列，则在启用 ONLY_FULL_GROUP_BY 时，以下查询将失败：For example, if name is a nonindexed column, the following query fails with ONLY_FULL_GROUP_BY enabled:

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 接受该查询：The failure occurs because address is a nonaggregated column that is neither named among GROUP BY columns nor functionally dependent on them. As a result, the address value for rows within each name group is nondeterministic. There are multiple ways to cause MySQL to accept the query:

ANY_VALUE() 也很有用，当列之间存在函数依赖但 MySQL 无法确定时。以下查询是有效的，因为 age 函数上依赖于分组列 age-1，但 MySQL 无法识别这一点，并在启用 ONLY_FULL_GROUP_BY 时拒绝该查询：ANY_VALUE() is also useful if functional dependence exists between columns but MySQL cannot determine it. The following query is valid because age is functionally dependent on the grouping column age-1, but MySQL cannot tell that and rejects the query with ONLY_FULL_GROUP_BY enabled:

SELECT age FROM t GROUP BY age-1;

为了让 MySQL 接受该查询，请使用 ANY_VALUE()：To cause MySQL to accept the query, use ANY_VALUE():

SELECT ANY_VALUE(age) FROM t GROUP BY age-1;

ANY_VALUE() 可以用于在没有 GROUP BY 子句的情况下引用聚合函数的查询：ANY_VALUE() can be used for queries that refer to aggregate functions in the absence of a GROUP BY clause:

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 接受该查询：Without GROUP BY, there is a single group and it is nondeterministic which name value to choose for the group. ANY_VALUE() tells MySQL to accept the query:

SELECT ANY_VALUE(name), MAX(age) FROM t;

可能由于给定数据集的某些特性，你知道所选的非聚合列实际上在功能上依赖于 GROUP BY 列。例如，一个应用程序可能会强制对另一列保持某一列的唯一性。在这种情况下，对于实际上功能上依赖的列使用 ANY_VALUE() 可能是有意义的。It may be that, due to some property of a given data set, you know that a selected nonaggregated column is effectively functionally dependent on a GROUP BY column. For example, an application may enforce uniqueness of one column with respect to another. In this case, using ANY_VALUE() for the effectively functionally dependent column may make sense.

欲了解更多讨论，请参见 第 14.19.3 节，“MySQL 处理 GROUP BY”。For additional discussion, see Section 14.19.3, “MySQL Handling of GROUP BY”.

BIN_TO_UUID(binary_uuid), BIN_TO_UUID(binary_uuid, swap_flag)

BIN_TO_UUID() 是 UUID_TO_BIN() 的逆函数。它将二进制 UUID 转换为字符串 UUID 并返回结果。二进制值应该是 VARBINARY(16) 类型的 UUID。返回值是由五个以连字符分隔的十六进制数字组成的字符串。（有关此格式的详细信息，请参阅 UUID() 函数说明。）如果 UUID 参数为 NULL，则返回值为 NULL。如果任何参数无效，将会发生错误。BIN_TO_UUID() is the inverse of UUID_TO_BIN(). It converts a binary UUID to a string UUID and returns the result. The binary value should be a UUID as a VARBINARY(16) value. The return value is a string of five hexadecimal numbers separated by dashes. (For details about this format, see the UUID() function description.) If the UUID argument is NULL, the return value is NULL. If any argument is invalid, an error occurs.

BIN_TO_UUID() 接受一个或两个参数：BIN_TO_UUID() takes one or two arguments:

有关使用示例和时间部分交换的信息，请参见 UUID_TO_BIN() 函数说明。For usage examples and information about time-part swapping, see the UUID_TO_BIN() function description.

DEFAULT(col_name)

返回表列的默认值。如果该列没有默认值，将会产生错误。Returns the default value for a table column. An error results if the column has no default value.

使用 DEFAULT(col_name) 为命名列指定默认值仅允许用于具有字面默认值的列，而不适用于具有表达式默认值的列。The use of DEFAULT(col_name) to specify the default value for a named column is permitted only for columns that have a literal default value, not for columns that have an expression default value.

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

FORMAT(X,D)

将数字X格式化为类似'#,###,###.##'的格式，四舍五入到D位小数，并将结果作为字符串返回。详情请参见第 14.8 节，“字符串函数和操作符”。Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string. For details, see Section 14.8, “String Functions and Operators”.

GROUPING(expr [, expr] ...)

对于包含 WITH ROLLUP 修饰符的 GROUP BY 查询，ROLLUP 操作会生成超级汇总输出行，其中 NULL 表示所有值的集合。GROUPING() 函数使您能够区分超级汇总行的 NULL 值与常规分组行中的 NULL 值。For GROUP BY queries that include a WITH ROLLUP modifier, the ROLLUP operation produces super-aggregate output rows where NULL represents the set of all values. The GROUPING() function enables you to distinguish NULL values for super-aggregate rows from NULL values in regular grouped rows.

GROUPING() 可以在 select 列表、HAVING 子句和 ORDER BY 子句中使用。GROUPING() is permitted in the select list, HAVING clause, and ORDER BY clause.

GROUPING() 的每个参数必须是一个在 GROUP BY 子句中完全匹配的表达式。该表达式不能是位置说明符。对于每个表达式，如果当前行中的表达式值是表示超级聚合值的 NULL，GROUPING() 将返回 1。否则，GROUPING() 将返回 0，表示表达式值在普通结果行中是 NULL 或者不是 NULL。Each argument to GROUPING() must be an expression that exactly matches an expression in the GROUP BY clause. The expression cannot be a positional specifier. For each expression, GROUPING() produces 1 if the expression value in the current row is a NULL representing a super-aggregate value. Otherwise, GROUPING() produces 0, indicating that the expression value is a NULL for a regular result row or is not NULL.

假设表 t1 包含这些行，其中 NULL 表示类似 “其他” 或 “未知” 的内容：Suppose that table t1 contains these rows, where NULL indicates something like “other” or “unknown”:

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 |
+------+-------+----------+

没有 WITH ROLLUP 的表格汇总如下：A summary of the table without WITH ROLLUP looks like this:

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

结果包含 NULL 值，但这些值并不表示超级汇总行，因为查询没有包含 WITH ROLLUP。The result contains NULL values, but those do not represent super-aggregate rows because the query does not include WITH ROLLUP.

添加 WITH ROLLUP 会生成包含额外 NULL 值的超级汇总行。然而，如果不将此结果与之前的结果进行比较，很难分辨哪些 NULL 值出现在超级汇总行中，哪些出现在普通分组行中：Adding WITH ROLLUP produces super-aggregate summary rows containing additional NULL values. However, without comparing this result to the previous one, it is not easy to see which NULL values occur in super-aggregate rows and which occur in regular grouped rows:

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 |
+------+-------+----------+

为了区分NULL超级聚合行中的值和普通分组行中的值，请使用GROUPING()，它仅对超级聚合NULL值返回 1：To distinguish NULL values in super-aggregate rows from those in regular grouped rows, use GROUPING(), which returns 1 only for super-aggregate NULL values:

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

GROUPING() 的常见用途：Common uses for GROUPING():

GROUPING() 允许多个表达式参数。在这种情况下，GROUPING() 返回值表示由每个表达式结果组合而成的位掩码，其中最低位对应最右边表达式的结果。例如，对于三个表达式参数，GROUPING(expr1, expr2, expr3) 的计算如下：GROUPING() permits multiple expression arguments. In this case, the GROUPING() return value represents a bitmask combined from the results for each expression, where the lowest-order bit corresponds to the result for the rightmost expression. For example, with three expression arguments, GROUPING(expr1, expr2, expr3) is evaluated like this:

  result for GROUPING(expr3)
+ result for GROUPING(expr2) << 1
+ result for GROUPING(expr1) << 2

下面的查询显示了单个参数的 GROUPING() 结果如何组合在多参数调用中以生成位掩码值：The following query shows how GROUPING() results for single arguments combine for a multiple-argument call to produce a bitmask value:

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

对于多个表达式参数，如果任何表达式表示超级聚合值，则 GROUPING() 返回值为非零。多参数 GROUPING() 语法因此提供了一种更简单的方式来编写之前仅返回超级聚合行的查询，通过使用一次多参数 GROUPING() 调用，而不是多次单参数调用：With multiple expression arguments, the GROUPING() return value is nonzero if any expression represents a super-aggregate value. Multiple-argument GROUPING() syntax thus provides a simpler way to write the earlier query that returned only super-aggregate rows, by using a single multiple-argument GROUPING() call rather than multiple single-argument calls:

mysql> SELECT name, size, SUM(quantity) AS quantity
       FROM t1
       GROUP BY name, size WITH ROLLUP
       HAVING GROUPING(name, size) <> 0;
+------+------+----------+
| name | size | quantity |
+------+------+----------+
| ball | NULL |       35 |
| hoop | NULL |       23 |
| NULL | NULL |       58 |
+------+------+----------+

使用 GROUPING() 受以下限制：Use of GROUPING() is subject to these limitations:

有关 WITH ROLLUP 和 GROUPING() 的更多信息，请参见 第 14.19.2 节，“GROUP BY 修饰符”。For more information about WITH ROLLUP and GROUPING(), see Section 14.19.2, “GROUP BY Modifiers”.

INET_ATON(expr)

给定一个以字符串形式表示的 IPv4 网络地址的点分十进制表示，返回一个整数，表示网络字节顺序（大端）中的地址数值。INET_ATON() 如果无法理解其参数，或者expr为NULL，则返回NULL。Given the dotted-quad representation of an IPv4 network address as a string, returns an integer that represents the numeric value of the address in network byte order (big endian). INET_ATON() returns NULL if it does not understand its argument, or if expr is NULL.

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

在此示例中，返回值计算为 10×256³ + 0×256² + 5×256 + 9。For this example, the return value is calculated as 10×256³ + 0×256² + 5×256 + 9.

INET_ATON() 可能会或可能不会为短格式 IP 地址（例如将 '127.1' 作为 '127.0.0.1' 的表示）返回非 NULL 的结果。因此，不应对这些地址使用 INET_ATON()。INET_ATON() may or may not return a non-NULL result for short-form IP addresses (such as '127.1' as a representation of '127.0.0.1'). Because of this, INET_ATON()a should not be used for such addresses.

INET_NTOA(expr)

给定一个网络字节顺序的数字 IPv4 网络地址，返回以连接字符集表示的点分十进制字符串形式的地址。INET_NTOA() 如果无法理解其参数，则返回 NULL。Given a numeric IPv4 network address in network byte order, returns the dotted-quad string representation of the address as a string in the connection character set. INET_NTOA() returns NULL if it does not understand its argument.

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

INET6_ATON(expr)

给定一个 IPv6 或 IPv4 网络地址字符串，返回一个二进制字符串，该字符串表示网络字节顺序（大端）中的地址数值。由于数字格式的 IPv6 地址所需的字节比最大整数类型更多，本函数返回的表示使用 VARBINARY 数据类型：IPv6 地址使用 VARBINARY(16)，IPv4 地址使用 VARBINARY(4)。如果参数不是有效地址，或者为 NULL，INET6_ATON() 将返回 NULL。Given an IPv6 or IPv4 network address as a string, returns a binary string that represents the numeric value of the address in network byte order (big endian). Because numeric-format IPv6 addresses require more bytes than the largest integer type, the representation returned by this function has the VARBINARY data type: VARBINARY(16) for IPv6 addresses and VARBINARY(4) for IPv4 addresses. If the argument is not a valid address, or if it is NULL, INET6_ATON() returns NULL.

以下示例使用 HEX() 以可打印形式显示 INET6_ATON() 的结果：The following examples use HEX() to display the INET6_ATON() result in printable form:

mysql> SELECT HEX(INET6_ATON('fdfe::5a55:caff:fefa:9089'));
        -> 'FDFE0000000000005A55CAFFFEFA9089'
mysql> SELECT HEX(INET6_ATON('10.0.5.9'));
        -> '0A000509'

INET6_ATON() 对有效参数有若干限制。以下列表列出了这些限制及示例。INET6_ATON() observes several constraints on valid arguments. These are given in the following list along with examples.

要将以数字形式表示的 IPv4 地址 expr（作为 INT 值）转换为以数字形式表示的 IPv6 地址（作为 VARBINARY 值），使用以下表达式：To convert an IPv4 address expr represented in numeric form as an INT value to an IPv6 address represented in numeric form as a VARBINARY value, use this expression:

INET6_ATON(INET_NTOA(expr))

例如：For example:

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

如果从 mysql 客户端调用 INET6_ATON()，则二进制字符串将根据 --binary-as-hex 的值以十六进制表示。有关该选项的更多信息，请参阅 第 6.5.1 节，“mysql — MySQL 命令行客户端”。If INET6_ATON() is invoked from within the mysql client, binary strings display using hexadecimal notation, depending on the value of the --binary-as-hex. For more information about that option, see Section 6.5.1, “mysql — The MySQL Command-Line Client”.

INET6_NTOA(expr)

给定一个以二进制字符串表示的 IPv6 或 IPv4 网络地址，返回该地址在连接字符集中的字符串表示。如果参数不是有效地址，或者为 NULL，INET6_NTOA() 将返回 NULL。Given an IPv6 or IPv4 network address represented in numeric form as a binary string, returns the string representation of the address as a string in the connection character set. If the argument is not a valid address, or if it is NULL, INET6_NTOA() returns NULL.

INET6_NTOA() 具有如下属性：INET6_NTOA() has these properties:

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'

如果在 mysql 客户端中调用 INET6_NTOA()，二进制字符串将根据 --binary-as-hex 的值以十六进制表示。有关此选项的更多信息，请参阅 第 6.5.1 节，“mysql — MySQL 命令行客户端”。If INET6_NTOA() is invoked from within the mysql client, binary strings display using hexadecimal notation, depending on the value of the --binary-as-hex. For more information about that option, see Section 6.5.1, “mysql — The MySQL Command-Line Client”.

IS_IPV4(expr)

如果参数是以字符串形式指定的有效 IPv4 地址，则返回 1，否则返回 0。如果expr为NULL，则返回NULL。Returns 1 if the argument is a valid IPv4 address specified as a string, 0 otherwise. Returns NULL if expr is NULL.

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。For a given argument, if IS_IPV4() returns 1, INET_ATON() (and INET6_ATON()) returns non-NULL. The converse statement is not true: In some cases, INET_ATON() returns non-NULL when IS_IPV4() returns 0.

如前述评论所示，IS_IPV4() 对什么构成有效的 IPv4 地址比 INET_ATON() 更严格，因此它可能对需要对无效值进行严格检查的应用程序有用。或者，可以使用 INET6_ATON() 将 IPv4 地址转换为内部形式，并检查 NULL 结果（表示地址无效）。INET6_ATON() 对检查 IPv4 地址的严格性与 IS_IPV4() 相同。As implied by the preceding remarks, IS_IPV4() is more strict than INET_ATON() about what constitutes a valid IPv4 address, so it may be useful for applications that need to perform strong checks against invalid values. Alternatively, use INET6_ATON() to convert IPv4 addresses to internal form and check for a NULL result (which indicates an invalid address). INET6_ATON() is equally strong as IS_IPV4() about checking IPv4 addresses.

IS_IPV4_COMPAT(expr)

此函数接受以二进制字符串形式表示的 IPv6 地址，即 INET6_ATON() 返回的值。如果参数是有效的 IPv4 兼容 IPv6 地址，则返回 1，否则返回 0（除非 expr 为 NULL，此时函数返回 NULL）。IPv4 兼容地址的形式为 ::ipv4_address。This function takes an IPv6 address represented in numeric form as a binary string, as returned by INET6_ATON(). It returns 1 if the argument is a valid IPv4-compatible IPv6 address, 0 otherwise (unless expr is NULL, in which case the function returns NULL). IPv4-compatible addresses have the form ::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 的原始十六进制值为：The IPv4 part of an IPv4-compatible address can also be represented using hexadecimal notation. For example, 198.51.100.1 has this raw hexadecimal value:

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

以 IPv4 兼容形式表示，::198.51.100.1 等价于 ::c0a8:0001 或（去掉前导零）::c0a8:1Expressed in IPv4-compatible form, ::198.51.100.1 is equivalent to ::c0a8:0001 or (without leading zeros) ::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，除非 expr 为 NULL，此时函数返回 NULL。IPv4 映射地址的形式为 ::ffff:ipv4_address。This function takes an IPv6 address represented in numeric form as a binary string, as returned by INET6_ATON(). It returns 1 if the argument is a valid IPv4-mapped IPv6 address, 0 otherwise, unless expr is NULL, in which case the function returns NULL. IPv4-mapped addresses have the form ::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 部分也可以使用十六进制表示法：As with IS_IPV4_COMPAT() the IPv4 part of an IPv4-mapped address can also be represented using hexadecimal notation:

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，除非 expr 为 NULL，此时函数返回 NULL。此函数不将 IPv4 地址视为有效的 IPv6 地址。Returns 1 if the argument is a valid IPv6 address specified as a string, 0 otherwise, unless expr is NULL, in which case the function returns NULL. This function does not consider IPv4 addresses to be valid IPv6 addresses.

mysql> SELECT IS_IPV6('10.0.5.9'), IS_IPV6('::1');
        -> 0, 1

对于给定的参数，如果 IS_IPV6() 返回 1，则 INET6_ATON() 返回非 NULL。For a given argument, if IS_IPV6() returns 1, INET6_ATON() returns non-NULL.

IS_UUID(string_uuid)

如果参数是有效的字符串格式 UUID，则返回 1；如果参数不是有效的 UUID，则返回 0；如果参数是 NULL，则返回 NULL。Returns 1 if the argument is a valid string-format UUID, 0 if the argument is not a valid UUID, and NULL if the argument is NULL.

“有效” 意味着该值的格式可以被解析。也就是说，它具有正确的长度，并且只包含允许的字符（十六进制数字，字母大小写不限，并且可选的短横线和大括号）。这种格式是最常见的：“Valid” means that the value is in a format that can be parsed. That is, it has the correct length and contains only the permitted characters (hexadecimal digits in any lettercase and, optionally, dashes and curly braces). This format is most common:

aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee

其他这些格式也是允许的：These other formats are also permitted:

aaaaaaaabbbbccccddddeeeeeeeeeeee
{aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee}

有关值中各字段的含义，请参阅 UUID() 函数描述。For the meanings of fields within the value, see the UUID() function description.

mysql> SELECT IS_UUID('6ccd780c-baba-1026-9564-5b8c656024db');
+-------------------------------------------------+
| IS_UUID('6ccd780c-baba-1026-9564-5b8c656024db') |
+-------------------------------------------------+
|                                               1 |
+-------------------------------------------------+
mysql> SELECT IS_UUID('6CCD780C-BABA-1026-9564-5B8C656024DB');
+-------------------------------------------------+
| IS_UUID('6CCD780C-BABA-1026-9564-5B8C656024DB') |
+-------------------------------------------------+
|                                               1 |
+-------------------------------------------------+
mysql> SELECT IS_UUID('6ccd780cbaba102695645b8c656024db');
+---------------------------------------------+
| IS_UUID('6ccd780cbaba102695645b8c656024db') |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+
mysql> SELECT IS_UUID('{6ccd780c-baba-1026-9564-5b8c656024db}');
+---------------------------------------------------+
| IS_UUID('{6ccd780c-baba-1026-9564-5b8c656024db}') |
+---------------------------------------------------+
|                                                 1 |
+---------------------------------------------------+
mysql> SELECT IS_UUID('6ccd780c-baba-1026-9564-5b8c6560');
+---------------------------------------------+
| IS_UUID('6ccd780c-baba-1026-9564-5b8c6560') |
+---------------------------------------------+
|                                           0 |
+---------------------------------------------+
mysql> SELECT IS_UUID(RAND());
+-----------------+
| IS_UUID(RAND()) |
+-----------------+
|               0 |
+-----------------+

NAME_CONST(name,value)

返回给定的值。当用于生成结果集列时，NAME_CONST() 会使该列具有给定的名称。参数应为常量。Returns the given value. When used to produce a result set column, NAME_CONST() causes the column to have the given name. The arguments should be constants.

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

此函数仅供内部使用。当服务器在从包含对本地程序变量的引用的存储程序中写入语句时，会使用它，如第 27.7 节，“存储程序二进制日志”所述。你可能会在mysqlbinlog的输出中看到此函数。This function is for internal use only. The server uses it when writing statements from stored programs that contain references to local program variables, as described in Section 27.7, “Stored Program Binary Logging”. You might see this function in the output from mysqlbinlog.

对于你的应用程序，你可以通过使用简单的别名，就像这样，获得与刚才示例中完全相同的结果：For your applications, you can obtain exactly the same result as in the example just shown by using simple aliasing, like this:

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

有关列别名的更多信息，请参见第 15.2.13 节，“SELECT 语句”。See Section 15.2.13, “SELECT Statement”, for more information about column aliases.

SLEEP(duration)

休眠（暂停）指定的秒数，由duration参数给出，然后返回 0。持续时间可以有小数部分。如果参数为NULL或负数，SLEEP()会产生警告，或者在严格 SQL 模式下产生错误。Sleeps (pauses) for the number of seconds given by the duration argument, then returns 0. The duration may have a fractional part. If the argument is NULL or negative, SLEEP() produces a warning, or an error in strict SQL mode.

当睡眠正常恢复（没有中断）时，返回 0：When sleep returns normally (without interruption), it returns 0:

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

当 SLEEP() 是查询中唯一被调用的函数且查询被中断时，它会返回 1，而查询本身不会报错。无论查询是被终止还是超时，这都是成立的：When SLEEP() is the only thing invoked by a query that is interrupted, it returns 1 and the query itself returns no error. This is true whether the query is killed or times out:

当SLEEP()仅是被中断查询的一部分时，查询将返回错误：When SLEEP() is only part of a query that is interrupted, the query returns an error:

该函数在基于语句的复制中不安全。如果在 binlog_format 设置为 STATEMENT 时使用此函数，将记录一个警告。This function is unsafe for statement-based replication. A warning is logged if you use this function when binlog_format is set to STATEMENT.

UUID()

返回根据 RFC 4122 生成的通用唯一标识符（UUID），“通用唯一标识符 (UUID) URN 命名空间” (http://www.ietf.org/rfc/rfc4122.txt)。Returns a Universal Unique Identifier (UUID) generated according to RFC 4122, “A Universally Unique IDentifier (UUID) URN Namespace” (http://www.ietf.org/rfc/rfc4122.txt).

UUID 被设计为在空间和时间上全局唯一的数字。即使这两个调用分别在两个不相连的设备上进行，对UUID()的两次调用也预计会生成两个不同的值。A UUID is designed as a number that is globally unique in space and time. Two calls to UUID() are expected to generate two different values, even if these calls are performed on two separate devices not connected to each other.

UUID() 返回符合 RFC 4122 中描述的 UUID 版本 1 的值。该值是一个 128 位数字，表示为 utf8mb3 字符串，格式为五个十六进制数字的 aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee：UUID() returns a value that conforms to UUID version 1 as described in RFC 4122. The value is a 128-bit number represented as a utf8mb3 string of five hexadecimal numbers in aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee format:

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

要在字符串和二进制 UUID 值之间转换，请使用 UUID_TO_BIN() 和 BIN_TO_UUID() 函数。要检查字符串是否为有效的 UUID 值，请使用 IS_UUID() 函数。To convert between string and binary UUID values, use the UUID_TO_BIN() and BIN_TO_UUID() functions. To check whether a string is a valid UUID value, use the IS_UUID() function.

该函数在基于语句的复制中不安全。如果在 binlog_format 设置为 STATEMENT 时使用此函数，将记录一个警告。This function is unsafe for statement-based replication. A warning is logged if you use this function when binlog_format is set to STATEMENT.

UUID_SHORT()

返回一个 64 位无符号整数形式的“短”通用标识符。UUID_SHORT() 返回的值不同于 UUID() 函数返回的字符串格式的 128 位标识符，并且具有不同的唯一性特性。如果满足以下条件，UUID_SHORT() 的值保证是唯一的：Returns a “short” universal identifier as a 64-bit unsigned integer. Values returned by UUID_SHORT() differ from the string-format 128-bit identifiers returned by the UUID() function and have different uniqueness properties. The value of UUID_SHORT() is guaranteed to be unique if the following conditions hold:

UUID_SHORT() 的返回值构造方式如下：The UUID_SHORT() return value is constructed this way:

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

mysql> SELECT UUID_SHORT();
        -> 92395783831158784

UUID_TO_BIN(string_uuid), UUID_TO_BIN(string_uuid, swap_flag)

将字符串 UUID 转换为二进制 UUID 并返回结果。（IS_UUID() 函数描述列出了允许的字符串 UUID 格式。）返回的二进制 UUID 是一个 VARBINARY(16) 值。如果 UUID 参数为 NULL，则返回值为 NULL。如果任何参数无效，将发生错误。Converts a string UUID to a binary UUID and returns the result. (The IS_UUID() function description lists the permitted string UUID formats.) The return binary UUID is a VARBINARY(16) value. If the UUID argument is NULL, the return value is NULL. If any argument is invalid, an error occurs.

UUID_TO_BIN() 接受一个或两个参数：UUID_TO_BIN() takes one or two arguments:

时间部分交换假设使用的是 UUID 版本 1 的值，例如通过 UUID() 函数生成的值。对于以其他方式生成且不遵循版本 1 格式的 UUID 值，时间部分交换没有任何好处。有关版本 1 格式的详细信息，请参见 UUID() 函数说明。Time-part swapping assumes the use of UUID version 1 values, such as are generated by the UUID() function. For UUID values produced by other means that do not follow version 1 format, time-part swapping provides no benefit. For details about version 1 format, see the UUID() function description.

假设您有以下字符串 UUID 值：Suppose that you have the following string UUID value:

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

要将字符串 UUID 转换为二进制（无论是否交换时间部分），请使用 UUID_TO_BIN()：To convert the string UUID to binary with or without time-part swapping, use 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()。如果你通过调用 UUID_TO_BIN() 并传入第二个参数为 1 来交换时间部分生成二进制 UUID，则在将二进制 UUID 转换回字符串 UUID 时，也应向 BIN_TO_UUID() 传入第二个参数为 1 来取消交换时间部分：To convert a binary UUID returned by UUID_TO_BIN() to a string UUID, use BIN_TO_UUID(). If you produce a binary UUID by calling UUID_TO_BIN() with a second argument of 1 to swap time parts, you should also pass a second argument of 1 to BIN_TO_UUID() to unswap the time parts when converting the binary UUID back to a string 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：If the use of time-part swapping is not the same for the conversion in both directions, the original UUID is not recovered properly:

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 的值以十六进制显示。有关该选项的更多信息，请参见 第 6.5.1 节，“mysql — MySQL 命令行客户端”。If UUID_TO_BIN() is invoked from within the mysql client, binary strings display using hexadecimal notation, depending on the value of the --binary-as-hex. For more information about that option, see Section 6.5.1, “mysql — The MySQL Command-Line Client”.

VALUES(col_name)

在 INSERT ... ON DUPLICATE KEY UPDATE 语句中，你可以在 UPDATE 子句中使用 VALUES(col_name) 函数来引用语句中 INSERT 部分的列值。换句话说，在 UPDATE 子句中的 VALUES(col_name) 指的是如果不存在重复键冲突，将要插入的 col_name 的值。此函数在多行插入中特别有用。VALUES() 函数仅在 INSERT 语句的 ON DUPLICATE KEY UPDATE 子句中有意义，否则返回 NULL。参见 第 15.2.7.2 节，“INSERT ... ON DUPLICATE KEY UPDATE 语句”。In an INSERT ... ON DUPLICATE KEY UPDATE statement, you can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the statement. In other words, VALUES(col_name) in the UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts. The VALUES() function is meaningful only in the ON DUPLICATE KEY UPDATE clause of INSERT statements and returns NULL otherwise. See Section 15.2.7.2, “INSERT ... ON DUPLICATE KEY UPDATE Statement”.

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