MySQL8 中文参考（五十四）

原文：docs.oracle.com/javase/tutorial/reallybigindex.html

14.19.3 MySQL GROUP BY 处理

原文：dev.mysql.com/doc/refman/8.0/en/group-by-handling.html

SQL-92 及更早版本不允许查询，其中选择列表、HAVING条件或ORDER BY列表引用未在GROUP BY子句中命名的非聚合列。例如，这个查询在标准 SQL-92 中是非法的，因为选择列表中的非聚合name列不出现在GROUP BY中：

SELECT o.custid, c.name, MAX(o.payment)
  FROM orders AS o, customers AS c
  WHERE o.custid = c.custid
  GROUP BY o.custid;

要使查询在 SQL-92 中合法，name列必须在选择列表中省略或在GROUP BY子句中命名。

SQL:1999 及更高版本允许这样的非聚合列，如果它们在功能上依赖于GROUP BY列，则可选择功能 T301 的可选特性：如果name和custid之间存在这样的关系，则查询是合法的。例如，如果custid是customers的主键，则会出现这种情况。

MySQL 实现了功能依赖的检测。如果启用了（默认情况下启用的）ONLY_FULL_GROUP_BY SQL 模式，MySQL 会拒绝查询，其中选择列表、HAVING条件或ORDER BY列表引用既不在GROUP BY子句中命名也不在功能上依赖于它们的非聚合列。

当启用 SQL ONLY_FULL_GROUP_BY模式时，MySQL 还允许在GROUP BY子句中未命名的非聚合列，前提是该列被限制为单个值，如下例所示：

mysql> CREATE TABLE mytable (
 ->    id INT UNSIGNED NOT NULL PRIMARY KEY,
 ->    a VARCHAR(10),
 ->    b INT
 -> );

mysql> INSERT INTO mytable
 -> VALUES (1, 'abc', 1000),
 ->        (2, 'abc', 2000),
 ->        (3, 'def', 4000);

mysql> SET SESSION sql_mode = sys.list_add(@@session.sql_mode, 'ONLY_FULL_GROUP_BY');

mysql> SELECT a, SUM(b) FROM mytable WHERE a = 'abc';
+------+--------+
| a    | SUM(b) |
+------+--------+
| abc  |   3000 |
+------+--------+

当使用ONLY_FULL_GROUP_BY时，SELECT列表中也可以有多个非聚合列。在这种情况下，每个这样的列必须在WHERE子句中限制为单个值，并且所有这些限制条件必须通过逻辑AND连接，如下所示：

mysql> DROP TABLE IF EXISTS mytable;

mysql> CREATE TABLE mytable (
 ->    id INT UNSIGNED NOT NULL PRIMARY KEY,
 ->    a VARCHAR(10),
 ->    b VARCHAR(10),
 ->    c INT
 -> );

mysql> INSERT INTO mytable
 -> VALUES (1, 'abc', 'qrs', 1000),
 ->        (2, 'abc', 'tuv', 2000),
 ->        (3, 'def', 'qrs', 4000),
 ->        (4, 'def', 'tuv', 8000),
 ->        (5, 'abc', 'qrs', 16000),
 ->        (6, 'def', 'tuv', 32000);

mysql> SELECT @@session.sql_mode;
+---------------------------------------------------------------+
| @@session.sql_mode                                            |
+---------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------------+

mysql> SELECT a, b, SUM(c) FROM mytable
 ->     WHERE a = 'abc' AND b = 'qrs';
+------+------+--------+
| a    | b    | SUM(c) |
+------+------+--------+
| abc  | qrs  |  17000 |
+------+------+--------+

如果禁用了ONLY_FULL_GROUP_BY，MySQL 对GROUP BY的标准 SQL 使用的扩展允许选择列表、HAVING条件或ORDER BY列表引用非聚合列，即使这些列在功能上不依赖于GROUP BY列。这导致 MySQL 接受前面的查询。在这种情况下，服务器可以自由选择每个组中的任何值，因此除非它们相同，否则所选的值是不确定的，这可能不是您想要的。此外，从每个组中选择值后，不能通过添加ORDER BY子句来影响。结果集排序发生在值被选择之后，ORDER BY不影响服务器选择每个组中的哪个值。禁用ONLY_FULL_GROUP_BY主要在您知道由于数据的某些属性，GROUP BY中未命名的每个非聚合列的所有值对于每个组都相同时才有用。

你可以通过使用 ANY_VALUE() 引用非聚合列来达到相同效果，而不禁用 ONLY_FULL_GROUP_BY。

以下讨论演示了功能依赖，当功能依赖不存在时 MySQL 产生的错误消息，以及在功能依赖不存在时导致 MySQL 接受查询的方法。

如果启用了 ONLY_FULL_GROUP_BY，则此查询可能无效，因为选择列表中的非聚合 address 列未在 GROUP BY 子句中命名：

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

如果 name 是 t 的主键或是唯一的 NOT NULL 列，则查询是有效的。在这种情况下，MySQL 会认识到所选列在一个分组列上具有功能依赖关系。例如，如果 name 是主键，其值确定了 address 的值，因为每个组只有一个主键值，因此只有一行。因此，在组中选择 address 值时没有随机性，也不需要拒绝查询。

如果 name 不是 t 的主键或唯一的 NOT NULL 列，则查询是无效的。在这种情况下，无法推断出功能依赖关系，会发生错误：

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

如果你知道，对于给定的数据集，每个 name 值实际上唯一确定了 address 值，那么 address 实际上是依赖于 name 的。为了告诉 MySQL 接受这个查询，你可以使用 ANY_VALUE() 函数：

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

或者禁用 ONLY_FULL_GROUP_BY。

上面的例子相当简单。特别是，你不太可能仅对一个主键列进行分组，因为每个组只包含一行。要了解更复杂查询中的功能依赖的其他示例，请参见 第 14.19.4 节，“功能依赖的检测”。

如果一个查询有聚合函数但没有 GROUP BY 子句，则在启用 ONLY_FULL_GROUP_BY 的情况下，选择列表、HAVING 条件或 ORDER 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 选择哪个 name 值并不重要：

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

ONLY_FULL_GROUP_BY 也会影响使用 DISTINCT 和 ORDER BY 的查询处理。考虑一个包含三列 c1、c2 和 c3 的表 t，其中包含以下行：

c1 c2 c3
1  2  A
3  4  B
1  2  C

假设我们执行以下查询，期望结果按 c3 排序：

SELECT DISTINCT c1, c2 FROM t ORDER BY c3;

要对结果进行排序，必须先消除重复项。但是，在这样做时，我们应该保留第一行还是第三行？这种任意选择会影响 c3 的保留值，进而影响排序并使其变得任意。为了避免这个问题，如果任何一个 ORDER BY 表达式不满足以下条件，具有 DISTINCT 和 ORDER BY 的查询将被拒绝为无效：

• 该表达式等于选择列表中的一个

• 表达式引用的所有列并且属于查询选定的表的元素都在选择列表中

另一个 MySQL 对标准 SQL 的扩展允许在 HAVING 子句中引用选择列表中的别名表达式。例如，以下查询返回表 orders 中仅出现一次的 name 值：

SELECT name, COUNT(name) FROM orders
  GROUP BY name
  HAVING COUNT(name) = 1;

MySQL 扩展允许在聚合列的 HAVING 子句中使用别名：

SELECT name, COUNT(name) AS c FROM orders
  GROUP BY name
  HAVING c = 1;

标准 SQL 仅允许在 GROUP BY 子句中使用列表达式，因此像这样的语句是无效的，因为 FLOOR(value/100) 是一个非列表达式：

SELECT id, FLOOR(value/100)
  FROM *tbl_name*
  GROUP BY id, FLOOR(value/100);

MySQL 扩展了标准 SQL，允许在 GROUP BY 子句中使用非列表达式，并认为前述语句是有效的。

标准 SQL 也不允许在 GROUP BY 子句中使用别名。MySQL 扩展了标准 SQL，允许使用别名，因此编写查询的另一种方式如下：

SELECT id, FLOOR(value/100) AS val
  FROM *tbl_name*
  GROUP BY id, val;

别名 val 被视为 GROUP BY 子句中的列表达式。

在 GROUP BY 子句中存在非列表达式的情况下，MySQL 认可该表达式与选择列表中的表达式相等。这意味着启用 ONLY_FULL_GROUP_BY SQL 模式时，包含 GROUP BY id, FLOOR(value/100) 的查询是有效的，因为选择列表中也包含相同的 FLOOR() 表达式。然而，MySQL 不会尝试识别对 GROUP BY 非列表达式的函数依赖性，因此即使第三个选择的表达式是 id 列和 GROUP BY 子句中的 FLOOR() 表达式的简单公式，以下查询在启用 ONLY_FULL_GROUP_BY 的情况下是无效的：

SELECT id, FLOOR(value/100), id+FLOOR(value/100)
  FROM *tbl_name*
  GROUP BY id, FLOOR(value/100);

一个解决方法是使用派生表：

SELECT id, F, id+F
  FROM
    (SELECT id, FLOOR(value/100) AS F
     FROM *tbl_name*
     GROUP BY id, FLOOR(value/100)) AS dt;

14.19.4 功能依赖的检测

原文：dev.mysql.com/doc/refman/8.0/en/group-by-functional-dependence.html

以下讨论提供了 MySQL 检测功能依赖的几个示例。示例使用以下符号表示：

{*X*} -> {*Y*}

将其理解为“X 唯一确定 Y”，这也意味着 Y 在 X 上是函数依赖的。

