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

数据库杂货铺 2021-10-06

290

杂项函数

名称	介绍	引入	放弃
ANY_VALUE()	抑制 ONLY_FULL_GROUP_BY 值拒绝
BIN_TO_UUID()	转换二进制UUID为字符串
DEFAULT()	返回表列的默认值
GROUPING()	区分超级聚合的 ROLLUP 行和常规行
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地址
IS_UUID()	参数是否为有效的 UUID
MASTER_POS_WAIT()	阻止直到副本已经读取并应用了截止到指定位置的所有更新		8.0.26
NAME_CONST()	使列具有指定的名称
SLEEP()	休眠指定秒数
SOURCE_POS_WAIT()	阻止直到副本已经读取并应用到了指定位置的所有更新	8.0.26
UUID()	返回通用唯一标识符(UUID)
UUID_SHORT()	返回整数值的通用标识符
UUID_TO_BIN()	转换字符串UUID为二进制
VALUES()	定义在INSERT期间使用的值

● ANY_VALUE(arg)

当启用了 ONLY_FULL_GROUP_BY SQL 模式时，MySQL 拒绝一个查询，您知道是有效的查询，而 MySQL 无法确定是有效的，这时函数对于 GROUP BY 查询很有用。函数的返回值和类型与它的参数的返回值和类型相同，但是在 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 接受查询：

■ 修改表，使 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 忽略每个 name 组中 address 值的不确定性，并接受查询。如果不关心为每个组选择的非聚合列的值，那么这可能很有用。ANY_VALUE() 不是一个聚合函数，不像 SUM() 或 COUNT() 这样的函数。它只是用来抑制对不确定性的测试。

■ 禁用 ONLY_FULL_GROUP_BY。这相当于在启用了 ONLY_FULL_GROUP_BY 的情况下使用 ANY_VALUE()，如上一项所述。

如果在列之间存在功能依赖但 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() 是有意义的。

● BIN_TO_UUID(binary_uuid), BIN_TO_UUID(binary_uuid, swap_flag)

BIN_TO_UUID() 是 UUID_TO_BIN() 的逆函数。它将二进制 UUID 转换为字符串 UUID 并返回结果。二进制值应该是 VARBINARY(16) 值的 UUID。返回值是一个由破折号分隔的5组十六进制数组成的 utf8 字符串。(关于该格式请参见 UUID() 函数描述。)如果 UUID 参数为 NULL，则返回值为 NULL。如果任何参数无效，则会发生错误。

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

■ 单参数形式接受二进制 UUID 值。假设 UUID 值的时间低和时间高部分没有交换（关于UUID表示的值，请自行查询相关资料）。字符串结果与二进制参数的顺序相同。

■ 双参数形式接受二进制 UUID 值和交换标志值：

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

○ 如果 swap_flag 为 1，则假设 UUID 值的时间低和时间高部分交换。在结果值中这些部分被交换回它们的原始位置。

关于时间部分交换的使用示例和信息，请参见 UUID_TO_BIN() 函数描述。

● DEFAULT(col_name)

返回表列的默认值。如果列没有默认值，则会出现错误。

仅允许对具有字面量默认值的列使用 DEFAULT(col_name)，而不允许对具有表达式默认值的列使用。

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

● FORMAT(X,D)

将数字 X 格式化为形如 '#,###,###.##'，四舍五入到 D 位小数，并以字符串形式返回结果。

● GROUPING(expr [, expr] ...)

对于包含 WITH ROLLUP 修饰符的 GROUP BY 查询，ROLLUP 操作生成超聚合输出行，其中 NULL 表示所有值的集合。GROUPING() 函数使您能够区分超级聚合行的 NULL 值与常规分组行的 NULL 值。

在选择列表、HAVING 子句和(从MySQL 8.0.12开始)ORDER BY 子句中允许使用 GROUPING()。

GROUPING() 的每个参数必须是与 GROUP BY 子句中的表达式完全匹配的表达式。表达式不能是位置说明符。对于每个表达式，如果当前行的表达式值是表示超聚合值的 NULL，则 GROUPING() 会生成 1。否则，GROUPING() 产生 0，表示表达式值对于常规结果行是 NULL 或不是 NULL。

假设表 t1 包含这些行，其中 NULL 表示类似于 “other” 或 “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 语句的表摘要如下所示：

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。

添加 WITH ROLLUP 将生成包含额外 NULL 值的超聚合汇总行。然而，如果不将这个结果与前一个结果进行比较，就很难看出哪些 NULL 值出现在超级聚合的行中，哪些出现在常规分组的行中：

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：

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() 的常用用法：

■ 用一个标签代替超聚合的 NULL 值：

mysql> SELECT
         IF(GROUPING(name) = 1, 'All items', name) AS name,
         IF(GROUPING(size) = 1, 'All sizes', size) AS 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      | All sizes |       35 |
| hoop      | NULL      |        3 |
| hoop      | large     |        5 |
| hoop      | small     |       15 |
| hoop      | All sizes |       23 |
| All items | All sizes |       58 |
+-----------+-----------+----------+
复制

■ 过滤掉常规的分组行，只返回超聚合行：

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

GROUPING() 允许多个表达式参数。在这种情况下，GROUPING() 返回值表示由每个表达式的结果组合而成的位掩码，其中最低位对应于最右边的表达式的结果。例如，有三个表达式参数时，GROUPING(expr1, expr2, expr3) 的值是这样的：

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

下面的查询显示了如何将单个参数的结果组合为多个参数调用来生成位掩码值：

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() 调用而不是多个单参数调用，提供了一种更简单的方法来编写先前只返回超聚合行的查询：

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() 受以下限制：

■ 不要使用子查询 GROUP BY 表达式作为 GROUPING() 参数，因为匹配可能会失败。例如，这个查询的匹配失败：

mysql> SELECT GROUPING((SELECT MAX(name) FROM t1))
       FROM t1
       GROUP BY (SELECT MAX(name) FROM t1) WITH ROLLUP;
ERROR 3580 (HY000): Argument #1 of GROUPING function is not in GROUP BY
复制

■ GROUP BY 字面量表达式不应该在 HAVING 子句中用作 GROUPING() 参数。由于优化器计算 GROUP BY 和 HAVING 时的不同，匹配可能成功，但 GROUPING() 计算不会产生预期的结果。考虑以下查询：

SELECT a AS f1, 'w' AS f2
FROM t
GROUP BY f1, f2 WITH ROLLUP
HAVING GROUPING(f2) = 1;
复制

官方网址：

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

mysql

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

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

评论

相关阅读