示例使用 world 数据库，可以从 dev.mysql.com/doc/index-other.html 下载。您可以在同一页面找到如何安装数据库的详细信息。

• 从键派生的功能依赖

• 从多列键和等式派生的功能依赖

• 功能依赖特殊情况

• 功能依赖和视图

• 功能依赖的组合

从键派生的功能依赖

以下查询为每个国家选择使用语言的人数：

SELECT co.Name, COUNT(*)
FROM countrylanguage cl, country co
WHERE cl.CountryCode = co.Code
GROUP BY co.Code;

co.Code 是 co 的主键，因此 co 的所有列都对其具有函数依赖，使用以下符号表示：

{co.Code} -> {co.*}

因此，co.name 在 GROUP BY 列上是函数依赖的，查询是有效的。

可以使用在 NOT NULL 列上的 UNIQUE 索引代替主键，相同的功能依赖也适用。（对于允许 NULL 值的 UNIQUE 索引，这不成立，因为它允许多个 NULL 值，此时唯一性丢失。）

从多列键和等式派生的功能依赖

此查询为每个国家选择所有使用的语言及使用该语言的人数列表：

SELECT co.Name, cl.Language,
cl.Percentage * co.Population / 100.0 AS SpokenBy
FROM countrylanguage cl, country co
WHERE cl.CountryCode = co.Code
GROUP BY cl.CountryCode, cl.Language;

(cl.CountryCode, cl.Language) 是 cl 的两列复合主键，因此列对唯一确定了 cl 的所有列：

{cl.CountryCode, cl.Language} -> {cl.*}

此外，由于 WHERE 子句中的等式：

{cl.CountryCode} -> {co.Code}

并且，因为 co.Code 是 co 的主键：

{co.Code} -> {co.*}

“唯一确定”关系是传递的，因此：

{cl.CountryCode, cl.Language} -> {cl.*,co.*}

结果，查询是有效的。

与前面的示例一样，可以使用在 NOT NULL 列上的 UNIQUE 键代替主键。

可以使用 INNER JOIN 条件代替 WHERE。相同的功能依赖适用：

SELECT co.Name, cl.Language,
cl.Percentage * co.Population/100.0 AS SpokenBy
FROM countrylanguage cl INNER JOIN country co
ON cl.CountryCode = co.Code
GROUP BY cl.CountryCode, cl.Language;

功能依赖特殊情况

而在WHERE条件或INNER JOIN条件中的相等性测试是对称的，但在外连接条件中的相等性测试不是，因为表扮演不同的角色。

假设引用完整性被意外破坏，并且存在一个countrylanguage中没有对应行的country行。考虑与前一个示例中相同的查询，但使用LEFT JOIN：

SELECT co.Name, cl.Language,
cl.Percentage * co.Population/100.0 AS SpokenBy
FROM countrylanguage cl LEFT JOIN country co
ON cl.CountryCode = co.Code
GROUP BY cl.CountryCode, cl.Language;

对于给定的cl.CountryCode值，在连接结果中co.Code的值要么在匹配行中找到（由cl.CountryCode确定），要么如果没有匹配则是NULL-补充的（也由cl.CountryCode确定）。在每种情况下，这种关系适用：

{cl.CountryCode} -> {co.Code}

cl.CountryCode本身对{cl.CountryCode，cl.Language}具有函数依赖，这是一个主键。

如果在连接结果中co.Code是NULL-补充的，那么co.Name也是。如果co.Code没有被NULL-补充，那么因为co.Code是主键，它决定了co.Name。因此，在所有情况下：

{co.Code} -> {co.Name}

产生：

{cl.CountryCode, cl.Language} -> {cl.*,co.*}

结果，该查询是有效的。

然而，假设表被交换，如此查询：

SELECT co.Name, cl.Language,
cl.Percentage * co.Population/100.0 AS SpokenBy
FROM country co LEFT JOIN countrylanguage cl
ON cl.CountryCode = co.Code
GROUP BY cl.CountryCode, cl.Language;

现在这种关系不适用：

{cl.CountryCode, cl.Language} -> {cl.*,co.*}

实际上，为cl制作的所有NULL-补充行被放入一个单独的组中（它们的GROUP BY列都等于NULL），在这个组内，co.Name的值可以变化。查询是无效的，MySQL 拒绝它。

外连接中的函数依赖因此与决定性列属于LEFT JOIN的左侧还是右侧有关。如果存在嵌套的外连接或连接条件不完全由相等比较组成，则函数依赖的确定变得更加复杂。

函数依赖和视图

假设一个关于国家的视图生成它们的代码、它们的大写名称以及它们拥有多少种不同的官方语言：

CREATE VIEW country2 AS
SELECT co.Code, UPPER(co.Name) AS UpperName,
COUNT(cl.Language) AS OfficialLanguages
FROM country AS co JOIN countrylanguage AS cl
ON cl.CountryCode = co.Code
WHERE cl.isOfficial = 'T'
GROUP BY co.Code;

这个定义是有效的，因为：

{co.Code} -> {co.*}

在视图结果中，第一个选择的列是co.Code，它也是分组列，因此决定了所有其他选择的表达式：

{country2.Code} -> {country2.*}

MySQL 理解这一点并使用这些信息，如下所述。

该查询显示了国家、它们拥有多少种不同的官方语言以及它们拥有多少个城市，通过将视图与city表进行连接：

SELECT co2.Code, co2.UpperName, co2.OfficialLanguages,
COUNT(*) AS Cities
FROM country2 AS co2 JOIN city ci
ON ci.CountryCode = co2.Code
GROUP BY co2.Code;

这个查询是有效的，因为如前所述：

{co2.Code} -> {co2.*}

MySQL 能够发现视图结果中的函数依赖，并使用它来验证使用该视图的查询。如果country2是一个派生表（或公共表达式），情况也是如此，如下所示：

SELECT co2.Code, co2.UpperName, co2.OfficialLanguages,
COUNT(*) AS Cities
FROM
(
 SELECT co.Code, UPPER(co.Name) AS UpperName,
 COUNT(cl.Language) AS OfficialLanguages
 FROM country AS co JOIN countrylanguage AS cl
 ON cl.CountryCode=co.Code
 WHERE cl.isOfficial='T'
 GROUP BY co.Code
) AS co2
JOIN city ci ON ci.CountryCode = co2.Code
GROUP BY co2.Code;

函数依赖的组合

MySQL 能够结合所有前述类型的函数依赖（基于键、基于相等性、基于视图）来验证更复杂的查询。

14.20 窗口函数

原文：dev.mysql.com/doc/refman/8.0/en/window-functions.html

14.20.1 窗口函数描述

14.20.2 窗口函数概念和语法

14.20.3 窗口函数帧规范

14.20.4 命名窗口

14.20.5 窗口函数限制

MySQL 支持窗口函数，对于查询结果中的每一行，使用与该行相关的行执行计算。以下部分讨论如何使用窗口函数，包括OVER和WINDOW子句的描述。第一部分提供了非聚合窗口函数的描述。有关聚合窗口函数的描述，请参见第 14.19.1 节，“聚合函数描述”。

有关优化和窗口函数的信息，请参见第 10.2.1.21 节，“窗口函数优化”。

14.20.1 窗口函数描述

原文：dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

本节描述了非聚合窗口函数，对于查询中的每一行，使用与该行相关的行执行计算。大多数聚合函数也可以用作窗口函数；请参见第 14.19.1 节，“聚合函数描述”。

有关窗口函数的使用信息和示例，以及术语的定义，如OVER子句、窗口、分区、帧和对等行，请参见第 14.20.2 节，“窗口函数概念和语法”。

表 14.30 窗口函数

名称	描述
CUME_DIST()	累积分布值
DENSE_RANK()	分区内当前行的排名，无间隔
FIRST_VALUE()	窗口帧的第一行的参数值
LAG()	分区内滞后于当前行的行的参数值
LAST_VALUE()	窗口帧的最后一行的参数值
LEAD()	分区内领先于当前行的行的参数值
NTH_VALUE()	窗口帧的第 N 行的参数值
NTILE()	当前行在其分区内的桶编号
PERCENT_RANK()	百分比排名值
RANK()	分区内当前行的排名，有间隔
ROW_NUMBER()	当前行在其分区内的编号
名称	描述

在以下函数描述中，*over_clause代表OVER子句，详见第 14.20.2 节，“窗口函数概念和语法”。一些窗口函数允许使用null_treatment*子句，指定在计算结果时如何处理NULL值。此子句是可选的。它是 SQL 标准的一部分，但 MySQL 实现仅允许RESPECT NULLS（这也是默认值）。这意味着在计算结果时会考虑NULL值。IGNORE NULLS被解析，但会产生错误。

• CUME_DIST() over_clause

  返回值在组内值的累积分布；即，在当前行中窗口排序的窗口分区中小于或等于当前行中值的分区值的百分比。这表示在窗口分区中在当前行之前或与当前行并列的行数除以窗口分区中的总行数。返回值范围从 0 到 1。

  此函数应与 ORDER BY 一起使用，以将分区行按所需顺序排序。没有 ORDER BY，所有行都是对等的，并且具有值 N/N = 1，其中 N 是分区大小。

  over_clause 如 第 14.20.2 节，“窗口函数概念和语法” 中所述。

  以下查询显示了 val 列中值集合的每行的 CUME_DIST() 值，以及类似的 PERCENT_RANK() 函数返回的百分比排名值。供参考，查询还使用 ROW_NUMBER() 显示行号：

  mysql> SELECT
           val,
           ROW_NUMBER()   OVER w AS 'row_number',
           CUME_DIST()    OVER w AS 'cume_dist',
           PERCENT_RANK() OVER w AS 'percent_rank'
         FROM numbers
         WINDOW w AS (ORDER BY val);
  +------+------------+--------------------+--------------+
  | val  | row_number | cume_dist          | percent_rank |
  +------+------------+--------------------+--------------+
  |    1 |          1 | 0.2222222222222222 |            0 |
  |    1 |          2 | 0.2222222222222222 |            0 |
  |    2 |          3 | 0.3333333333333333 |         0.25 |
  |    3 |          4 | 0.6666666666666666 |        0.375 |
  |    3 |          5 | 0.6666666666666666 |        0.375 |
  |    3 |          6 | 0.6666666666666666 |        0.375 |
  |    4 |          7 | 0.8888888888888888 |         0.75 |
  |    4 |          8 | 0.8888888888888888 |         0.75 |
  |    5 |          9 |                  1 |            1 |
  +------+------------+--------------------+--------------+
  

• DENSE_RANK() over_clause

  返回当前行在其分区内的排名，没有间隔。对等行被视为并列，并获得相同的排名。此函数为对等组分配连续的排名；结果是大小大于一的组不会产生不连续的排名数字。有关示例，请参阅 RANK() 函数描述。

  此函数应与 ORDER BY 一起使用，以将分区行按所需顺序排序。没有 ORDER BY，所有行都是对等的。

  over_clause 如 第 14.20.2 节，“窗口函数概念和语法” 中所述。

• FIRST_VALUE(*expr*) [null_treatment] over_clause

  返回窗口帧的第一行中 expr 的值。

  over_clause 如 第 14.20.2 节，“窗口函数概念和语法” 中所述。null_treatment 如章节介绍中所述。

  以下查询演示了 FIRST_VALUE()、LAST_VALUE() 和两个 NTH_VALUE() 的实例：

  mysql> SELECT
           time, subject, val,
           FIRST_VALUE(val)  OVER w AS 'first',
           LAST_VALUE(val)   OVER w AS 'last',
           NTH_VALUE(val, 2) OVER w AS 'second',
           NTH_VALUE(val, 4) OVER w AS 'fourth'
         FROM observations
         WINDOW w AS (PARTITION BY subject ORDER BY time
                      ROWS UNBOUNDED PRECEDING);
  +----------+---------+------+-------+------+--------+--------+
  | time     | subject | val  | first | last | second | fourth |
  +----------+---------+------+-------+------+--------+--------+
  | 07:00:00 | st113   |   10 |    10 |   10 |   NULL |   NULL |
  | 07:15:00 | st113   |    9 |    10 |    9 |      9 |   NULL |
  | 07:30:00 | st113   |   25 |    10 |   25 |      9 |   NULL |
  | 07:45:00 | st113   |   20 |    10 |   20 |      9 |     20 |
  | 07:00:00 | xh458   |    0 |     0 |    0 |   NULL |   NULL |
  | 07:15:00 | xh458   |   10 |     0 |   10 |     10 |   NULL |
  | 07:30:00 | xh458   |    5 |     0 |    5 |     10 |   NULL |
  | 07:45:00 | xh458   |   30 |     0 |   30 |     10 |     30 |
  | 08:00:00 | xh458   |   25 |     0 |   25 |     10 |     30 |
  +----------+---------+------+-------+------+--------+--------+
  

  每个函数使用当前帧中的行，根据所示的窗口定义，该帧从第一个分区行延伸到当前行。对于 NTH_VALUE() 调用，当前帧并不总是包括请求的行；在这种情况下，返回值为 NULL。

• LAG(*expr* [, *N*[, *default*]]) [null_treatment] over_clause

  返回在其分区内当前行之前*N行的行的expr的值。如果没有这样的行，则返回值为default。例如，如果N为 3，则前三行的返回值为default。如果N或default*缺失，则默认值分别为 1 和NULL。

  N必须是一个字面非负整数。如果N为 0，则为当前行评估expr。

  从 MySQL 8.0.22 开始，N 不能为NULL。此外，它现在必须是范围在0到2⁶³之间的整数，包括以下任一形式：

  • 一个无符号整数常量字面值

  • 一个位置参数标记（?）

  • 一个用户定义的变量

  • 存储过程中的局部变量

  over_clause 如第 14.20.2 节，“窗口函数概念和语法”所述。null_treatment 如章节介绍所述。

  LAG()（以及类似的LEAD()函数）经常用于计算行之间的差异。以下查询显示了一组按时间排序的观测值，以及每个观测值的LAG()和LEAD()值，以及当前行与相邻行之间的差异：

  mysql> SELECT
           t, val,
           LAG(val)        OVER w AS 'lag',
           LEAD(val)       OVER w AS 'lead',
           val - LAG(val)  OVER w AS 'lag diff',
           val - LEAD(val) OVER w AS 'lead diff'
         FROM series
         WINDOW w AS (ORDER BY t);
  +----------+------+------+------+----------+-----------+
  | t        | val  | lag  | lead | lag diff | lead diff |
  +----------+------+------+------+----------+-----------+
  | 12:00:00 |  100 | NULL |  125 |     NULL |       -25 |
  | 13:00:00 |  125 |  100 |  132 |       25 |        -7 |
  | 14:00:00 |  132 |  125 |  145 |        7 |       -13 |
  | 15:00:00 |  145 |  132 |  140 |       13 |         5 |
  | 16:00:00 |  140 |  145 |  150 |       -5 |       -10 |
  | 17:00:00 |  150 |  140 |  200 |       10 |       -50 |
  | 18:00:00 |  200 |  150 | NULL |       50 |      NULL |
  +----------+------+------+------+----------+-----------+
  

  在示例中，LAG()和LEAD()调用使用默认的*N和default*值分别为 1 和NULL。

  第一行显示了当LAG()没有前一行时会发生什么：函数返回*default*值（在本例中为NULL）。最后一行显示了当LEAD()没有下一行时会发生的情况。

  LAG()和LEAD()还用于计算和而不是差。考虑这个数据集，其中包含斐波那契数列的前几个数字：

  mysql> SELECT n FROM fib ORDER BY n;
  +------+
  | n    |
  +------+
  |    1 |
  |    1 |
  |    2 |
  |    3 |
  |    5 |
  |    8 |
  +------+
  

  以下查询显示了与当前行相邻的行的LAG()和LEAD()值。它还使用这些函数将前一行和后一行的值添加到当前行值中。效果是生成斐波那契数列中的下一个数字，以及其后一个数字：

  mysql> SELECT
           n,
           LAG(n, 1, 0)      OVER w AS 'lag',
           LEAD(n, 1, 0)     OVER w AS 'lead',
           n + LAG(n, 1, 0)  OVER w AS 'next_n',
           n + LEAD(n, 1, 0) OVER w AS 'next_next_n'
         FROM fib
         WINDOW w AS (ORDER BY n);
  +------+------+------+--------+-------------+
  | n    | lag  | lead | next_n | next_next_n |
  +------+------+------+--------+-------------+
  |    1 |    0 |    1 |      1 |           2 |
  |    1 |    1 |    2 |      2 |           3 |
  |    2 |    1 |    3 |      3 |           5 |
  |    3 |    2 |    5 |      5 |           8 |
  |    5 |    3 |    8 |      8 |          13 |
  |    8 |    5 |    0 |     13 |           8 |
  +------+------+------+--------+-------------+
  

  生成斐波那契数列的初始集合的一种方法是使用递归公共表达式。有关示例，请参见 Fibonacci Series Generation。

  从 MySQL 8.0.22 开始，您不能在此函数的行参数中使用负值。

• LAST_VALUE(*expr*) [null_treatment] over_clause

  返回窗口帧的最后一行的*expr*的值。

  *over_clause如第 14.20.2 节，“窗口函数概念和语法”中所述。null_treatment*如本节介绍中所述。

  有关示例，请参见FIRST_VALUE()函数描述。

• LEAD(*expr* [, *N*[, *default*]]) [null_treatment] over_clause

  返回在其分区内跟随当前行的第N行的*expr的值。如果没有这样的行，则返回值为default。例如，如果N为 3，则最后三行的返回值为default。如果N或default*缺失，则默认值分别为 1 和NULL。

  N必须是一个字面非负整数。如果N为 0，则为当前行评估*expr*。

  从 MySQL 8.0.22 开始，*N*不能为NULL。此外，现在它必须是范围为0到2⁶³（包括）的整数，可以采用以下任何形式：

  • 一个无符号整数常量字面值

  • 一个位置参数标记（?）

  • 用户定义变量

  • 存储过程中的局部变量

  *over_clause如第 14.20.2 节，“窗口函数概念和语法”中所述。null_treatment*如本节介绍中所述。

  有关示例，请参见LAG()函数描述。

  在 MySQL 8.0.22 及更高版本中，不允许在此函数的行参数中使用负值。

• NTH_VALUE(*expr*, *N*) [from_first_last] [null_treatment] over_clause

  返回窗口帧的第N行的*expr*的值。如果没有这样的行，则返回值为NULL。

  *N*必须是一个字面正整数。

  *from_first_last*是 SQL 标准的一部分，但 MySQL 实现仅允许FROM FIRST（这也是默认值）。这意味着计算从窗口的第一行开始。FROM LAST被解析，但会产生错误。要获得与FROM LAST相同的效果（从窗口的最后一行开始计算），请使用ORDER BY以相反顺序排序。

  *over_clause如第 14.20.2 节，“窗口函数概念和语法”中描述的那样。null_treatment*如章节介绍中描述的那样。

  有关示例，请参阅FIRST_VALUE()函数描述。

  在 MySQL 8.0.22 及更高版本中，您不能将NULL用作此函数的行参数。

• NTILE(*N*) over_clause

  将分区分成*N组（桶），为分区中的每行分配其桶号，并返回当前行在其分区中的桶号。例如，如果N为 4，NTILE()将行分成四个桶。如果N*为 100，NTILE()将行分成 100 个桶。

  N必须是一个字面正整数。桶号返回值范围从 1 到N。

  从 MySQL 8.0.22 开始，*N*不能为NULL，必须是范围在0到2⁶³之间的整数，可以采用以下任何形式：

  • 一个无符号整数常量字面值

  • 一个位置参数标记（?）

  • 一个用户定义的变量

  • 存储过程中的局部变量

  此函数应与ORDER BY一起使用，以将分区行按所需顺序排序。

  *over_clause*如第 14.20.2 节，“窗口函数概念和语法”中描述的那样。

  以下查询显示了val列中值集合的百分位值，将行分成两组或四组。为了参考，查询还使用ROW_NUMBER()显示行号：

  mysql> SELECT
           val,
           ROW_NUMBER() OVER w AS 'row_number',
           NTILE(2)     OVER w AS 'ntile2',
           NTILE(4)     OVER w AS 'ntile4'
         FROM numbers
         WINDOW w AS (ORDER BY val);
  +------+------------+--------+--------+
  | val  | row_number | ntile2 | ntile4 |
  +------+------------+--------+--------+
  |    1 |          1 |      1 |      1 |
  |    1 |          2 |      1 |      1 |
  |    2 |          3 |      1 |      1 |
  |    3 |          4 |      1 |      2 |
  |    3 |          5 |      1 |      2 |
  |    3 |          6 |      2 |      3 |
  |    4 |          7 |      2 |      3 |
  |    4 |          8 |      2 |      4 |
  |    5 |          9 |      2 |      4 |
  +------+------------+--------+--------+
  

  从 MySQL 8.0.22 开始，不再允许使用构造NTILE(NULL)。

• PERCENT_RANK() over_clause

  返回小于当前行值的分区值的百分比，不包括最高值。返回值范围从 0 到 1，表示行相对排名，计算公式的结果如下，其中*rank是行排名，rows*是分区行数：

  (*rank* - 1) / (*rows* - 1)
  

  此函数应与ORDER BY一起使用，以将分区行按所需顺序排序。没有ORDER BY，所有行都是同级。

  *over_clause*如第 14.20.2 节，“窗口函数概念和语法”中描述的那样。

  有关示例，请参阅CUME_DIST()函数描述。

• RANK() over_clause

  返回当前行在其分区中的排名，带有间隙。同级被视为并列并获得相同的排名。如果存在大于一的同级组，则此函数不会为同级组分配连续的排名；结果是不连续的排名数字。

  此函数应与 ORDER BY 一起使用，以将分区行按所需顺序排序。没有 ORDER BY，所有行都是对等值。

  over_clause 如 第 14.20.2 节，“窗口函数概念和语法” 中所述。

  下面的查询展示了 RANK() 和 DENSE_RANK() 之间的差异。前者生成带有间隔的排名，后者生成没有间隔的排名。该查询显示了 val 列中一组值的排名，其中包含一些重复值。RANK() 为对等值（重复值）分配相同的排名，下一个更大的值的排名比对等值的数量减一高。DENSE_RANK() 也为对等值分配相同的排名，但下一个更大的值的排名比前一个高一。为了参考，该查询还使用 ROW_NUMBER() 显示行号：

  mysql> SELECT
           val,
           ROW_NUMBER() OVER w AS 'row_number',
           RANK()       OVER w AS 'rank',
           DENSE_RANK() OVER w AS 'dense_rank'
         FROM numbers
         WINDOW w AS (ORDER BY val);
  +------+------------+------+------------+
  | val  | row_number | rank | dense_rank |
  +------+------------+------+------------+
  |    1 |          1 |    1 |          1 |
  |    1 |          2 |    1 |          1 |
  |    2 |          3 |    3 |          2 |
  |    3 |          4 |    4 |          3 |
  |    3 |          5 |    4 |          3 |
  |    3 |          6 |    4 |          3 |
  |    4 |          7 |    7 |          4 |
  |    4 |          8 |    7 |          4 |
  |    5 |          9 |    9 |          5 |
  +------+------------+------+------------+
  

• ROW_NUMBER() over_clause

  返回当前行在其分区内的编号。行号从 1 开始，到分区行数结束。

  ORDER BY 影响编号行的顺序。没有 ORDER BY，行编号是不确定的。

  ROW_NUMBER() 为对等值分配不同的行号。要为对等值分配相同的值，请使用 RANK() 或 DENSE_RANK()。有关示例，请参阅 RANK() 函数描述。

  over_clause 如 第 14.20.2 节，“窗口函数概念和语法” 中所述。

14.20.2 窗口函数概念和语法

原文：dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html

本节描述了如何使用窗口函数。示例使用与第 14.19.2 节，“GROUP BY 修饰符”中的GROUPING()函数讨论中找到的相同销售信息数据集：

mysql> SELECT * FROM sales ORDER BY country, year, product;
+------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2000 | Finland | Phone      |    100 |
| 2001 | Finland | Phone      |     10 |
| 2000 | India   | Calculator |     75 |
| 2000 | India   | Calculator |     75 |
| 2000 | India   | Computer   |   1200 |
| 2000 | USA     | Calculator |     75 |
| 2000 | USA     | Computer   |   1500 |
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   1500 |
| 2001 | USA     | Computer   |   1200 |
| 2001 | USA     | TV         |    150 |
| 2001 | USA     | TV         |    100 |
+------+---------+------------+--------+

窗口函数在一组查询行上执行类似聚合的操作。然而，聚合操作将查询行分组为单个结果行，而窗口函数为每个查询行产生一个结果：

• 函数评估发生的行称为当前行。

• 与函数评估相关的当前行的查询行构成了当前行的窗口。

例如，使用销售信息表，这两个查询执行产生所有行作为一组的单个全局总和的聚合操作，以及按国家分组的总和：

mysql> SELECT SUM(profit) AS total_profit
       FROM sales;
+--------------+
| total_profit |
+--------------+
|         7535 |
+--------------+
mysql> SELECT country, SUM(profit) AS country_profit
       FROM sales
       GROUP BY country
       ORDER BY country;
+---------+----------------+
| country | country_profit |
+---------+----------------+
| Finland |           1610 |
| India   |           1350 |
| USA     |           4575 |
+---------+----------------+

相比之下，窗口操作不会将查询行的组合折叠为单个输出行。相反，它们为每一行产生一个结果。与前面的查询一样，以下查询使用SUM()，但这次作为一个窗口函数：

mysql> SELECT
         year, country, product, profit,
         SUM(profit) OVER() AS total_profit,
         SUM(profit) OVER(PARTITION BY country) AS country_profit
       FROM sales
       ORDER BY country, year, product, profit;
+------+---------+------------+--------+--------------+----------------+
| year | country | product    | profit | total_profit | country_profit |
+------+---------+------------+--------+--------------+----------------+
| 2000 | Finland | Computer   |   1500 |         7535 |           1610 |
| 2000 | Finland | Phone      |    100 |         7535 |           1610 |
| 2001 | Finland | Phone      |     10 |         7535 |           1610 |
| 2000 | India   | Calculator |     75 |         7535 |           1350 |
| 2000 | India   | Calculator |     75 |         7535 |           1350 |
| 2000 | India   | Computer   |   1200 |         7535 |           1350 |
| 2000 | USA     | Calculator |     75 |         7535 |           4575 |
| 2000 | USA     | Computer   |   1500 |         7535 |           4575 |
| 2001 | USA     | Calculator |     50 |         7535 |           4575 |
| 2001 | USA     | Computer   |   1200 |         7535 |           4575 |
| 2001 | USA     | Computer   |   1500 |         7535 |           4575 |
| 2001 | USA     | TV         |    100 |         7535 |           4575 |
| 2001 | USA     | TV         |    150 |         7535 |           4575 |
+------+---------+------------+--------+--------------+----------------+

查询中的每个窗口操作都通过包含指定如何将查询行分组以供窗口函数处理的OVER子句来表示：

• 第一个OVER子句为空，这将整个查询行集视为单个分区。因此，窗口函数为每一行产生一个全局总和。

• 第二个OVER子句按国家对行进行分区，为每个分区（每个国家）产生一个总和。该函数为每个分区行产生这个总和。

窗口函数仅允许在选择列表和ORDER BY子句中使用。查询结果行是从FROM子句中确定的，在WHERE、GROUP BY和HAVING处理之后，窗口执行发生在ORDER BY、LIMIT和SELECT DISTINCT之前。

OVER子句允许许多聚合函数，因此可以根据OVER子句的存在与否将其用作窗口函数或非窗口函数：

AVG()
BIT_AND()
BIT_OR()
BIT_XOR()
COUNT()
JSON_ARRAYAGG()
JSON_OBJECTAGG()
MAX()
MIN()
STDDEV_POP(), STDDEV(), STD()
STDDEV_SAMP()
SUM()
VAR_POP(), VARIANCE()
VAR_SAMP()

对于每个聚合函数的详细信息，请参见第 14.19.1 节，“聚合函数描述”。

MySQL 还支持仅用作窗口函数的非聚合函数。对于这些函数，OVER子句是强制的：

CUME_DIST()
DENSE_RANK()
FIRST_VALUE()
LAG()
LAST_VALUE()
LEAD()
NTH_VALUE()
NTILE()
PERCENT_RANK()
RANK()
ROW_NUMBER()

对于每个非聚合函数的详细信息，请参见第 14.20.1 节，“窗口函数描述”。

作为那些非聚合窗口函数之一的示例，此查询使用ROW_NUMBER()，它生成每个分区内每行的行号。在本例中，行按国家编号。默认情况下，分区行是无序的，行编号是不确定的。要对分区行进行排序，请在窗口定义中包含一个ORDER BY子句。查询使用无序和有序分区（row_num1和row_num2列）来说明省略和包含ORDER BY之间的差异：

mysql> SELECT
         year, country, product, profit,
         ROW_NUMBER() OVER(PARTITION BY country) AS row_num1,
         ROW_NUMBER() OVER(PARTITION BY country ORDER BY year, product) AS row_num2
       FROM sales;
+------+---------+------------+--------+----------+----------+
| year | country | product    | profit | row_num1 | row_num2 |
+------+---------+------------+--------+----------+----------+
| 2000 | Finland | Computer   |   1500 |        2 |        1 |
| 2000 | Finland | Phone      |    100 |        1 |        2 |
| 2001 | Finland | Phone      |     10 |        3 |        3 |
| 2000 | India   | Calculator |     75 |        2 |        1 |
| 2000 | India   | Calculator |     75 |        3 |        2 |
| 2000 | India   | Computer   |   1200 |        1 |        3 |
| 2000 | USA     | Calculator |     75 |        5 |        1 |
| 2000 | USA     | Computer   |   1500 |        4 |        2 |
| 2001 | USA     | Calculator |     50 |        2 |        3 |
| 2001 | USA     | Computer   |   1500 |        3 |        4 |
| 2001 | USA     | Computer   |   1200 |        7 |        5 |
| 2001 | USA     | TV         |    150 |        1 |        6 |
| 2001 | USA     | TV         |    100 |        6 |        7 |
+------+---------+------------+--------+----------+----------+

如前所述，要使用窗口函数（或将聚合函数视为窗口函数），请在函数调用后包含一个OVER子句。OVER子句有两种形式：

*over_clause*:
    {OVER (*window_spec*) | OVER *window_name*}

这两种形式定义了窗口函数如何处理查询行。它们的区别在于窗口是直接在OVER子句中定义，还是通过引用在查询中其他地方定义的命名窗口提供：

• 在第一种情况下，窗口规范直接出现在括号之间的OVER子句中。

• 在第二种情况下，*window_name*是查询中其他地方由WINDOW子句定义的窗口规范的名称。有关详细信息，请参见第 14.20.4 节，“命名窗口”。

对于OVER (*window_spec*)语法，窗口规范有几个部分，都是可选的：

*window_spec*:
    [*window_name*] [*partition_clause*] [*order_clause*] [*frame_clause*]

如果OVER()为空，则窗口包含所有查询行，窗口函数使用所有行计算结果。否则，括号内的子句确定用于计算函数结果的查询行以及它们如何分区和排序：

• window_name：查询中其他地方由WINDOW子句定义的窗口的名称。如果*window_name*单独出现在OVER子句中，它完全定义了窗口。如果还提供了分区、排序或帧子句，则它们修改了命名窗口的解释。有关详细信息，请参见第 14.20.4 节，“命名窗口”。

• partition_clause：PARTITION BY子句指示如何将查询行分成组。给定行的窗口函数结果基于包含该行的分区的行。如果省略PARTITION BY，则有一个包含所有查询行的单个分区。

  注意

  窗口函数的分区与表分区不同。有关表分区的信息，请参见第二十六章，分区。

  *partition_clause*的语法如下：

  *partition_clause*:
      PARTITION BY *expr* [, *expr*] ...
  

  标准 SQL 要求 PARTITION BY 后面只能跟列名。MySQL 的扩展允许表达式，而不仅仅是列名。例如，如果一个表包含名为 ts 的 TIMESTAMP 列，标准 SQL 允许 PARTITION BY ts，但不允许 PARTITION BY HOUR(ts)，而 MySQL 允许两者。

• order_clause: ORDER BY 子句指示如何对每个分区的行进行排序。根据 ORDER BY 子句相等的分区行被视为对等。如果省略 ORDER BY，分区行是无序的，没有暗示任何处理顺序，并且所有分区行都是对等的。

  order_clause 的语法如下：

  *order_clause*:
      ORDER BY *expr* [ASC|DESC] [, *expr* [ASC|DESC]] ...
  

  每个 ORDER BY 表达式可选择跟随 ASC 或 DESC 表示排序方向。如果未指定方向，则默认为 ASC。对于升序排序，NULL 值排在最前面，对于降序排序，排在最后面。

  窗口定义中的 ORDER BY 适用于各个分区。要对整个结果集进行排序，请在查询顶层包含一个 ORDER BY。

• frame_clause: 一个框架是当前分区的子集，框架子句指定如何定义这个子集。框架子句有许多自己的子句。详情请参见 Section 14.20.3, “窗口函数框架规范”。

14.20.3 窗口函数帧规范

原文：dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html

与窗口函数一起使用的窗口的定义可以包括一个帧子句。帧是当前分区的子集，帧子句指定如何定义子集。

帧是相对于当前行确定的，这使得帧可以根据当前行在其分区中的位置移动。例如：

• 通过将帧定义为从分区开始到当前行的所有行，您可以为每行计算累计总和。

• 通过将帧定义为在当前行的两侧扩展*N*行，您可以计算滚动平均值。

以下查询演示了使用移动帧来计算每组时间排序的level值内的累计总和，以及从当前行和紧随其后的行计算的滚动平均值：

mysql> SELECT
         time, subject, val,
         SUM(val) OVER (PARTITION BY subject ORDER BY time
                        ROWS UNBOUNDED PRECEDING)
           AS running_total,
         AVG(val) OVER (PARTITION BY subject ORDER BY time
                        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
           AS running_average
       FROM observations;
+----------+---------+------+---------------+-----------------+
| time     | subject | val  | running_total | running_average |
+----------+---------+------+---------------+-----------------+
| 07:00:00 | st113   |   10 |            10 |          9.5000 |
| 07:15:00 | st113   |    9 |            19 |         14.6667 |
| 07:30:00 | st113   |   25 |            44 |         18.0000 |
| 07:45:00 | st113   |   20 |            64 |         22.5000 |
| 07:00:00 | xh458   |    0 |             0 |          5.0000 |
| 07:15:00 | xh458   |   10 |            10 |          5.0000 |
| 07:30:00 | xh458   |    5 |            15 |         15.0000 |
| 07:45:00 | xh458   |   30 |            45 |         20.0000 |
| 08:00:00 | xh458   |   25 |            70 |         27.5000 |
+----------+---------+------+---------------+-----------------+

对于running_average列，第一个和最后一个之后没有帧行。在这些情况下，AVG()计算可用行的平均值。

作为窗口函数使用的聚合函数在当前行帧上操作，这些非聚合窗口函数也是如此：

FIRST_VALUE()
LAST_VALUE()
NTH_VALUE()

标准 SQL 指定对整个分区操作的窗口函数不应具有帧子句。MySQL 允许这些函数具有帧子句，但会忽略它。即使指定了帧，这些函数也使用整个分区：

CUME_DIST()
DENSE_RANK()
LAG()
LEAD()
NTILE()
PERCENT_RANK()
RANK()
ROW_NUMBER()

如果提供了帧子句，则具有以下语法：

*frame_clause*:
    *frame_units* *frame_extent*

*frame_units*:
    {ROWS | RANGE}

在没有帧子句的情况下，默认帧取决于是否存在ORDER BY子句，如本节后面所述。

*frame_units*值表示当前行与帧行之间的关系类型：

• ROWS: 帧由开始和结束行位置定义。偏移量是当前行号与行号之间的差异。

• RANGE: 帧由值范围内的行定义。偏移量是当前行值与行值之间的差异。

*frame_extent*值表示帧的起始点和结束点。您可以仅指定帧的起始点（在这种情况下，当前行隐含为结束点），或使用BETWEEN指定帧的两个端点：

*frame_extent*:
    {*frame_start* | *frame_between*}

*frame_between*:
    BETWEEN *frame_start* AND *frame_end*

*frame_start*, *frame_end*: {
    CURRENT ROW
  | UNBOUNDED PRECEDING
  | UNBOUNDED FOLLOWING
  | *expr* PRECEDING
  | *expr* FOLLOWING
}

使用BETWEEN语法，*frame_start不能出现在frame_end*之后。

允许的*frame_start和frame_end*值具有以下含义：

• CURRENT ROW: 对于ROWS，边界是当前行。对于RANGE，边界是当前行的对等行。

• UNBOUNDED PRECEDING: 边界是第一个分区行。

• UNBOUNDED FOLLOWING: 边界是最后一个分区行。

• *expr* PRECEDING: 对于ROWS，边界是当前行之前的*expr行。对于RANGE，边界是具有值等于当前行值减去expr*的行；如果当前行值为NULL，则边界是该行的对等行。

  对于*expr* PRECEDING（和*expr* FOLLOWING），*expr可以是一个?参数标记（用于准备的语句中），一个非负数数字文字，或者形式为INTERVAL *val* *unit*的时间间隔。对于INTERVAL表达式，val指定非负的间隔值，unit是一个关键字，指示值应该以哪种单位解释。（有关允许的units*说明符的详细信息，请参阅第 14.7 节“日期和时间函数”中的DATE_ADD()函数的描述。）

  在数字或时间*expr*上的RANGE需要在数字或时间表达式上使用ORDER BY。

  有效的*expr* PRECEDING和*expr* FOLLOWING指示的示例：

  10 PRECEDING
  INTERVAL 5 DAY PRECEDING
  5 FOLLOWING
  INTERVAL '2:30' MINUTE_SECOND FOLLOWING
  

• *expr* FOLLOWING: 对于ROWS，边界是当前行之后的*expr行。对于RANGE，边界是具有值等于当前行值加上expr*的行；如果当前行值为NULL，则边界是该行的对等行。

  对于*expr*的允许值，请参阅*expr* PRECEDING的描述。

以下查询演示了FIRST_VALUE()，LAST_VALUE()和两个NTH_VALUE()实例：

mysql> SELECT
         time, subject, val,
         FIRST_VALUE(val)  OVER w AS 'first',
         LAST_VALUE(val)   OVER w AS 'last',
         NTH_VALUE(val, 2) OVER w AS 'second',
         NTH_VALUE(val, 4) OVER w AS 'fourth'
       FROM observations
       WINDOW w AS (PARTITION BY subject ORDER BY time
                    ROWS UNBOUNDED PRECEDING);
+----------+---------+------+-------+------+--------+--------+
| time     | subject | val  | first | last | second | fourth |
+----------+---------+------+-------+------+--------+--------+
| 07:00:00 | st113   |   10 |    10 |   10 |   NULL |   NULL |
| 07:15:00 | st113   |    9 |    10 |    9 |      9 |   NULL |
| 07:30:00 | st113   |   25 |    10 |   25 |      9 |   NULL |
| 07:45:00 | st113   |   20 |    10 |   20 |      9 |     20 |
| 07:00:00 | xh458   |    0 |     0 |    0 |   NULL |   NULL |
| 07:15:00 | xh458   |   10 |     0 |   10 |     10 |   NULL |
| 07:30:00 | xh458   |    5 |     0 |    5 |     10 |   NULL |
| 07:45:00 | xh458   |   30 |     0 |   30 |     10 |     30 |
| 08:00:00 | xh458   |   25 |     0 |   25 |     10 |     30 |
+----------+---------+------+-------+------+--------+--------+

每个函数使用当前帧中的行，根据所示的窗口定义，该帧从第一个分区行延伸到当前行。对于NTH_VALUE()调用，当前帧并不总是包括请求的行；在这种情况下，返回值为NULL。

在没有帧子句的情况下，默认帧取决于是否存在ORDER BY子句：

• 使用ORDER BY：默认帧包括从分区开始到当前行的所有行，包括当前行的所有对等行（根据ORDER BY子句与当前行相等的行）。默认等同于此帧规范：

  RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  

• 没有ORDER BY：默认帧包括所有分区行（因为没有ORDER BY，所有分区行都是对等的）。默认等同于此帧规范：

  RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  

因为默认帧取决于是否存在ORDER BY，为了获得确定性结果，向查询添加ORDER BY可能会改变结果。（例如，SUM()产生的值可能会改变。）为了获得相同的结果但按ORDER BY排序，提供一个明确的帧规范，无论是否存在ORDER BY都会使用。

当当前行值为NULL时，框架规范的含义可能不明显。假设是这种情况，以下示例说明了各种框架规范的应用：

• ORDER BY X ASC RANGE BETWEEN 10 FOLLOWING AND 15 FOLLOWING

  框架从NULL开始，止于NULL，因此只包括值为NULL的行。

• ORDER BY X ASC RANGE BETWEEN 10 FOLLOWING AND UNBOUNDED FOLLOWING

  框架从NULL开始，止于分区末尾。因为ASC排序将NULL值放在最前面，所以框架是整个分区。

• ORDER BY X DESC RANGE BETWEEN 10 FOLLOWING AND UNBOUNDED FOLLOWING

  框架从NULL开始，止于分区末尾。因为DESC排序将NULL值放在最后，所以框架只包括NULL值。

• ORDER BY X ASC RANGE BETWEEN 10 PRECEDING AND UNBOUNDED FOLLOWING

  框架从NULL开始，止于分区末尾。因为ASC排序将NULL值放在最前面，所以框架是整个分区。

• ORDER BY X ASC RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING

  框架从NULL开始，止于NULL，因此只包括值为NULL的行。

• ORDER BY X ASC RANGE BETWEEN 10 PRECEDING AND 1 PRECEDING

  框架从NULL开始，止于NULL，因此只包括值为NULL的行。

• ORDER BY X ASC RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING

  框架从分区开始，止于值为NULL的行。因为ASC排序将NULL值放在最前面，所以框架只包括NULL值。

14.20.4 命名窗口

原文：dev.mysql.com/doc/refman/8.0/en/window-functions-named-windows.html

可以通过在OVER子句中引用定义和命名窗口来定义窗口。为此，请使用WINDOW子句。如果在查询中存在，WINDOW子句位于HAVING和ORDER BY子句的位置之间，并具有以下语法：

WINDOW *window_name* AS (*window_spec*)
    [, *window_name* AS (*window_spec*)] ...

对于每个窗口定义，*window_name是窗口名称，window_spec*与OVER子句括号中给定的窗口规范类型相同，如第 14.20.2 节，“窗口函数概念和语法”中所述：

*window_spec*:
    [*window_name*] [*partition_clause*] [*order_clause*] [*frame_clause*]

对于多个OVER子句本应定义相同窗口的查询，WINDOW子句非常有用。相反，您可以一次定义窗口，为其命名，并在OVER子句中引用该名称。考虑以下查询，该查询多次定义相同窗口：

SELECT
  val,
  ROW_NUMBER() OVER (ORDER BY val) AS 'row_number',
  RANK()       OVER (ORDER BY val) AS 'rank',
  DENSE_RANK() OVER (ORDER BY val) AS 'dense_rank'
FROM numbers;

通过使用WINDOW一次性定义窗口并在OVER子句中引用窗口名称，可以更简单地编写查询：

SELECT
  val,
  ROW_NUMBER() OVER w AS 'row_number',
  RANK()       OVER w AS 'rank',
  DENSE_RANK() OVER w AS 'dense_rank'
FROM numbers
WINDOW w AS (ORDER BY val);

命名窗口还使得更容易尝试窗口定义以查看对查询结果的影响。您只需修改WINDOW子句中的窗口定义，而不是多个OVER子句定义。

如果OVER子句使用OVER (*window_name* ...)而不是OVER *window_name*，则可以通过添加其他子句修改命名窗口。例如，此查询定义了一个包含分区的窗口，并在OVER子句中使用ORDER BY以不同方式修改窗口：

SELECT
  DISTINCT year, country,
  FIRST_VALUE(year) OVER (w ORDER BY year ASC) AS first,
  FIRST_VALUE(year) OVER (w ORDER BY year DESC) AS last
FROM sales
WINDOW w AS (PARTITION BY country);

OVER子句只能向命名窗口添加属性，而不能修改它们。如果命名窗口定义包括分区、排序或帧属性，则引用窗口名称的OVER子句也不能包括相同类型的属性，否则将出现错误：

• 这种构造是允许的，因为窗口定义和引用的OVER子句不包含相同类型的属性：

  OVER (w ORDER BY country)
  ... WINDOW w AS (PARTITION BY country)
  

• 这种构造是不允许的，因为OVER子句为已经具有PARTITION BY的命名窗口指定了PARTITION BY：

  OVER (w PARTITION BY year)
  ... WINDOW w AS (PARTITION BY country)
  

命名窗口的定义本身可以以*window_name*开头。在这种情况下，允许前向和后向引用，但不允许循环：

• 这是允许的；它包含前向和后向引用，但没有循环：

  WINDOW w1 AS (w2), w2 AS (), w3 AS (w1)
  

• 这是不允许的，因为它包含一个循环：

  WINDOW w1 AS (w2), w2 AS (w3), w3 AS (w1)
  

14.20.5 窗口函数限制

原文：dev.mysql.com/doc/refman/8.0/en/window-function-restrictions.html

SQL 标准对窗口函数施加了一个限制，即它们不能在 UPDATE 或 DELETE 语句中用于更新行。在这些语句的子查询中使用这些函数（选择行）是允许的。

MySQL 不支持这些窗口函数特性：

• DISTINCT 语法用于聚合窗口函数。

• 嵌套窗口函数。

• 依赖于当前行值的动态帧端点。

解析器识别这些窗口构造，但仍不支持：

• GROUPS 帧单位说明符被解析，但会产生错误。只支持 ROWS 和 RANGE。

• 解析帧规范的 EXCLUDE 子句，但会产生错误。

• IGNORE NULLS 被解析，但会产生错误。只支持 RESPECT NULLS。

• FROM LAST 被解析，但会产生错误。只支持 FROM FIRST。

截至 MySQL 8.0.28，对于给定的 SELECT，支持最多 127 个窗口。请注意，单个查询可能使用多个 SELECT 子句，每个子句支持最多 127 个窗口。不同窗口的数量定义为命名窗口的总和以及任何作为任何窗口函数的 OVER 子句的一部分指定的隐式窗口。您还应该注意，使用大量窗口的查询可能需要增加默认线程堆栈大小（thread_stack 系统变量）。

14.21 Performance Schema Functions

原文：dev.mysql.com/doc/refman/8.0/en/performance-schema-functions.html

截至 MySQL 8.0.16，MySQL 包含内置的 SQL 函数，用于格式化或检索性能模式数据，并可用作对应的sys模式存储函数的等效函数。内置函数可以在任何模式中调用，无需限定符，不像sys函数，后者要求使用sys.模式限定符或sys为当前模式。

表 14.31 Performance Schema Functions

名称	描述	引入版本
FORMAT_BYTES()	将字节计数转换为带单位的值	8.0.16
FORMAT_PICO_TIME()	将皮秒时间转换为带单位的值	8.0.16
PS_CURRENT_THREAD_ID()	当前线程的性能模式线程 ID	8.0.16
PS_THREAD_ID()	给定线程的性能模式线程 ID	8.0.16

内置函数取代了相应的sys函数，后者已被��用；预计它们将在未来的 MySQL 版本中被移除。使用sys函数的应用程序应调整为使用内置函数，需要注意sys函数与内置函数之间的一些细微差异。有关这些差异的详细信息，请参阅本节中的函数描述。

• FORMAT_BYTES(*count*)

  给定一个数字字节计数，将其转换为人类可读格式，并返回一个由值和单位指示器组成的字符串。该字符串包含四舍五入到 2 位小数和至少 3 个有效数字的字节数。小于 1024 字节的数字表示为整数，不进行四舍五入。如果*count*为NULL，则返回NULL。

  单位指示器取决于字节计数参数的大小，如下表所示。

  参数值	结果单位	结果单位指示器
  最多 1023	字节	字节
  最多 1024² − 1	kibibytes	KiB
  最多 1024³ − 1	mebibytes	MiB
  最多 1024⁴ − 1	gibibytes	GiB
  最多 1024⁵ − 1	tebibytes	TiB
  最多 1024⁶ − 1	pebibytes	PiB
  1024⁶及以上	exbibytes	EiB

  mysql> SELECT FORMAT_BYTES(512), FORMAT_BYTES(18446644073709551615);
  +-------------------+------------------------------------+
  | FORMAT_BYTES(512) | FORMAT_BYTES(18446644073709551615) |
  +-------------------+------------------------------------+
  |  512 bytes        | 16.00 EiB                          |
  +-------------------+------------------------------------+
  

  FORMAT_BYTES() 在 MySQL 8.0.16 中添加。它可以用来替代sys模式中的format_bytes() Function")函数，需要注意以下区别：

  • FORMAT_BYTES() 使用EiB单位指示器。sys.format_bytes() Function")则不使用。

• FORMAT_PICO_TIME(*time_val*)

  给定一个数值型 Performance Schema 潜伏时间或等待时间（以皮秒为单位），将其转换为人类可读格式，并返回一个由值和单位指示符组成的字符串。字符串包含四舍五入到 2 位小数的十进制时间和至少 3 个有效数字。小于 1 纳秒的时间表示为整数，不进行四舍五入。

  如果 time_val 为 NULL，此函数返回 NULL。

  单位指示符取决于时间值参数的大小，如下表所示。

  参数值	结果单位	结果单位指示符
  最大为 10³ − 1	皮秒	ps
  最大为 10⁶ − 1	纳秒	ns
  最大为 10⁹ − 1	微秒	us
  最大为 10¹² − 1	毫秒	ms
  最大为 60×10¹² − 1	秒	s
  最大为 3.6×10¹⁵ − 1	分钟	min
  最大为 8.64×10¹⁶ − 1	小时	h
  大于等于 8.64×10¹⁶	天	d

  mysql> SELECT FORMAT_PICO_TIME(3501), FORMAT_PICO_TIME(188732396662000);
  +------------------------+-----------------------------------+
  | FORMAT_PICO_TIME(3501) | FORMAT_PICO_TIME(188732396662000) |
  +------------------------+-----------------------------------+
  | 3.50 ns                | 3.15 min                          |
  +------------------------+-----------------------------------+
  

  FORMAT_PICO_TIME() 在 MySQL 8.0.16 版本中添加。可以用来替代 sys 模式中的 format_time() Function") 函数，需要注意以下区别：

  • 为了表示分钟，sys.format_time() Function") 使用 m 单位指示符，而 FORMAT_PICO_TIME() 使用 min。

  • sys.format_time() Function") 使用 w（周）单位指示符。FORMAT_PICO_TIME() 不使用。

• PS_CURRENT_THREAD_ID()

  返回一个表示当前连接分配的 Performance Schema 线程 ID 的 BIGINT UNSIGNED 值。

  线程 ID 返回值是 Performance Schema 表中 THREAD_ID 列中给定类型的值。

  Performance Schema 配置对 PS_CURRENT_THREAD_ID() 的影响与对 PS_THREAD_ID() 的影响相同。详情请参阅该函数的描述。

  mysql> SELECT PS_CURRENT_THREAD_ID();
  +------------------------+
  | PS_CURRENT_THREAD_ID() |
  +------------------------+
  |                     52 |
  +------------------------+
  mysql> SELECT PS_THREAD_ID(CONNECTION_ID());
  +-------------------------------+
  | PS_THREAD_ID(CONNECTION_ID()) |
  +-------------------------------+
  |                            52 |
  +-------------------------------+
  

  PS_CURRENT_THREAD_ID() 在 MySQL 8.0.16 版本中添加。可以用作调用 sys 模式中的 ps_thread_id() Function") 函数的快捷方式，参数为 NULL 或 CONNECTION_ID()。

• PS_THREAD_ID(*connection_id*)

  给定连接 ID，返回一个表示分配给连接 ID 的性能模式线程 ID 的BIGINT UNSIGNED值，如果连接 ID 没有线程 ID 存在，则返回NULL。后者可能发生在未被检测的线程上，或者如果*connection_id*为NULL。

  连接 ID 参数是性能模式threads表中PROCESSLIST_ID列或SHOW PROCESSLIST输出中的Id列的值。

  线程 ID 返回值是性能模式表中THREAD_ID列中给定类型的值。

  性能模式配置会影响PS_THREAD_ID()的操作。 (这些备注也适用于PS_CURRENT_THREAD_ID().)

  • 禁用thread_instrumentation消费者会导致无法在线程级别收集和聚合统计数据，但不会影响PS_THREAD_ID()。

  • 如果performance_schema_max_thread_instances不为 0，则性能模式为线程统计数据分配内存，并为每个可用实例内存的线程分配一个内部 ID。如果有线程没有可用实例内存，PS_THREAD_ID()返回NULL；在这种情况下，Performance_schema_thread_instances_lost不为零。

  • 如果performance_schema_max_thread_instances为 0，则性能模式不分配线程内存，PS_THREAD_ID()返回NULL。

  • 如果性能模式本身被禁用，PS_THREAD_ID()会产生错误。

  mysql> SELECT PS_THREAD_ID(6);
  +-----------------+
  | PS_THREAD_ID(6) |
  +-----------------+
  |              45 |
  +-----------------+
  

  PS_THREAD_ID()在 MySQL 8.0.16 中添加。它可以代替sys模式的ps_thread_id() Function")函数，但要注意以下差异：

  • 使用NULL作为参数，sys.ps_thread_id() Function")函数返回当前连接的线程 ID，而PS_THREAD_ID()返回NULL。要获取当前连接的线程 ID，请使用PS_CURRENT_THREAD_ID()。

14.22 内部函数

原文：dev.mysql.com/doc/refman/8.0/en/internal-functions.html

表格 14.32 内部函数

名称	描述	引入版本
CAN_ACCESS_COLUMN()	仅供内部使用
CAN_ACCESS_DATABASE()	仅供内部使用
CAN_ACCESS_TABLE()	仅供内部使用
CAN_ACCESS_USER()	仅供内部使用	8.0.22
CAN_ACCESS_VIEW()	仅供内部使用
GET_DD_COLUMN_PRIVILEGES()	仅供内部使用
GET_DD_CREATE_OPTIONS()	仅供内部使用
GET_DD_INDEX_SUB_PART_LENGTH()	仅供内部使用
INTERNAL_AUTO_INCREMENT()	仅供内部使用
INTERNAL_AVG_ROW_LENGTH()	仅供内部使用
INTERNAL_CHECK_TIME()	仅供内部使用
INTERNAL_CHECKSUM()	仅供内部使用
INTERNAL_DATA_FREE()	仅供内部使用
INTERNAL_DATA_LENGTH()	仅供内部使用
INTERNAL_DD_CHAR_LENGTH()	仅供内部使用
INTERNAL_GET_COMMENT_OR_ERROR()	仅供内部使用
INTERNAL_GET_ENABLED_ROLE_JSON()	仅供内部使用	8.0.19
INTERNAL_GET_HOSTNAME()	仅供内部使用	8.0.19
INTERNAL_GET_USERNAME()	仅供内部使用	8.0.19
INTERNAL_GET_VIEW_WARNING_OR_ERROR()	仅供内部使用
INTERNAL_INDEX_COLUMN_CARDINALITY()	仅供内部使用
INTERNAL_INDEX_LENGTH()	仅供内部使用
INTERNAL_IS_ENABLED_ROLE()	仅供内部使用	8.0.19
INTERNAL_IS_MANDATORY_ROLE()	仅供内部使用	8.0.19
INTERNAL_KEYS_DISABLED()	仅供内部使用
INTERNAL_MAX_DATA_LENGTH()	仅供内部使用
INTERNAL_TABLE_ROWS()	仅供内部使用
INTERNAL_UPDATE_TIME()	仅供内部使用
名称	描述	引入版本

本节列出的函数仅供服务器内部使用。用户尝试调用它们会导致错误。

• CAN_ACCESS_COLUMN(*ARGS*)

• CAN_ACCESS_DATABASE(*ARGS*)

• CAN_ACCESS_TABLE(*ARGS*)

• CAN_ACCESS_USER(*ARGS*)

• CAN_ACCESS_VIEW(*ARGS*)

• GET_DD_COLUMN_PRIVILEGES(*ARGS*)

• GET_DD_CREATE_OPTIONS(*ARGS*)

• GET_DD_INDEX_SUB_PART_LENGTH(*ARGS*)

• INTERNAL_AUTO_INCREMENT(*ARGS*)

• INTERNAL_AVG_ROW_LENGTH(*ARGS*)

• INTERNAL_CHECK_TIME(*ARGS*)

• INTERNAL_CHECKSUM(*ARGS*)

• INTERNAL_DATA_FREE(*ARGS*)

• INTERNAL_DATA_LENGTH(*ARGS*)

• INTERNAL_DD_CHAR_LENGTH(*ARGS*)

• INTERNAL_GET_COMMENT_OR_ERROR(*ARGS*)

• INTERNAL_GET_ENABLED_ROLE_JSON(*ARGS*)

• INTERNAL_GET_HOSTNAME(*ARGS*)

• INTERNAL_GET_USERNAME(*ARGS*)

• INTERNAL_GET_VIEW_WARNING_OR_ERROR(*ARGS*)

• INTERNAL_INDEX_COLUMN_CARDINALITY(*ARGS*)

• INTERNAL_INDEX_LENGTH(*ARGS*)

• INTERNAL_IS_ENABLED_ROLE(*ARGS*)

• INTERNAL_IS_MANDATORY_ROLE(*ARGS*)

• INTERNAL_KEYS_DISABLED(*ARGS*)

• INTERNAL_MAX_DATA_LENGTH(*ARGS*)

• INTERNAL_TABLE_ROWS(*ARGS*)

• INTERNAL_UPDATE_TIME(*ARGS*)

• IS_VISIBLE_DD_OBJECT(*ARGS*)

14.23 杂项函数

原文：dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html

表 14.33 杂项函数

名称	描述
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
NAME_CONST()	使列具有给定名称
SLEEP()	休眠若干秒
UUID()	返回通用唯一标识符（UUID）
UUID_SHORT()	返回整数值的通用标识符
UUID_TO_BIN()	将字符串 UUID 转换为二进制
VALUES()	定义在插入期间要使用的值
名称	描述

• 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 接受查询：

  • 修改表，使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;
  

  在没有GROUP BY子句的情况下，可以使用ANY_VALUE()来引用聚合函数：

  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()可能是有意义的。

  有关更多讨论，请参阅 第 14.19.3 节，“MySQL 对 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() 接受一个或两个参数：

  • 一参数形式接受一个二进制 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 小数位，并将结果作为字符串返回。有关详细信息，请参阅 第 14.8 节，“字符串函数和运算符”。

• GROUPING(*expr* [, *expr*] ...)

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

  GROUPING()允许在选择列表、HAVING子句和（自 MySQL 8.0.12 起）ORDER BY子句中使用。

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

  假设表t1包含以下行，其中NULL表示类似于“其他”或“未知”的内容：

  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值和常规分组行中的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
  

  以下查询展示了单个参数的GROUPING()结果如何组合为多参数调用以生成位掩码值：

  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
    

  • 不应在HAVING子句中使用GROUP BY文字表达式作为GROUPING()参数。由于优化器评估GROUP BY和HAVING的时间差异，匹配可能成功，但GROUPING()的评估并不产生预期的结果。考虑以下查询：

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

    GROUPING()在整个HAVING子句之前对文字常量表达式进行评估，并返回 0。要检查是否受到影响，可以使用EXPLAIN并查找Extra列中的Impossible having。

  有关WITH ROLLUP和GROUPING()的更多信息，请参见第 14.19.2 节，“GROUP BY 修饰符”。

• INET_ATON(*expr*)

  给定一个作为字符串的 IPv4 网络地址的点分十进制表示，返回一个代表地址在网络字节顺序（大端）中的数值的整数。如果INET_ATON()不理解其参数，或者*expr*为NULL，则返回NULL。

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

  对于此示例，返回值计算为 10×256³ + 0×256² + 5×256 + 9。

  对于简短形式的 IP 地址（例如'127.1'表示'127.0.0.1'），INET_ATON()可能会或可能不会返回非NULL结果。因此，不应该对这样的地址使用INET_ATON()。

  注意

  为了存储INET_ATON()生成的值，请使用INT UNSIGNED列，而不是带有符号的INT。如果使用带符号的列，无法正确存储首个八位组大于 127 的 IP 地址对应的值。参见第 13.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数据类型的表示：IPv6 地址为VARBINARY(16)，IPv4 地址为VARBINARY(4)。如果参数不是有效地址，或者为NULL，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 映射地址。

  要将以INT - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT")值表示的 IPv4 地址*expr*转换为以VARBINARY值表示的 IPv6 地址，使用以下表达式：

  INET6_ATON(INET_NTOA(*expr*))
  

  例如：

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

  如果在mysql客户端中调用INET6_ATON()，二进制字符串将使用十六进制表示，具体取决于--binary-as-hex的值。有关该选项的更多信息，请参见第 6.5.1 节，“mysql — The MySQL Command-Line Client”。

• INET6_NTOA(*expr*)

  给定以二进制字符串形式表示的 IPv6 或 IPv4 网络地址，将返回连接字符集中的地址字符串表示。如果参数不是有效地址，或者为NULL，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 utf8mb3 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'
  

  如果在mysql客户端中调用INET6_NTOA()，二进制字符串将使用十六进制表示，具体取决于--binary-as-hex的值。有关该选项的更多信息，请参见第 6.5.1 节，“mysql — The MySQL Command-Line Client”。

• IS_IPV4(*expr*)

  如果参数作为字符串指定的 IPv4 地址有效，则返回 1，否则返回 0。如果*expr*为NULL，则返回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。

  如前述所示，IS_IPV4()对于何为有效的 IPv4 地址更为严格，因此对于需要对无效值进行强检查的应用程序可能很有用。或者，使用INET6_ATON()将 IPv4 地址转换为内部形式并检查NULL结果（表示无效地址）。INET6_ATON()在检查 IPv4 地址方面与IS_IPV4()一样强大。

• IS_IPV4_COMPAT(*expr*)

  此函数接受以二进制字符串形式表示的数字形式的 IPv6 地址，如INET6_ATON()返回的。如果参数是有效的 IPv4 兼容 IPv6 地址，则返回 1，否则返回 0（除非*expr*为NULL，在这种情况下函数返回NULL）。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，除非*expr*为NULL，在这种情况下函数返回NULL。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，除非*expr*为NULL，在这种情况下函数返回NULL。此函数不认为 IPv4 地址是有效的 IPv6 地址。

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

  对于给定的参数，如果IS_IPV6()返回 1，则INET6_ATON()返回非NULL。

• IS_UUID(*string_uuid*)

  如果参数是有效的字符串格式 UUID，则返回 1，如果参数不是有效的 UUID，则返回 0，如果参数为NULL，则返回NULL。

  “有效”意味着该值以可解析的格式存在。也就是说，它具有正确的长度并且仅包含允许的字符（十六进制数字以任何大小写字母形式，可选地包括短横线和大括号）。这种格式最常见：

  aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee
  

  还允许这些其他格式：

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

  有关值内字段的含义，请参阅UUID()函数描述。

  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()使列具有给定的名称。参数应为常量。

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

  此函数仅供内部使用。服务器在编写包含对本地程序变量的引用的存储程序语句时使用它，如第 27.7 节“存储程序二进制日志记录”中所述。您可能会在`mysqlbinlog**的输出中看到此函数。

  对于您的应用程序，您可以通过简单的别名来获得与刚刚显示的示例完全相同的结果，如下所示：

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

  有关列别名的更多信息，请参阅第 15.2.13 节“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 命名空间”（www.ietf.org/rfc/rfc4122.txt）生成的通用唯一标识符（UUID）。

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

  警告

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

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

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

  • 第四个数字在时间戳值失去单调性时保留时间上的唯一性（例如，由于夏令时）。

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

    仅在 FreeBSD、Linux 和 Windows 上考虑接口的 MAC 地址。在其他操作系统上，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 并返回结果。（IS_UUID()函数描述列出了允许的字符串 UUID 格式。）返回的二进制 UUID 是VARBINARY(16)值。如果 UUID 参数为 NULL，则返回值为 NULL。如果任何参数无效，则会发生错误。

  UUID_TO_BIN() 接受一个或两个参数：

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

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

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

    • 如果 swap_flag 为 1，则返回值的格式不同：时间低位和时间高位部分（分别为第一组和第三组十六进制数字）被交换。这将更快变化的部分移到右侧，并且如果结果存储在索引列中，可以提高索引效率。

  时间部分交换假定使用 UUID 版本 1 值，例如由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，则在将二进制 UUID 转换回字符串 UUID 时，还应将第二个参数设置为 1 传递给BIN_TO_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的值以十六进制表示。有关该选项的更多信息，请参阅 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。详见 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);
  

  重要提示

  在 MySQL 8.0.20 中，此用法已被弃用，并可能在将来的 MySQL 版本中被移除。请改用行别名或行和列别名。有关更多信息和示例，请参见 Section 15.2.7.2, “INSERT ... ON DUPLICATE KEY UPDATE Statement”。