MySQL8-中文参考-二十四-
MySQL8 中文参考(二十四)
14.18.2 与全局事务标识符(GTID)一起使用的函数
本节描述的函数用于基于 GTID 的复制。重要的是要记住,所有这些函数都将 GTID 集的字符串表示作为参数。因此,在使用它们时,GTID 集必须始终用引号括起来。有关更多信息,请参见GTID Sets。
两个 GTID 集的并集只是它们作为字符串的表示,用逗号连接在一起。换句话说,您可以定义一个非常简单的函数来获取两个 GTID 集的并集,类似于此处创建的函数:
CREATE FUNCTION GTID_UNION(g1 TEXT, g2 TEXT)
RETURNS TEXT DETERMINISTIC
RETURN CONCAT(g1,',',g2);
有关 GTID 及这些 GTID 函数在实践中的使用方式的更多信息,请参见第 19.1.3 节,“具有全局事务标识符的复制”。
表 14.26 GTID 函数
名称 | 描述 | 已弃用 |
---|---|---|
GTID_SUBSET() |
如果子集中的所有 GTID 也在集合中,则返回 true;否则返回 false。 | |
GTID_SUBTRACT() |
返回集合中不在子集中的所有 GTID。 | |
WAIT_FOR_EXECUTED_GTID_SET() |
等待给定的 GTID 在副本上执行。 | |
WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS() |
使用WAIT_FOR_EXECUTED_GTID_SET() 。 |
8.0.18 |
-
给定两组全局事务标识符
set1
和set2
,如果set1
中的所有 GTID 也在set2
中,则返回 true。如果set1
或set2
为NULL
,则返回NULL
。否则返回 false。与此函数一起使用的 GTID 集表示为字符串,如以下示例所示:
mysql> SELECT GTID_SUBSET('3E11FA47-71CA-11E1-9E33-C80AA9429562:23', -> '3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57')\G *************************** 1\. row *************************** GTID_SUBSET('3E11FA47-71CA-11E1-9E33-C80AA9429562:23', '3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57'): 1 1 row in set (0.00 sec) mysql> SELECT GTID_SUBSET('3E11FA47-71CA-11E1-9E33-C80AA9429562:23-25', -> '3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57')\G *************************** 1\. row *************************** GTID_SUBSET('3E11FA47-71CA-11E1-9E33-C80AA9429562:23-25', '3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57'): 1 1 row in set (0.00 sec) mysql> SELECT GTID_SUBSET('3E11FA47-71CA-11E1-9E33-C80AA9429562:20-25', -> '3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57')\G *************************** 1\. row *************************** GTID_SUBSET('3E11FA47-71CA-11E1-9E33-C80AA9429562:20-25', '3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57'): 0 1 row in set (0.00 sec)
-
给定两组全局事务标识符
set1
和set2
,仅返回set1
中不在set2
中的 GTID。如果set1
或set2
为NULL
,则返回NULL
。与此函数一起使用的所有 GTID 集表示为字符串,并且必须用引号括起来,如下例所示:
mysql> SELECT GTID_SUBTRACT('3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57', -> '3E11FA47-71CA-11E1-9E33-C80AA9429562:21')\G *************************** 1\. row *************************** GTID_SUBTRACT('3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57', '3E11FA47-71CA-11E1-9E33-C80AA9429562:21'): 3e11fa47-71ca-11e1-9e33-c80aa9429562:22-57 1 row in set (0.00 sec) mysql> SELECT GTID_SUBTRACT('3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57', -> '3E11FA47-71CA-11E1-9E33-C80AA9429562:20-25')\G *************************** 1\. row *************************** GTID_SUBTRACT('3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57', '3E11FA47-71CA-11E1-9E33-C80AA9429562:20-25'): 3e11fa47-71ca-11e1-9e33-c80aa9429562:26-57 1 row in set (0.00 sec) mysql> SELECT GTID_SUBTRACT('3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57', -> '3E11FA47-71CA-11E1-9E33-C80AA9429562:23-24')\G *************************** 1\. row *************************** GTID_SUBTRACT('3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57', '3E11FA47-71CA-11E1-9E33-C80AA9429562:23-24'): 3e11fa47-71ca-11e1-9e33-c80aa9429562:21-22:25-57 1 row in set (0.01 sec)
从一个 GTID 集中减去它自身会产生一个空集,如下所示:
mysql> SELECT GTID_SUBTRACT('3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57', -> '3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57')\G *************************** 1\. row *************************** GTID_SUBTRACT('3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57', '3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57'): 1 row in set (0.00 sec)
-
WAIT_FOR_EXECUTED_GTID_SET(*
gtid_set*[, *
timeout*])
等待服务器应用所有全局事务标识符包含在
gtid_set
中的事务,即直到条件 GTID_SUBSET(gtid_subset
,@@GLOBAL.gtid_executed
)成立为止。有关 GTID 集的定义,请参见第 19.1.3.1 节,“GTID 格式和存储”。如果指定了超时时间,并且在所有 GTID 集中的事务被应用之前经过
timeout
秒,函数将停止等待。timeout
是可选的,默认超时时间为 0 秒,在这种情况下,函数始终等待直到所有 GTID 集中的事务被应用。timeout
必须大于或等于 0;在严格 SQL 模式下运行时,负的timeout
值会立即被拒绝并显示错误(ER_WRONG_ARGUMENTS
);否则函数返回NULL
并发出警告。WAIT_FOR_EXECUTED_GTID_SET()
监视服务器上应用的所有 GTID,包括从所有复制通道和用户客户端到达的事务。它不考虑复制通道是否已启动或停止。更多信息,请参见第 19.1.3 节,“使用全局事务标识符进行复制”。
与此函数一起使用的 GTID 集表示为字符串,因此必须像以下示例中所示进行引用:
mysql> SELECT WAIT_FOR_EXECUTED_GTID_SET('3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5'); -> 0
有关 GTID 集的语法描述,请参见第 19.1.3.1 节,“GTID 格式和存储”。
对于
WAIT_FOR_EXECUTED_GTID_SET()
,返回值是查询的状态,其中 0 表示成功,1 表示超时。任何其他失败都会生成错误。gtid_mode
在任何客户端使用此函数等待 GTID 被应用时,不能被更改为 OFF。 -
WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS(*
gtid_set*[, *
timeout*][,*
channel*])
WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS()
已被弃用。请改用WAIT_FOR_EXECUTED_GTID_SET()
,它可以工作,无论复制通道或用户客户端通过哪个指定事务到达服务器。
14.18.3 异步复制通道故障转移函数
原文:
dev.mysql.com/doc/refman/8.0/en/replication-functions-async-failover.html
以下函数从 MySQL 8.0.22 开始适用于标准源到副本复制,从 MySQL 8.0.23 开始适用于 Group Replication,使您能够向复制通道的源列表中添加和删除复制源服务器。从 MySQL 8.0.27 开始,您还可以清除服务器的源列表。
表 14.27 故障转移通道函数
名称 | 描述 | 引入版本 |
---|---|---|
asynchronous_connection_failover_add_managed() |
将组成员源服务器配置信息添加到复制通道源列表 | 8.0.23 |
asynchronous_connection_failover_add_source() |
将源服务器配置信息添加到复制通道源列表 | 8.0.22 |
asynchronous_connection_failover_delete_managed() |
从复制通道源列表中删除托管组 | 8.0.23 |
asynchronous_connection_failover_delete_source() |
从复制通道源列表中删除源服务器 | 8.0.22 |
asynchronous_connection_failover_reset() |
删除与组复制异步故障转移相关的所有设置 | 8.0.27 |
异步连接故障转移机制在复制连接(源到副本)失败后,自动从适当列表中为新源建立异步连接。从 MySQL 8.0.23 开始,如果当前连接的源不是组中具有最高加权优先级的源,则连接也会更改。对于作为托管组的一部分定义的 Group Replication 源服务器,如果当前连接的源离开组或不再占多数,连接也会切换到另一个组成员。有关该机制的更多信息,请参见第 19.4.9 节,“使用异步连接故障转移切换源和副本”。
源列表存储在mysql.replication_asynchronous_connection_failover
和mysql.replication_asynchronous_connection_failover_managed
表中,并且可以在性能模式replication_asynchronous_connection_failover
表中查看。
如果复制通道位于启用了副本之间故障切换的组的 Group Replication 主服务器上,则当它们加入或通过任何方法更新时,源列表将广播给所有组成员。副本之间的故障切换由mysql_start_failover_channels_if_primary
成员操作控制,默认情况下启用,并且可以使用group_replication_disable_member_action
函数禁用。
-
asynchronous_connection_failover_add_managed()
为受管组(Group Replication 组成员)的复制源服务器添加配置信息到复制通道的源列表中。您只需要添加一个组成员。副本会自动从当前组成员中添加其余成员,然后根据成员变化保持源列表更新。
语法:
asynchronous_connection_failover_add_managed(*channel*, *managed_type*, *managed_name*, *host*, *port*, *network_namespace*, *primary_weight*, *secondary_weight*)
参数:
-
channel
: 此复制源服务器所属的复制通道。 -
managed_type
: 异步连接故障转移机制必须为此服务器提供的受管服务类型。当前唯一接受的值是GroupReplication
。 -
managed_name
: 服务器所属的受管组的标识符。对于GroupReplication
受管服务,标识符是group_replication_group_name
系统变量的值。 -
host
: 此复制源服务器的主机名。 -
port
: 此复制源服务器的端口号。 -
network_namespace
: 此复制源服务器的网络命名空间。指定空字符串,因为此参数保留供将来使用。 -
primary_weight
: 当作为受管组的主服务器时,此复制源服务器在复制通道源列表中的优先级。权重范围为 1 到 100,100 为最高。对于主服务器,80 是一个合适的权重。如果当前连接的源不是组中权重最高的源,则异步连接故障转移机制会激活。假设您设置了受管组,为主服务器分配较高的权重,为次要服务器分配较低的权重,当主服务器更改时,其权重增加,副本会切换到连接到主服务器。 -
secondary_weight
: 当作为托管组中的次要角色时,此复制源服务器在复制通道源列表中的优先级。权重范围为 1 到 100,100 为最高。对于次要角色,60 是一个合适的权重。
返回值:
包含操作结果的字符串,例如操作是否成功。
示例:
SELECT asynchronous_connection_failover_add_managed('channel2', 'GroupReplication', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', '127.0.0.1', 3310, '', 80, 60); +----------------------------------------------------------------------------------------------------------------------------------------------------+ | asynchronous_connection_failover_add_source('channel2', 'GroupReplication', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', '127.0.0.1', 3310, '', 80, 60) | +----------------------------------------------------------------------------------------------------------------------------------------------------+ | Source managed configuration details successfully inserted. | +----------------------------------------------------------------------------------------------------------------------------------------------------+
更多信息,请参见 Section 19.4.9, “使用异步连接故障转移切换源和副本”。
-
-
asynchronous_connection_failover_add_source()
为复制通道的源列表添加复制源服务器的配置信息。
语法:
asynchronous_connection_failover_add_source(*channel*, *host*, *port*, *network_namespace*, *weight*)
参数:
-
channel
: 此复制源服务器所属源列表的复制通道。 -
host
: 此复制源服务器的主机名。 -
port
: 此复制源服务器的端口号。 -
network_namespace
: 此复制源服务器的网络命名空间。请指定一个空字符串,因为此参数保留供将来使用。 -
weight
: 此复制源服务器在复制通道源列表中的优先级。优先级范围为 1 到 100,100 为最高,50 为默认值。当异步连接故障转移机制激活时,通道源列表中具有最高优先级设置的备用源将被选择用于第一次连接尝试。如果此尝试不起作用,则副本将按优先级降序尝试所有列出的源,然后从最高优先级源重新开始。如果多个源具有相同的优先级,则副本会随机排序它们。从 MySQL 8.0.23 开始,如果当前连接的源不是组中权重最高的源,则异步连接故障转移机制将激活。
返回值:
包含操作结果的字符串,例如操作是否成功。
示例:
SELECT asynchronous_connection_failover_add_source('channel2', '127.0.0.1', 3310, '', 80); +-------------------------------------------------------------------------------------------------+ | asynchronous_connection_failover_add_source('channel2', '127.0.0.1', 3310, '', 80) | +-------------------------------------------------------------------------------------------------+ | Source configuration details successfully inserted. | +-------------------------------------------------------------------------------------------------+
更多信息,请参见 Section 19.4.9, “使用异步连接故障转移切换源和副本”。
-
-
asynchronous_connection_failover_delete_managed()
从复制通道的源列表中删除整个托管组。使用此函数时,托管组中定义的所有复制源服务器都将从通道的源列表中移除。
语法:
asynchronous_connection_failover_delete_managed(*channel*, *managed_name*)
参数:
-
channel
: 此复制源服务器曾经是其所属源列表的复制通道。 -
managed_name
:服务器所属的托管组的标识符。对于GroupReplication
托管服务,标识符是group_replication_group_name
系统变量的值。
返回值:
包含操作结果的字符串,例如操作是否成功。
示例:
SELECT asynchronous_connection_failover_delete_managed('channel2', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa'); +-----------------------------------------------------------------------------------------------------+ | asynchronous_connection_failover_delete_managed('channel2', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa') | +-----------------------------------------------------------------------------------------------------+ | Source managed configuration details successfully deleted. | +-----------------------------------------------------------------------------------------------------+
有关更多信息,请参见第 19.4.9 节,“使用异步连接故障转移切换源和副本”。
-
-
asynchronous_connection_failover_delete_source()
从复制通道的源列表中删除复制源服务器的配置信息。
语法:
asynchronous_connection_failover_delete_source(*channel*, *host*, *port*, *network_namespace*)
参数:
-
channel
:此复制源服务器所属源列表的复制通道。 -
host
:此复制源服务器的主机名。 -
port
:此复制源服务器的端口号。 -
network_namespace
:此复制源服务器的网络命名空间。指定空字符串,因为此参数保留供将来使用。
返回值:
包含操作结果的字符串,例如操作是否成功。
示例:
SELECT asynchronous_connection_failover_delete_source('channel2', '127.0.0.1', 3310, ''); +------------------------------------------------------------------------------------------------+ | asynchronous_connection_failover_delete_source('channel2', '127.0.0.1', 3310, '') | +------------------------------------------------------------------------------------------------+ | Source configuration details successfully deleted. | +------------------------------------------------------------------------------------------------+
有关更多信息,请参见第 19.4.9 节,“使用异步连接故障转移切换源和副本”。
-
-
asynchronous_connection_failover_reset()
删除与异步连接故障转移机制相关的所有设置。该函数清除性能模式表
replication_asynchronous_connection_failover
和replication_asynchronous_connection_failover_managed
。asynchronous_connection_failover_reset()
只能在当前不属于任何组且没有任何复制通道运行的服务器上使用。您可以使用此函数清理不再在托管组中使用的服务器。语法:
STRING asynchronous_connection_failover_reset()
参数:
无。
返回值:
包含操作结果的字符串,例如操作是否成功。
示例:
mysql> SELECT asynchronous_connection_failover_reset(); +-------------------------------------------------------------------------+ | asynchronous_connection_failover_reset() | +-------------------------------------------------------------------------+ | The UDF asynchronous_connection_failover_reset() executed successfully. | +-------------------------------------------------------------------------+ 1 row in set (0.00 sec)
欲了解更多信息,请参阅第 19.4.9 节,“使用异步连接故障转移切换源和副本”。
14.18.4 基于位置的同步函数
原文:
dev.mysql.com/doc/refman/8.0/en/replication-functions-synchronization.html
此部分列出的函数用于控制 MySQL 复制中源和副本服务器的基于位置的同步。
表 14.28 位置同步函数
名称 | 描述 | 引入版本 | 弃用版本 |
---|---|---|---|
MASTER_POS_WAIT() |
阻塞,直到副本已读取并应用了指定位置之前的所有更新 | 8.0.26 | |
SOURCE_POS_WAIT() |
阻塞,直到副本已读取并应用了指定位置之前的所有更新 | 8.0.26 |
-
MASTER_POS_WAIT(*
log_name*,*
log_pos*[,*
timeout*][,*
channel*])
这个函数用于控制源和副本同步。它会阻塞,直到副本已经读取并应用了源二进制日志中指定位置之前的所有更新。从 MySQL 8.0.26 开始,
MASTER_POS_WAIT()
已被弃用,应该使用别名SOURCE_POS_WAIT()
。在 MySQL 8.0.26 之前的版本中,请使用MASTER_POS_WAIT()
。返回值是副本需要等待的日志事件数量,以便前进到指定位置。如果复制 SQL 线程未启动、副本的源信息未初始化、参数不正确或发生错误,则函数返回
NULL
。如果超过超时时间,则返回-1
。如果MASTER_POS_WAIT()
等待时复制 SQL 线程停止,则函数返回NULL
。如果副本已经超过指定位置,则函数立即返回。如果二进制日志文件位置被标记为无效,函数会等待直到知道有效的文件位置。当为复制通道设置了
CHANGE REPLICATION SOURCE TO
选项GTID_ONLY
,并且服务器重新启动或复制停止时,二进制日志文件位置可以被标记为无效。在成功应用超过给定文件位置的事务后,文件位置变为有效。如果应用程序未达到指定位置,则函数会等待直到超时。使用SHOW REPLICA STATUS
语句检查二进制日志文件位置是否被标记为无效。在多线程复制中,该函数会等待直到达到由
replica_checkpoint_group
、slave_checkpoint_group
、replica_checkpoint_period
或slave_checkpoint_period
系统变量设置的限制时间,当调用检查点操作更新复制品状态时。根据系统变量的设置,该函数可能会在指定位置到达后的一段时间后返回。如果使用了二进制日志事务压缩,并且指定位置处的事务负载已被压缩(作为
Transaction_payload_event
),该函数会等待直到整个事务被读取和应用,并且位置已更新。如果指定了
timeout
值,MASTER_POS_WAIT()
在经过timeout
秒后停止等待。timeout
必须大于或等于 0。(当服务器运行在严格 SQL 模式下时,负的timeout
值会立即被拒绝,并显示ER_WRONG_ARGUMENTS
;否则函数返回NULL
,并发出警告。)可选的
channel
值使您能够命名函数应用于哪个复制通道。有关更多信息,请参见第 19.2.2 节,“复制通道”。此函数对于基于语句的复制是不安全的。如果在
binlog_format
设置为STATEMENT
时使用此函数,将记录警告。 -
SOURCE_POS_WAIT(*
log_name*,*
log_pos*[,*
timeout*][,*
channel*])
此函数用于控制源-复制品同步。它会阻塞,直到复制品已读取并应用源二进制日志中指定位置的所有更新。从 MySQL 8.0.26 开始,使用
SOURCE_POS_WAIT()
代替从该版本开始弃用的MASTER_POS_WAIT()
。在 MySQL 8.0.26 之前的版本中,请使用MASTER_POS_WAIT()
。返回值是复制必须等待的日志事件数以前进到指定位置。如果复制 SQL 线程未启动,复制源信息未初始化,参数不正确或发生错误,则函数返回
NULL
。如果超时已超过,则返回-1
。如果SOURCE_POS_WAIT()
在等待时复制 SQL 线程停止,则函数返回NULL
。如果复制已超过指定位置,则函数立即返回。如果二进制日志文件位置被标记为无效,该函数将等待直到已知有效文件位置。当为复制通道设置了
CHANGE REPLICATION SOURCE TO
选项GTID_ONLY
,并且服务器重新启动或复制停止时,二进制日志文件位置可以被标记为无效。在成功应用超过给定文件位置的事务后,文件位置变为有效。如果应用程序未达到指定位置,则函数将等待超时。使用SHOW REPLICA STATUS
语句检查二进制日志文件位置是否已标记为无效。在多线程复制中,当调用检查点操作以更新复制状态时,该函数将等待直到达到
replica_checkpoint_group
或replica_checkpoint_period
系统变量设置的限制。根据系统变量的设置,因此该函数可能在达到指定位置后的一段时间后返回。如果使用二进制日志事务压缩,并且指定位置处的事务有效载荷已压缩(作为
Transaction_payload_event
),则函数将等待直到整个事务已被读取和应用,并且位置已更新。如果指定了
timeout
值,则SOURCE_POS_WAIT()
在timeout
秒已过时停止等待。timeout
必须大于或等于 0。 (在严格 SQL 模式下,负的timeout
值将立即被ER_WRONG_ARGUMENTS
拒绝;否则函数返回NULL
,并引发警告。)可选的
channel
值使您能够命名函数应用于哪个复制通道。有关更多信息,请参见 Section 19.2.2, “Replication Channels”。当
binlog_format
设置为STATEMENT
时,此函数不适用于基于语句的复制,如果您在这种情况下使用此函数,将会记录警告。
14.19 聚合函数
原文:
dev.mysql.com/doc/refman/8.0/en/aggregate-functions-and-modifiers.html
14.19.1 聚合函数描述
14.19.2 GROUP BY 修饰符
14.19.3 MySQL 对 GROUP BY 的处理
14.19.4 功能依赖的检测
聚合函数操作在一组值上。它们通常与GROUP BY
子句一起使用,将值分组为子集。本节描述了大多数聚合函数。有关操作几何值的聚合函数的信息,请参见第 14.16.12 节,“空间聚合函数”。
14.19.1 聚合函数描述
本节描述了对值集合进行操作的聚合函数。它们通常与GROUP BY
子句一起使用,将值分组为子集。
表 14.29 聚合函数
名称 | 描述 |
---|---|
AVG() |
返回参数的平均值 |
BIT_AND() |
返回按位与 |
BIT_OR() |
返回按位或 |
BIT_XOR() |
返回按位异或 |
COUNT() |
返回返回的行数计数 |
COUNT(DISTINCT) |
返回不同值的数量 |
GROUP_CONCAT() |
返回连接的字符串 |
JSON_ARRAYAGG() |
将结果集作为单个 JSON 数组返回 |
JSON_OBJECTAGG() |
将结果集作为单个 JSON 对象返回 |
MAX() |
返回最大值 |
MIN() |
返回最小值 |
STD() |
返回总体标准偏差 |
STDDEV() |
返回总体标准偏差 |
STDDEV_POP() |
返回总体标准偏差 |
STDDEV_SAMP() |
返回样本标准偏差 |
SUM() |
返回总和 |
VAR_POP() |
返回总体标准方差 |
VAR_SAMP() |
返回样本方差 |
VARIANCE() |
返回总体标准方差 |
名称 | 描述 |
除非另有说明,聚合函数会忽略NULL
值。
如果在不包含GROUP BY
子句的语句中使用聚合函数,则相当于对所有行进行分组。有关更多信息,请参见第 14.19.3 节,“MySQL GROUP BY 的处理”。
大多数聚合函数都可以作为窗口函数使用。可以这样使用的函数在其语法描述中通过[*
over_clause*]
表示,表示一个可选的OVER
子句。over_clause
在第 14.20.2 节,“窗口函数概念和语法”中描述,该节还包括有关窗口函数使用的其他信息。
对于数值参数,方差和标准差函数返回一个DOUBLE
值。SUM()
和AVG()
函数对于精确值参数(整数或DECIMAL
)返回一个DECIMAL
值,对于近似值参数(FLOAT
或DOUBLE
)返回一个DOUBLE
值。
SUM()
和AVG()
聚合函数不适用于时间值。(它们将值转换为数字,丢失第一个非数字字符后的所有内容。)为解决此问题,将值转换为数字单位,执行聚合操作,然后将其转换回时间值。示例:
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(*time_col*))) FROM *tbl_name*;
SELECT FROM_DAYS(SUM(TO_DAYS(*date_col*))) FROM *tbl_name*;
诸如SUM()
或AVG()
这样期望数值参数的函数,如有必要会将参数转换为数字。对于SET
或ENUM
值,转换操作会使用底层的数值。
BIT_AND()
、BIT_OR()
和BIT_XOR()
聚合函数执行位操作。在 MySQL 8.0 之前,位函数和运算符需要BIGINT
(64 位整数)参数,并返回BIGINT
值,因此它们的最大范围为 64 位。非BIGINT
参数在执行操作之前被转换为BIGINT
,可能会发生截断。
在 MySQL 8.0 中,位函数和运算符允许二进制字符串类型参数(BINARY
、VARBINARY
和BLOB
类型),并返回相同类型的值,这使它们能够接受参数并生成大于 64 位的返回值。有关位操作的参数评估和结果类型的讨论,请参见第 14.12 节“位函数和运算符”中的介绍性讨论。
-
AVG([DISTINCT] *
expr*) [*
over_clause*]
返回
*
expr*
的平均值。DISTINCT
选项可用于返回expr
的不同值的平均值。如果没有匹配的行,
AVG()
返回NULL
。如果expr
为NULL
,该函数也返回NULL
。如果
over_clause
存在,则此函数作为窗口函数执行。over_clause
如第 14.20.2 节“窗口函数概念和语法”中所述;它不能与DISTINCT
一起使用。mysql> SELECT student_name, AVG(test_score) FROM student GROUP BY student_name;
-
BIT_AND(*
expr*) [*
over_clause*]
返回
expr
中所有位的按位AND
。结果类型取决于函数参数值是作为二进制字符串还是数字进行评估:
-
当参数值具有二进制字符串类型且参数不是十六进制文字、位文字或
NULL
文字时,进行二进制字符串评估。否则进行数值评估,必要时将参数值转换为无符号 64 位整数。 -
二进制字符串评估会产生与参数值相同长度的二进制字符串。如果参数值长度不相等,则会出现
ER_INVALID_BITWISE_OPERANDS_SIZE
错误。如果参数大小超过 511 字节,则会出现ER_INVALID_BITWISE_AGGREGATE_OPERANDS_SIZE
错误。数值评估会产生一个无符号 64 位整数。
如果没有匹配的行,
BIT_AND()
返回一个中性值(所有位设置为 1),其长度与参数值相同。NULL
值不会影响结果,除非所有值都是NULL
。在这种情况下,结果是一个中性值,其长度与参数值相同。有关参数评估和结果类型的更多信息,请参见第 14.12 节,“位函数和运算符”中的介绍性讨论。
如果从mysql客户端中调用
BIT_AND()
,二进制字符串结果将根据--binary-as-hex
的值使用十六进制表示。有关该选项的更多信息,请参见第 6.5.1 节,“mysql — MySQL 命令行客户端”。从 MySQL 8.0.12 开始,如果存在
over_clause
,此函数将作为窗口函数执行。over_clause
的描述如第 14.20.2 节,“窗口函数概念和语法”中所述。 -
-
BIT_OR(*
expr*) [*
over_clause*]
��回
expr
中所有位的按位OR
。结果类型取决于函数参数值是作为二进制字符串还是数字进行评估:
-
当参数值具有二进制字符串类型且参数不是十六进制文字、位文字或
NULL
文字时,会发生二进制字符串评估。否则会发生数值评估,必要时将参数值转换为无符号 64 位整数。 -
二进制字符串评估会产生与参数值相同长度的二进制字符串。如果参数值长度不相等,则会出现
ER_INVALID_BITWISE_OPERANDS_SIZE
错误。如果参数大小超过 511 字节,则会出现ER_INVALID_BITWISE_AGGREGATE_OPERANDS_SIZE
错误。数值评估会产生一个无符号 64 位整数。
如果没有匹配的行,
BIT_OR()
返回一个中性值(所有位设置为 0),其长度与参数值相同。NULL
值不会影响结果,除非所有值都是NULL
。在这种情况下,结果是一个中性值,其长度与参数值相同。有关参数评估和结果类型的更多信息,请参见 Section 14.12, “Bit Functions and Operators” 中的介绍性讨论。
如果从 mysql 客户端内调用
BIT_OR()
,二进制字符串结果将根据--binary-as-hex
的值以十六进制表示。有关该选项的更多信息,请参见 Section 6.5.1, “mysql — The MySQL Command-Line Client”。从 MySQL 8.0.12 开始,如果存在
over_clause
,此函数将作为窗口函数执行。over_clause
如 Section 14.20.2, “Window Function Concepts and Syntax” 中所述。 -
-
BIT_XOR(*
expr*) [*
over_clause*]
返回
expr
中所有位的按位XOR
。结果类型取决于函数参数值是作为二进制字符串还是���字进行评估:
-
当参数值具有二进制字符串类型且参数不是十六进制文字、位文字或
NULL
文字时,进行二进制字符串评估。否则进行数字评估,必要时将参数值转换为无符号 64 位整数。 -
二进制字符串评估产生与参数值相同长度的二进制字符串。如果参数值长度不相等,则会出现
ER_INVALID_BITWISE_OPERANDS_SIZE
错误。如果参数大小超过 511 字节,则会出现ER_INVALID_BITWISE_AGGREGATE_OPERANDS_SIZE
错误。数字评估产生一个无符号 64 位整数。
如果没有匹配的行,
BIT_XOR()
返回一个中性值(所有位设置为 0),其长度与参数值相同。NULL
值不会影响结果,除非所有值都是NULL
。在这种情况下,结果是一个中性值,其长度与参数值相同。有关参数评估和结果类型的更多信息,请参见 Section 14.12, “Bit Functions and Operators” 中的介绍性讨论。
如果从 mysql 客户端调用
BIT_XOR()
,二进制字符串结果将使用十六进制表示,取决于--binary-as-hex
的值。有关该选项的更多信息,请参见 Section 6.5.1, “mysql — The MySQL Command-Line Client”。截至 MySQL 8.0.12,如果存在
over_clause
,此函数将作为窗口函数执行。over_clause
如 Section 14.20.2, “Window Function Concepts and Syntax” 中所述。 -
-
返回
SELECT
语句检索的行中expr
的非NULL
值的计数。结果是一个BIGINT
- INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT") 值。如果没有匹配的行,
COUNT()
返回0
。COUNT(NULL)
返回 0。如果存在
over_clause
,此函数将作为窗口函数执行。over_clause
如 Section 14.20.2, “Window Function Concepts and Syntax” 中所述。mysql> SELECT student.student_name,COUNT(*) FROM student,course WHERE student.student_id=course.student_id GROUP BY student_name;
COUNT(*)
有些不同,它返回检索到的行数,无论它们是否包含NULL
值。对于像
InnoDB
这样的事务性存储引擎,存储精确的行计数是有问题的。可能同时发生多个事务,每个事务可能会影响计数。InnoDB
不会保留表中行的内部计数,因为并发事务可能同时“看到”不同数量的行。因此,SELECT COUNT(*)
语句仅计算当前事务可见的行数。截至 MySQL 8.0.13,对于
InnoDB
表,SELECT COUNT(*) FROM *
tbl_name*
查询性能在没有额外子句(如WHERE
或GROUP BY
)的情况下针对单线程工作负载进行了优化。InnoDB
通过遍历最小可用的次要索引来处理SELECT COUNT(*)
语句,除非索引或优化器提示指示优化器使用不同的索引。如果不存在次要索引,则InnoDB
通过扫描聚簇索引来处理SELECT COUNT(*)
语句。处理
SELECT COUNT(*)
语句需要一些时间,如果索引记录不完全在缓冲池中。为了更快地计数,创建一个计数器表,并让您的应用程序根据其执行的插入和删除更新它。然而,在数千个并发事务启动对同一计数器表的更新的情况下,这种方法可能不会很好地扩展。如果近似行数足够,使用SHOW TABLE STATUS
。InnoDB
处理SELECT COUNT(*)
和SELECT COUNT(1)
操作的方式相同。没有性能差异。对于
MyISAM
表,如果SELECT
从一个表中检索,没有检索到其他列,并且没有WHERE
子句,则COUNT(*)
被优化为非常快速地返回。例如:mysql> SELECT COUNT(*) FROM student;
这种优化仅适用于
MyISAM
表,因为对于这种存储引擎存储了精确的行数计数,并且可以非常快速地访问。如果第一列被定义为NOT NULL
,COUNT(1)
也仅受到相同优化的影响。 -
COUNT(DISTINCT *
expr*,[*
expr*...])
返回具有不同非
NULL
expr
值的行数计数。如果没有匹配的行,
COUNT(DISTINCT)
返回0
。mysql> SELECT COUNT(DISTINCT results) FROM student;
在 MySQL 中,您可以通过给出表达式列表来获取不包含
NULL
的不同表达式组合的数量。在标准 SQL 中,您将不得不对COUNT(DISTINCT ...)
中的所有表达式进行串联。 -
GROUP_CONCAT(*
expr*)
此函数返回一个字符串结果,其中包含来自一组的连接的非
NULL
值。如果没有非NULL
值,则返回NULL
。完整语法如下:GROUP_CONCAT([DISTINCT] *expr* [,*expr* ...] [ORDER BY {*unsigned_integer* | *col_name* | *expr*} [ASC | DESC] [,*col_name* ...]] [SEPARATOR *str_val*])
mysql> SELECT student_name, GROUP_CONCAT(test_score) FROM student GROUP BY student_name;
或:
mysql> SELECT student_name, GROUP_CONCAT(DISTINCT test_score ORDER BY test_score DESC SEPARATOR ' ') FROM student GROUP BY student_name;
在 MySQL 中,您可以获取表达式组合的连接值。要消除重复值,请使用
DISTINCT
子句。要对结果中的值进行排序,请使用ORDER BY
子句。要以相反顺序排序,请在ORDER BY
子句中按照您要排序的列的名称后添加DESC
(降序)关键字。默认是升序;这可以通过使用ASC
关键字明确指定。在组中值之间的默认分隔符是逗号(,
)。要明确指定分隔符,请使用SEPARATOR
,后跟应在组值之间插入的字符串文字值。要完全消除分隔符,请指定SEPARATOR ''
。结果被截断为由
group_concat_max_len
系统变量给出的最大长度,其默认值为 1024。该值可以设置更高,尽管返回值的有效最大长度受max_allowed_packet
的值限制。在运行时更改group_concat_max_len
值的语法如下,其中val
是无符号整数:SET [GLOBAL | SESSION] group_concat_max_len = *val*;
返回值是一个非二进制或二进制字符串,取决于参数是非二进制还是二进制字符串。结果类型是
TEXT
或BLOB
,除非group_concat_max_len
小于或等于 512,此时结果类型为VARCHAR
或VARBINARY
。如果从mysql客户端内调用
GROUP_CONCAT()
,二进制字符串结果将使用十六进制表示,取决于--binary-as-hex
的值。有关该选项的更多信息,请参见第 6.5.1 节,“mysql — MySQL 命令行客户端”。另请参阅
CONCAT()
和CONCAT_WS()
:第 14.8 节,“字符串函数和运算符”。 -
JSON_ARRAYAGG(*
col_or_expr*) [*
over_clause*]
将结果集聚合为一个单一的
JSON
数组,其元素由行组成。此数组中元素的顺序是未定义的。该函数作用于一个列或评估为单个值的表达式。如果结果不包含行或出现错误,则返回NULL
。如果col_or_expr
为NULL
,则函数返回一个 JSON 数组,其中包含[null]
元素。从 MySQL 8.0.14 开始,如果存在
over_clause
,此函数将作为窗口函数执行。over_clause
的描述如第 14.20.2 节,“窗口函数概念和语法”中所述。mysql> SELECT o_id, attribute, value FROM t3; +------+-----------+-------+ | o_id | attribute | value | +------+-----------+-------+ | 2 | color | red | | 2 | fabric | silk | | 3 | color | green | | 3 | shape | square| +------+-----------+-------+ 4 rows in set (0.00 sec) mysql> SELECT o_id, JSON_ARRAYAGG(attribute) AS attributes -> FROM t3 GROUP BY o_id; +------+---------------------+ | o_id | attributes | +------+---------------------+ | 2 | ["color", "fabric"] | | 3 | ["color", "shape"] | +------+---------------------+ 2 rows in set (0.00 sec)
-
JSON_OBJECTAGG(*
key*, *
value*) [*
over_clause*]
接受两个列名或表达式作为参数,第一个用作键,第二个用作值,并返回一个包含键值对的 JSON 对象。如果结果不包含行,则返回
NULL
,或者在出现错误时返回。如果任何键名为NULL
或参数数量不等于 2,则会发生错误。从 MySQL 8.0.14 开始,如果存在
over_clause
,此函数将作为窗口函数执行。over_clause
如第 14.20.2 节,“窗口函数概念和语法”中所述。mysql> SELECT o_id, attribute, value FROM t3; +------+-----------+-------+ | o_id | attribute | value | +------+-----------+-------+ | 2 | color | red | | 2 | fabric | silk | | 3 | color | green | | 3 | shape | square| +------+-----------+-------+ 4 rows in set (0.00 sec) mysql> SELECT o_id, JSON_OBJECTAGG(attribute, value) -> FROM t3 GROUP BY o_id; +------+---------------------------------------+ | o_id | JSON_OBJECTAGG(attribute, value) | +------+---------------------------------------+ | 2 | {"color": "red", "fabric": "silk"} | | 3 | {"color": "green", "shape": "square"} | +------+---------------------------------------+ 2 rows in set (0.00 sec)
重复键处理。 当此函数的结果被规范化时,具有重复键的值将被丢弃。遵循 MySQL
JSON
数据类型规范,不允许重复键,只使用遇到的最后一个值与该键在返回对象中(“最后重复键获胜”)。这意味着在从SELECT
中的列使用此函数的结果可能取决于返回行的顺序,这是不被保证的。当作为窗口函数使用时,如果帧内存在重复键,结果中只有键的最后一个值。如果
ORDER BY
规范保证值具有特定顺序,则帧中最后一行的键的值是确定的。如果没有,则键的结果值是不确定的。考虑以下内容:
mysql> CREATE TABLE t(c VARCHAR(10), i INT); Query OK, 0 rows affected (0.33 sec) mysql> INSERT INTO t VALUES ('key', 3), ('key', 4), ('key', 5); Query OK, 3 rows affected (0.10 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT c, i FROM t; +------+------+ | c | i | +------+------+ | key | 3 | | key | 4 | | key | 5 | +------+------+ 3 rows in set (0.00 sec) mysql> SELECT JSON_OBJECTAGG(c, i) FROM t; +----------------------+ | JSON_OBJECTAGG(c, i) | +----------------------+ | {"key": 5} | +----------------------+ 1 row in set (0.00 sec) mysql> DELETE FROM t; Query OK, 3 rows affected (0.08 sec) mysql> INSERT INTO t VALUES ('key', 3), ('key', 5), ('key', 4); Query OK, 3 rows affected (0.06 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT c, i FROM t; +------+------+ | c | i | +------+------+ | key | 3 | | key | 5 | | key | 4 | +------+------+ 3 rows in set (0.00 sec) mysql> SELECT JSON_OBJECTAGG(c, i) FROM t; +----------------------+ | JSON_OBJECTAGG(c, i) | +----------------------+ | {"key": 4} | +----------------------+ 1 row in set (0.00 sec)
从上次查询中选择的键是不确定的。如果查询不使用
GROUP BY
(通常会强加自己的排序),并且您希望特定键的顺序,您可以通过在OVER
子句中包含一个ORDER BY
规范来将JSON_OBJECTAGG()
作为窗口函数调用,以对帧行施加特定顺序。以下示例展示了对于几种不同帧规范,使用和不使用ORDER BY
会发生什么。没有
ORDER BY
,帧是整个分区:mysql> SELECT JSON_OBJECTAGG(c, i) OVER () AS json_object FROM t; +-------------+ | json_object | +-------------+ | {"key": 4} | | {"key": 4} | | {"key": 4} | +-------------+
使用
ORDER BY
,其中帧是默认的RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
(无论升序还是降序):mysql> SELECT JSON_OBJECTAGG(c, i) OVER (ORDER BY i) AS json_object FROM t; +-------------+ | json_object | +-------------+ | {"key": 3} | | {"key": 4} | | {"key": 5} | +-------------+ mysql> SELECT JSON_OBJECTAGG(c, i) OVER (ORDER BY i DESC) AS json_object FROM t; +-------------+ | json_object | +-------------+ | {"key": 5} | | {"key": 4} | | {"key": 3} | +-------------+
使用
ORDER BY
和整个分区的显式帧:mysql> SELECT JSON_OBJECTAGG(c, i) OVER (ORDER BY i ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS json_object FROM t; +-------------+ | json_object | +-------------+ | {"key": 5} | | {"key": 5} | | {"key": 5} | +-------------+
要返回特定键值(例如最小值或最大值),请在适当查询中包含一个
LIMIT
子句。例如:mysql> SELECT JSON_OBJECTAGG(c, i) OVER (ORDER BY i) AS json_object FROM t LIMIT 1; +-------------+ | json_object | +-------------+ | {"key": 3} | +-------------+ mysql> SELECT JSON_OBJECTAGG(c, i) OVER (ORDER BY i DESC) AS json_object FROM t LIMIT 1; +-------------+ | json_object | +-------------+ | {"key": 5} | +-------------+
有关 JSON 值的规范化、合并和自动包装,请参阅 JSON 值的规范化、合并和自动包装,获取更多信息和示例。
-
MAX([DISTINCT] *
expr*) [*
over_clause*]
返回
expr
的最大值。MAX()
可能接受一个字符串参数;在这种情况下,它返回最大的字符串值。参见第 10.3.1 节,“MySQL 如何使用索引”。DISTINCT
关键字可用于查找expr
的不同值的最大值,但是,这与省略DISTINCT
产生相同的结果。如果没有匹配的行,或者
expr
为NULL
,MAX()
返回NULL
。如果
over_clause
存在,则此函数作为窗口函数执行。over_clause
如第 14.20.2 节,“窗口函数概念和语法”中所述;它不能与DISTINCT
一起使用。mysql> SELECT student_name, MIN(test_score), MAX(test_score) FROM student GROUP BY student_name;
对于
MAX()
,MySQL 当前通过它们的字符串值而不是字符串在集合中的相对位置来比较ENUM
和SET
列。这与ORDER BY
比较它们的方式不同。 -
MIN([DISTINCT] *
expr*) [*
over_clause*]
返回
expr
的最小值。MIN()
可能接受一个字符串参数;在这种情况下,它返回最小的字符串值。参见第 10.3.1 节,“MySQL 如何使用索引”。DISTINCT
关键字可用于查找expr
的不同值的最小值,但是,这与省略DISTINCT
产生相同的结果。如果没有匹配的行,或者
expr
为NULL
,MIN()
返回NULL
。如果
over_clause
存在,则此函数作为窗口函数执行。over_clause
如第 14.20.2 节,“窗口函数概念和语法”中所述;它不能与DISTINCT
一起使用。mysql> SELECT student_name, MIN(test_score), MAX(test_score) FROM student GROUP BY student_name;
对于
MIN()
,MySQL 当前通过它们的字符串值而不是字符串在集合中的相对位置来比较ENUM
和SET
列。这与ORDER BY
比较它们的方式不同。 -
返回
expr
的总体标准偏差。STD()
是标准 SQL 函数STDDEV_POP()
的同义词,作为 MySQL 的扩展提供。如果没有匹配的行,或者
expr
为NULL
,STD()
返回NULL
。如果存在
over_clause
,此函数将作为窗口函数执行。over_clause
的描述如第 14.20.2 节,“窗口函数概念和语法”中所述。 -
STDDEV(*
expr*) [*
over_clause*]
返回
expr
的总体标准偏差。STDDEV()
是标准 SQL 函数STDDEV_POP()
的同义词,用于与 Oracle 兼容。如果没有匹配的行,或者
expr
为NULL
,STDDEV()
返回NULL
。如果存在
over_clause
,此函数将作为窗口函数执行。over_clause
的描述如第 14.20.2 节,“窗口函数概念和语法”中所述。 -
STDDEV_POP(*
expr*) [*
over_clause*]
返回
expr
的总体标准偏差(VAR_POP()
的平方根)。您还可以使用STD()
或STDDEV()
,它们是等效的但不是标准 SQL。如果没有匹配的行,或者
expr
为NULL
,STDDEV_POP()
返回NULL
。如果存在
over_clause
,此函数将作为窗口函数执行。over_clause
的描述如第 14.20.2 节,“窗口函数概念和语法”中所述。 -
STDDEV_SAMP(*
expr*) [*
over_clause*]
返回
expr
的样本标准偏差(VAR_SAMP()
的平方根)。如果没有匹配的行,或者
expr
为NULL
,STDDEV_SAMP()
返回NULL
。如果存在
over_clause
,此函数将作为窗口函数执行。over_clause
的描述如第 14.20.2 节,“窗口函数概念和语法”中所述。 -
SUM([DISTINCT] *
expr*) [*
over_clause*]
返回
expr
的总和。如果返回集没有行,则SUM()
返回NULL
。可以使用DISTINCT
关键字仅对expr
的不同值求和。如果没有匹配的行,或者
expr
为NULL
,SUM()
返回NULL
。如果存在
over_clause
,此函数将作为窗口函数执行。over_clause
如第 14.20.2 节,“窗口函数概念和语法”中描述的那样;它不能与DISTINCT
一起使用。 -
VAR_POP(*
expr*) [*
over_clause*]
返回
expr
的总体标准方差。它将行视为整体总体,而不是样本,因此分母是行数。您也可以使用VARIANCE()
,它是等效的但不是标准 SQL。如果没有匹配的行,或者
expr
为NULL
,VAR_POP()
返回NULL
。如果存在
over_clause
,此函数将作为窗口函数执行。over_clause
如第 14.20.2 节,“窗口函数概念和语法”中描述的那样。 -
VAR_SAMP(*
expr*) [*
over_clause*]
返回
expr
的样本方差。也就是说,分母是行数减一。如果没有匹配的行,或者
expr
为NULL
,VAR_SAMP()
返回NULL
。如果存在
over_clause
,此函数将作为窗口函数执行。over_clause
如第 14.20.2 节,“窗口函数概念和语法”中描述的那样。 -
VARIANCE(*
expr*) [*
over_clause*]
返回
expr
的总体标准方差。VARIANCE()
是标准 SQL 函数VAR_POP()
的同义词,作为 MySQL 扩展提供。如果没有匹配的行,或者
expr
为NULL
,VARIANCE()
返回NULL
。如果存在
over_clause
,此函数将作为窗口函数执行。over_clause
如第 14.20.2 节,“窗口函数概念和语法”中描述的那样。
14.19.2 GROUP BY 修饰符
GROUP BY
子句允许使用WITH ROLLUP
修饰符,导致摘要输出包括代表更高级别(即超级聚合)摘要操作的额外行。因此,ROLLUP
使您能够使用单个查询回答多个分析级别的问题。例如,ROLLUP
可用于支持 OLAP(在线分析处理)操作。
假设一个sales
表有year
、country
、product
和profit
列用于记录销售利润:
CREATE TABLE sales
(
year INT,
country VARCHAR(20),
product VARCHAR(32),
profit INT
);
要按年份总结表内容,请使用简单的GROUP BY
,如下所示:
mysql> SELECT year, SUM(profit) AS profit
FROM sales
GROUP BY year;
+------+--------+
| year | profit |
+------+--------+
| 2000 | 4525 |
| 2001 | 3010 |
+------+--------+
输出显示每年的总利润。要确定所有年份总利润的总和,您必须自己加总各个值或运行另一个查询。或者您可以使用ROLLUP
,它通过单个查询提供了两个级别的分析。在GROUP BY
子句中添加WITH ROLLUP
修饰符会导致查询生成另一行(超级聚合行),显示所有年份值的总计:
mysql> SELECT year, SUM(profit) AS profit
FROM sales
GROUP BY year WITH ROLLUP;
+------+--------+
| year | profit |
+------+--------+
| 2000 | 4525 |
| 2001 | 3010 |
| NULL | 7535 |
+------+--------+
year
列中的NULL
值标识总计超级聚合行。
ROLLUP
在有多个GROUP BY
列时具有更复杂的效果。在这种情况下,每当除最后一个分组列之外的任何列的值发生变化时,查询都会生成一个额外的超级聚合摘要行。
例如,没有ROLLUP
,基于year
、country
和product
的sales
表摘要可能如下所示,其中输出仅指示年/国家/产品分析级别的摘要值:
mysql> SELECT year, country, product, SUM(profit) AS profit
FROM sales
GROUP BY year, country, product;
+------+---------+------------+--------+
| year | country | product | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer | 1500 |
| 2000 | Finland | Phone | 100 |
| 2000 | India | Calculator | 150 |
| 2000 | India | Computer | 1200 |
| 2000 | USA | Calculator | 75 |
| 2000 | USA | Computer | 1500 |
| 2001 | Finland | Phone | 10 |
| 2001 | USA | Calculator | 50 |
| 2001 | USA | Computer | 2700 |
| 2001 | USA | TV | 250 |
+------+---------+------------+--------+
添加了ROLLUP
后,查询会生成几行额外的行:
mysql> SELECT year, country, product, SUM(profit) AS profit
FROM sales
GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+--------+
| year | country | product | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer | 1500 |
| 2000 | Finland | Phone | 100 |
| 2000 | Finland | NULL | 1600 |
| 2000 | India | Calculator | 150 |
| 2000 | India | Computer | 1200 |
| 2000 | India | NULL | 1350 |
| 2000 | USA | Calculator | 75 |
| 2000 | USA | Computer | 1500 |
| 2000 | USA | NULL | 1575 |
| 2000 | NULL | NULL | 4525 |
| 2001 | Finland | Phone | 10 |
| 2001 | Finland | NULL | 10 |
| 2001 | USA | Calculator | 50 |
| 2001 | USA | Computer | 2700 |
| 2001 | USA | TV | 250 |
| 2001 | USA | NULL | 3000 |
| 2001 | NULL | NULL | 3010 |
| NULL | NULL | NULL | 7535 |
+------+---------+------------+--------+
现在输出包括四个层次的分析摘要信息,而不仅仅是一个:
-
对于给定年份和国家的每组产品行之后,会出现一个额外的超级聚合摘要行,显示所有产品的总计。这些行的
product
列设置为NULL
。 -
对于给定年份的每组行之后,会出现一个额外的超级聚合摘要行,显示所有国家和产品的总计。这些行的
country
和products
列设置为NULL
。 -
最后,在所有其他行之后,会出现一个额外的超级聚合摘要行,显示所有年份、国家和产品的总计。此行的
year
、country
和products
列设置为NULL
。
每个超级聚合行中的NULL
指示符在将行发送到客户端时生成。服务器查看在第一个发生值变化的最左边的GROUP BY
子句中命名的列。对于结果集中的任何列,其名称与这些名称中的任何一个匹配,其值都设置为NULL
。(如果按列位置指定分组列,则服务器通过位置确定要设置为NULL
的列。)
因为在超级聚合行中的NULL
值是在查询处理的最后阶段放入结果集中的,所以你只能在选择列表或HAVING
子句中将它们作为NULL
值进行测试。你不能在连接条件或WHERE
子句中将它们作为NULL
值进行测试,以确定选择哪些行。例如,你不能在查询中添加WHERE product IS NULL
来从输出中消除除了超级聚合行之外的所有行。
NULL
值在客户端显示为NULL
,可以使用任何 MySQL 客户端编程接口进行测试。然而,在这一点上,你无法区分NULL
是代表常规分组值还是超级聚合值。要测试区分,使用稍后描述的GROUPING()
函数。
以前,MySQL 不允许在具有WITH ROLLUP
选项的查询中使用DISTINCT
或ORDER BY
。这个限制在 MySQL 8.0.12 及更高版本中被取消。(Bug #87450,Bug #86311,Bug #26640100,Bug #26073513)
对于GROUP BY ... WITH ROLLUP
查询,为了测试结果中的NULL
值是否代表超级聚合值,可以在选择列表、HAVING
子句和(从 MySQL 8.0.12 开始)ORDER BY
子句中使用GROUPING()
函数。例如,GROUPING(year)
在year
列中的NULL
出现在超级聚合行时返回 1,否则返回 0。类似地,GROUPING(country)
和GROUPING(product)
分别在country
和product
列中的超级聚合NULL
值时返回 1:
mysql> SELECT
year, country, product, SUM(profit) AS profit,
GROUPING(year) AS grp_year,
GROUPING(country) AS grp_country,
GROUPING(product) AS grp_product
FROM sales
GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+--------+----------+-------------+-------------+
| year | country | product | profit | grp_year | grp_country | grp_product |
+------+---------+------------+--------+----------+-------------+-------------+
| 2000 | Finland | Computer | 1500 | 0 | 0 | 0 |
| 2000 | Finland | Phone | 100 | 0 | 0 | 0 |
| 2000 | Finland | NULL | 1600 | 0 | 0 | 1 |
| 2000 | India | Calculator | 150 | 0 | 0 | 0 |
| 2000 | India | Computer | 1200 | 0 | 0 | 0 |
| 2000 | India | NULL | 1350 | 0 | 0 | 1 |
| 2000 | USA | Calculator | 75 | 0 | 0 | 0 |
| 2000 | USA | Computer | 1500 | 0 | 0 | 0 |
| 2000 | USA | NULL | 1575 | 0 | 0 | 1 |
| 2000 | NULL | NULL | 4525 | 0 | 1 | 1 |
| 2001 | Finland | Phone | 10 | 0 | 0 | 0 |
| 2001 | Finland | NULL | 10 | 0 | 0 | 1 |
| 2001 | USA | Calculator | 50 | 0 | 0 | 0 |
| 2001 | USA | Computer | 2700 | 0 | 0 | 0 |
| 2001 | USA | TV | 250 | 0 | 0 | 0 |
| 2001 | USA | NULL | 3000 | 0 | 0 | 1 |
| 2001 | NULL | NULL | 3010 | 0 | 1 | 1 |
| NULL | NULL | NULL | 7535 | 1 | 1 | 1 |
+------+---------+------------+--------+----------+-------------+-------------+
你可以使用GROUPING()
来替换超级聚合NULL
值的标签,而不是直接显示GROUPING()
的结果:
mysql> SELECT
IF(GROUPING(year), 'All years', year) AS year,
IF(GROUPING(country), 'All countries', country) AS country,
IF(GROUPING(product), 'All products', product) AS product,
SUM(profit) AS profit
FROM sales
GROUP BY year, country, product WITH ROLLUP;
+-----------+---------------+--------------+--------+
| year | country | product | profit |
+-----------+---------------+--------------+--------+
| 2000 | Finland | Computer | 1500 |
| 2000 | Finland | Phone | 100 |
| 2000 | Finland | All products | 1600 |
| 2000 | India | Calculator | 150 |
| 2000 | India | Computer | 1200 |
| 2000 | India | All products | 1350 |
| 2000 | USA | Calculator | 75 |
| 2000 | USA | Computer | 1500 |
| 2000 | USA | All products | 1575 |
| 2000 | All countries | All products | 4525 |
| 2001 | Finland | Phone | 10 |
| 2001 | Finland | All products | 10 |
| 2001 | USA | Calculator | 50 |
| 2001 | USA | Computer | 2700 |
| 2001 | USA | TV | 250 |
| 2001 | USA | All products | 3000 |
| 2001 | All countries | All products | 3010 |
| All years | All countries | All products | 7535 |
+-----------+---------------+--------------+--------+
带有多个表达式参数的GROUPING()
函数返回一个结果,表示将每个表达式的结果组合在一起的位掩码,最低位对应最右边表达式的结果。例如,GROUPING(year, country, product)
的计算如下:
result for GROUPING(*product*)
+ result for GROUPING(*country*) << 1
+ result for GROUPING(*year*) << 2
这样的GROUPING()
的结果非零,如果任何表达式代表超级聚合NULL
,那么你可以只返回超级聚合行并过滤掉常规分组行,如下所示:
mysql> SELECT year, country, product, SUM(profit) AS profit
FROM sales
GROUP BY year, country, product WITH ROLLUP
HAVING GROUPING(year, country, product) <> 0;
+------+---------+---------+--------+
| year | country | product | profit |
+------+---------+---------+--------+
| 2000 | Finland | NULL | 1600 |
| 2000 | India | NULL | 1350 |
| 2000 | USA | NULL | 1575 |
| 2000 | NULL | NULL | 4525 |
| 2001 | Finland | NULL | 10 |
| 2001 | USA | NULL | 3000 |
| 2001 | NULL | NULL | 3010 |
| NULL | NULL | NULL | 7535 |
+------+---------+---------+--------+
sales
表中不包含 NULL
值,因此 ROLLUP
结果中的所有 NULL
值都代表超级聚合值。当数据集包含 NULL
值时,ROLLUP
汇总可能不仅在超级聚合行中包含 NULL
值,还可能在常规分组行中包含 NULL
值。GROUPING()
可以帮助区分它们。假设表 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 |
+------+-------+----------+
简单的 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 |
+------+-------+----------+
使用 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 |
+-----------+-----------+----------+
使用 ROLLUP 时的其他考虑事项
以下讨论列出了 MySQL 实现 ROLLUP
的一些特定行为。
在 MySQL 8.0.12 之前,当使用 ROLLUP
时,不能同时使用 ORDER BY
子句对结果进行排序。换句话说,在 MySQL 中,ROLLUP
和 ORDER BY
是互斥的。然而,你仍然可以在排序顺序上有一定的控制。为了绕过不能将 ROLLUP
与 ORDER BY
结合使用的限制,并实现对分组结果的特定排序顺序,可以将分组结果集生成为派生表,并对其应用 ORDER BY
。例如:
mysql> SELECT * FROM
(SELECT year, SUM(profit) AS profit
FROM sales GROUP BY year WITH ROLLUP) AS dt
ORDER BY year DESC;
+------+--------+
| year | profit |
+------+--------+
| 2001 | 3010 |
| 2000 | 4525 |
| NULL | 7535 |
+------+--------+
截至 MySQL 8.0.12,ORDER BY
和 ROLLUP
可以一起使用,这使得可以使用 ORDER BY
和 GROUPING()
来实现对分组结果的特定排序顺序。例如:
mysql> SELECT year, SUM(profit) AS profit
FROM sales
GROUP BY year WITH ROLLUP
ORDER BY GROUPING(year) DESC;
+------+--------+
| year | profit |
+------+--------+
| NULL | 7535 |
| 2000 | 4525 |
| 2001 | 3010 |
+------+--------+
在这两种情况下,超级聚合摘要行与它们计算出的行一起排序,并且它们的放置取决于排序顺序(升序排序时在末尾,降序排序时在开头)。
LIMIT
可以用于限制返回给客户端的行数。LIMIT
应用于 ROLLUP
之后,因此限制适用于 ROLLUP
添加的额外行。例如:
mysql> SELECT year, country, product, SUM(profit) AS profit
FROM sales
GROUP BY year, country, product WITH ROLLUP
LIMIT 5;
+------+---------+------------+--------+
| year | country | product | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer | 1500 |
| 2000 | Finland | Phone | 100 |
| 2000 | Finland | NULL | 1600 |
| 2000 | India | Calculator | 150 |
| 2000 | India | Computer | 1200 |
+------+---------+------------+--------+
使用 LIMIT
与 ROLLUP
可能会产生更难解释的结果,因为对于理解超级聚合行来说,上下文更少。
MySQL 的一个扩展允许在选择列表中命名不在 GROUP BY
列表中出现的列。(有关非聚合列和 GROUP BY
的信息,请参阅 第 14.19.3 节,“MySQL 对 GROUP BY 的处理”。)在这种情况下,服务器可以自由选择摘要行中来自此非聚合列的任何值,包括 WITH ROLLUP
添加的额外行。例如,在以下查询中,country
是一个非聚合列,不在 GROUP BY
列表中出现,为此列选择的值是不确定的:
mysql> SELECT year, country, SUM(profit) AS profit
FROM sales
GROUP BY year WITH ROLLUP;
+------+---------+--------+
| year | country | profit |
+------+---------+--------+
| 2000 | India | 4525 |
| 2001 | USA | 3010 |
| NULL | USA | 7535 |
+------+---------+--------+
当未启用ONLY_FULL_GROUP_BY
SQL 模式时,允许这种行为。如果启用了该模式,服务器会因为country
未在GROUP BY
子句中列出而拒绝查询。启用ONLY_FULL_GROUP_BY
后,您仍可以通过对非确定性值列使用ANY_VALUE()
函数来执行查询:
mysql> SELECT year, ANY_VALUE(country) AS country, SUM(profit) AS profit
FROM sales
GROUP BY year WITH ROLLUP;
+------+---------+--------+
| year | country | profit |
+------+---------+--------+
| 2000 | India | 4525 |
| 2001 | USA | 3010 |
| NULL | USA | 7535 |
+------+---------+--------+
在 MySQL 8.0.28 及更高版本中,rollup
列不能作为MATCH()
的参数(并将被拒绝并显示错误),除非在WHERE
子句中调用。有关更多信息,请参见第 14.9 节,“全文搜索函数”。
14.19.3 MySQL GROUP BY 处理
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 窗口函数
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
函数与内置函数之间的一些细微差异。有关这些差异的详细信息,请参阅本节中的函数描述。
-
给定一个数字字节计数,将其转换为人类可读格式,并返回一个由值和单位指示器组成的字符串。该字符串包含四舍五入到 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 内部函数
表格 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”。
14.24 精度数学
14.24.1 数值类型
14.24.2 DECIMAL 数据类型特性
14.24.3 表达式处理
14.24.4 四舍五入行为
14.24.5 精度数学示例
MySQL 提供了对精度数学的支持:处理数字值以获得极其准确的结果,并对无效值具有高度控制。精度数学基于这两个特性:
-
控制服务器对接受或拒绝无效数据的严格程度的 SQL 模式。
-
MySQL 固定点算术库。
这些特性对数字操作有几个影响,并提供了与标准 SQL 高度一致性:
-
精确计算:对于精确值数字,计算不会引入浮点错误。相反,使用精确精度。例如,MySQL 将
.0001
这样的数字视为精确值,而不是近似值,将其累加 10,000 次的结果确切为1
,而不是仅仅“接近”1 的值。 -
明确定义的四舍五入行为:对于精确值数字,
ROUND()
的结果取决于其参数,而不取决于环境因素,如底层 C 库的工作方式。 -
平台独立性:对于精确数值的操作在不同平台(如 Windows 和 Unix)上是相同的。
-
对无效值处理的控制:溢出和除零可检测并可视为错误处理。例如,您可以将列值过大视为错误,而不是将值截断为列数据类型范围内。同样,您可以将除零视为错误,而不是产生
NULL
结果的操作。采取哪种方法由服务器 SQL 模式设置决定。
以下讨论涵盖了精度数学的几个方面,包括与旧应用程序可能存在的不兼容性。最后,给出了一些示例,展示了 MySQL 如何精确处理数字操作。有关控制 SQL 模式的信息,请参见第 7.1.11 节,“服务器 SQL 模式”。
14.24.1 数值类型
原文:
dev.mysql.com/doc/refman/8.0/en/precision-math-numbers.html
精确值操作的精度数学范围包括精确值数据类型(整数和DECIMAL
- DECIMAL, NUMERIC") 类型)和精确值数值文字。近似值数据类型和数值文字被处理为浮点数。
精确值数值文字具有整数部分、小数部分或两者兼有。它们可以带有符号。例如:1
、.2
、3.4
、-5
、-6.78
、+9.10
。
近似值数值文字用科学计数法表示,包括尾数和指数。尾数和/或指数部分可以带有符号。例如:1.2E3
、1.2E-3
、-1.2E3
、-1.2E-3
。
看似相似的两个数字可能会被处理得不同。例如,2.34
是一个精确值(定点)数字,而2.34E0
是一个近似值(浮点)数字。
DECIMAL
- DECIMAL, NUMERIC") 数据类型是一个定点类型,计算是精确的。在 MySQL 中,DECIMAL
- DECIMAL, NUMERIC") 类型有几个同义词:NUMERIC
- DECIMAL, NUMERIC")、DEC
- DECIMAL, NUMERIC")、FIXED
- DECIMAL, NUMERIC")。整数类型也是精确值类型。
FLOAT
- FLOAT, DOUBLE") 和 DOUBLE
- FLOAT, DOUBLE") 数据类型是浮点类型,计算是近似的。在 MySQL 中,与 FLOAT
- FLOAT, DOUBLE") 或 DOUBLE
- FLOAT, DOUBLE") 同义的类型有 DOUBLE PRECISION
- FLOAT, DOUBLE") 和 REAL
- FLOAT, DOUBLE")。
14.24.2 DECIMAL 数据类型特性
译文:
dev.mysql.com/doc/refman/8.0/en/precision-math-decimal-characteristics.html
本节讨论 DECIMAL
数据类型(及其同义词)的特性,特别关注以下主题:
-
最大数字位数
-
存储格式
-
存储需求
-
DECIMAL
列的非标准 MySQL 扩展的上限范围
DECIMAL
列的声明语法为 DECIMAL(*
M*,*
D*)
。参数的值范围如下:
-
M
是最大数字位数(精度)。它的范围是 1 到 65。 -
D
是小数点右侧的数字位数(精度)。它的范围是 0 到 30,且不得大于M
。
如果省略D
,默认值为 0。如果省略M
,默认值为 10。
M
的最大值为 65,意味着对 DECIMAL
值的计算精度可达 65 位。这个 65 位精度的限制也适用于精确值数值文字,因此这些文字的最大范围与以前不同。(DECIMAL
文字的文本长度也有限制;请参阅 第 14.24.3 节,“表达式处理”。)
DECIMAL
列的值以二进制格式存储,将九个十进制数字打包成 4 字节。每个值的整数部分和小数部分的存储需求分别确定。每个九位数字的倍数需要 4 字节,剩余的数字需要 4 字节的一部分。剩余数字所需的存储空间由以下表格给出。
剩余数字 | 字节数 |
---|---|
0 | 0 |
1–2 | 1 |
3–4 | 2 |
5–6 | 3 |
7–9 | 4 |
例如,DECIMAL(18,9)
列有小数点两侧各有九位数字,因此整数部分和小数部分各需要 4 字节。DECIMAL(20,6)
列有十四位整数数字和六位小数数字。九位整数数字需要四字节,剩下的五位数字需要 3 字节。六位小数数字需要 3 字节。
DECIMAL
列不存储前导的 +
字符或 -
字符或前导的 0
数字。如果你将 +0003.1
插入到 DECIMAL(5,1)
列中,它将被存储为 3.1
。对于负数,不会存储文字 -
字符。
DECIMAL
列不允许超出列定义所暗示的范围的值。例如,DECIMAL(3,0)
列支持 -999
到 999
的范围。DECIMAL(*
M*,*
D*)
列允许小数点左边最多 M
- D
位数字。
SQL 标准要求 NUMERIC(*
M*,*
D*)
的精度 必须 恰好为 M
位数字。对于 DECIMAL(*
M*,*
D*)
,标准要求至少 M
位数字的精度,但允许更多。在 MySQL 中,DECIMAL(*
M*,*
D*)
和 NUMERIC(*
M*,*
D*)
是相同的,两者的精度都恰好为 M
位数字。
要了解 DECIMAL
值的内部格式的详细解释,请参阅 MySQL 源代码分发中的 strings/decimal.c
文件。该格式在 decimal2bin()
函数中进行了解释(附有示例)。
14.24.3 表达式处理
原文:
dev.mysql.com/doc/refman/8.0/en/precision-math-expressions.html
使用精确数学,尽可能使用给定的精确值作为数字。例如,在比较中使用的数字将完全按照给定值使用,而不会更改值。在严格 SQL 模式下,对于具有精确数据类型(DECIMAL
或整数)的列进行INSERT
时,如果数字在列范围内,则将插入其精确值。检索时,值应与插入的值相同。(如果未启用严格 SQL 模式,则允许对INSERT
进行截断。)
数值表达式的处理取决于表达式包含的值的类型:
-
如果表达式中存在任何近似值,则表达式是近似的,并使用浮点运算进行评估。
-
如果表达式中没有近似值,则表达式只包含精确值。如果任何精确值包含小数部分(小数点后的值),则使用
DECIMAL
精确算术进行评估,并具有 65 位数字的精度。术语“精确”受二进制表示的限制。例如,1.0/3.0
可以在十进制表示中近似为.333...
,但不能写成一个精确的数字,因此(1.0/3.0)*3.0
不会精确评估为1.0
。 -
否则,表达式只包含整数值。表达式是精确的,并使用整数运算进行评估,具有与
BIGINT
(64 位)相同的精度。
如果数值表达式包含任何字符串,则它们将转换为双精度浮点值,并且表达式是近似的。
插入到数值列中受 SQL 模式的影响,该模式由sql_mode
系统变量控制。(参见 Section 7.1.11, “Server SQL Modes”.)以下讨论提到了严格模式(由STRICT_ALL_TABLES
或STRICT_TRANS_TABLES
模式值选择)和ERROR_FOR_DIVISION_BY_ZERO
。要打开所有限制,您可以简单地使用TRADITIONAL
模式,其中包括严格模式值和ERROR_FOR_DIVISION_BY_ZERO
:
SET sql_mode='TRADITIONAL';
如果将数字插入到精确类型列(DECIMAL
- DECIMAL, NUMERIC") 或整数)中,如果在列范围和精度内,则插入其精确值。
如果小数部分的数字过多,将进行四舍五入并生成一个注释。四舍五入的处理如第 14.24.4 节,“四舍五入行为”中所述。由于小数部分的四舍五入而导致截断不是错误,即使在严格模式下也是如此。
如果整数部分的数字过多,则它太大(超出范围)并按以下方式处理:
-
如果未启用严格模式,则该值将被截断为最接近的合法值,并生成警告。
-
如果启用严格模式,则会发生溢出错误。
在 MySQL 8.0.31 之前,对于DECIMAL
- DECIMAL, NUMERIC")文字,除了 65 位数字的精度限制外,还有一个限制文字长度的限制。如果值超过大约 80 个字符,可能会导致意外结果。例如:
mysql> SELECT
CAST(0000000000000000000000000000000000000000000000000000000000000000000000000000000020.01 AS DECIMAL(15,2)) as val;
+------------------+
| val |
+------------------+
| 9999999999999.99 |
+------------------+
1 row in set, 2 warnings (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect DECIMAL value: '20' |
| Warning | 1264 | Out of range value for column 'val' at row 1 |
+---------+------+----------------------------------------------+
2 rows in set (0.00 sec)
截至 MySQL 8.0.31,这不再是一个问题,如下所示:
mysql> SELECT
CAST(0000000000000000000000000000000000000000000000000000000000000000000000000000000020.01 AS DECIMAL(15,2)) as val;
+-------+
| val |
+-------+
| 20.01 |
+-------+
1 row in set (0.00 sec)
下溢不会被检测到,因此下溢处理是未定义的。
对于将字符串插入到数字列中,如果字符串具有非数字内容,则将字符串转换为数字的处理如下:
-
以非数字开头的字符串不能用作数字,在严格模式下会产生错误,否则会产生警告。这包括空字符串。
-
以数字开头的字符串可以转换,但尾随的非数字部分将被截断。如果被截断的部分包含除空格以外的任何内容,在严格模式下会产生错误,否则会产生警告。
默认情况下,除零操作会产生NULL
结果且不会有警告。通过适当设置 SQL 模式,可以限制除零操作。
启用ERROR_FOR_DIVISION_BY_ZERO
SQL 模式后,MySQL 会以不同方式处理除零操作:
-
如果未启用严格模式,将产生警告。
-
如果启用严格模式,则禁止涉及除零操作的插入和更新,并出现错误。
换句话说,涉及除零操作的插入和更新可以被视为错误,但这需要ERROR_FOR_DIVISION_BY_ZERO
以及严格模式。
假设我们有以下语句:
INSERT INTO t SET i = 1/0;
这是严格模式和ERROR_FOR_DIVISION_BY_ZERO
模式的组合情况。
sql_mode 值 |
结果 |
---|---|
'' (默认) |
无警告,无错误;i 被设置为 NULL 。 |
严格 | 无警告,无错误;i 被设置为 NULL 。 |
ERROR_FOR_DIVISION_BY_ZERO |
警告,没有错误;i 被设置为 NULL 。 |
严格模式,ERROR_FOR_DIVISION_BY_ZERO |
错误条件;不插入任何行。 |
14.24.4 舍入行为
原文:
dev.mysql.com/doc/refman/8.0/en/precision-math-rounding.html
本节讨论了ROUND()
函数的精确数学舍入以及对具有精确值类型(DECIMAL
和整数)的列的插入。
ROUND()
函数的舍入方式取决于其参数是精确还是近似值:
-
对于精确值数字,
ROUND()
使用“四舍五入到最接近的一半”规则:具有大于或等于.5 的分数部分的值将向上舍入到下一个整数(如果为正数)或向下舍入到下一个整数(如果为负数)。 (换句话说,它远离零四舍五入。)具有小于.5 的分数部分的值将向下舍入到下一个整数(如果为正数)或向上舍入到下一个整数(如果为负数)。 (换句话说,它朝向零四舍五入。) -
对于近似值数字,结果取决于 C 库。在许多系统上,这意味着
ROUND()
使用“四舍五入到最近的偶数”规则:一个具有恰好处于两个整数之间的分数部分的值将四舍五入为最接近的偶数整数。
以下示例显示了精确值和近似值之间舍入方式的不同:
mysql> SELECT ROUND(2.5), ROUND(25E-1);
+------------+--------------+
| ROUND(2.5) | ROUND(25E-1) |
+------------+--------------+
| 3 | 2 |
+------------+--------------+
对于插入到DECIMAL
或整数列中,目标是精确数据类型,因此无论要插入的值是精确还是近似值,舍入都使用“远离零的一半”:
mysql> CREATE TABLE t (d DECIMAL(10,0));
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t VALUES(2.5),(2.5E0);
Query OK, 2 rows affected, 2 warnings (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 2
mysql> SHOW WARNINGS;
+-------+------+----------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------+
| Note | 1265 | Data truncated for column 'd' at row 1 |
| Note | 1265 | Data truncated for column 'd' at row 2 |
+-------+------+----------------------------------------+
2 rows in set (0.00 sec)
mysql> SELECT d FROM t;
+------+
| d |
+------+
| 3 |
| 3 |
+------+
2 rows in set (0.00 sec)
SHOW WARNINGS
语句显示由于小数部分的舍入而生成的注释。这种截断不是错误,即使在严格的 SQL 模式下也是如此(请参阅第 14.24.3 节,“表达式处理”)。
14.24.5 精度数学示例
原文:
dev.mysql.com/doc/refman/8.0/en/precision-math-examples.html
本节提供了一些示例,展示了 MySQL 中精度数学查询结果的示例。这些示例演示了第 14.24.3 节,“表达式处理”和第 14.24.4 节,“四舍五入行为”中描述的原则。
示例 1。在可能的情况下,数字使用其给定的精确值:
mysql> SELECT (.1 + .2) = .3;
+----------------+
| (.1 + .2) = .3 |
+----------------+
| 1 |
+----------------+
对于浮点值,结果是不精确的:
mysql> SELECT (.1E0 + .2E0) = .3E0;
+----------------------+
| (.1E0 + .2E0) = .3E0 |
+----------------------+
| 0 |
+----------------------+
另一种看到精确值和近似值处理差异的方法是多次向总和中添加一个小数。考虑以下存储过程,它将.0001
添加到一个变量 1,000 次。
CREATE PROCEDURE p ()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE d DECIMAL(10,4) DEFAULT 0;
DECLARE f FLOAT DEFAULT 0;
WHILE i < 10000 DO
SET d = d + .0001;
SET f = f + .0001E0;
SET i = i + 1;
END WHILE;
SELECT d, f;
END;
对于d
和f
的总和在逻辑上应该为 1,但这仅适用于十进制计算。浮点计算引入了小误差:
+--------+------------------+
| d | f |
+--------+------------------+
| 1.0000 | 0.99999999999991 |
+--------+------------------+
示例 2。乘法使用标准 SQL 所需的比例执行。也就是说,对于具有比例S1和S2的两个数字X1和X2,结果的比例为S1 + S2:
mysql> SELECT .01 * .01;
+-----------+
| .01 * .01 |
+-----------+
| 0.0001 |
+-----------+
示例 3。精确值数字的四舍五入行为是明确定义的:
四舍五入行为(例如,使用ROUND()
函数)独立于底层 C 库的实现,这意味着结果在不同平台上是一致的。
-
精确值列(
DECIMAL
- DECIMAL, NUMERIC") 和整数)以及精确值数字的四舍五入使用“远离零的方向”规则。具有小数部分为.5 或更大的值将远离零四舍五入到最近的整数,如下所示:mysql> SELECT ROUND(2.5), ROUND(-2.5); +------------+-------------+ | ROUND(2.5) | ROUND(-2.5) | +------------+-------------+ | 3 | -3 | +------------+-------------+
-
浮点值的四舍五入使用 C 库,许多系统上使用“四舍五入到最近偶数”的规则。具有恰好处于两个整数之间的小数部分的值将四舍五入为最近的偶数:
mysql> SELECT ROUND(2.5E0), ROUND(-2.5E0); +--------------+---------------+ | ROUND(2.5E0) | ROUND(-2.5E0) | +--------------+---------------+ | 2 | -2 | +--------------+---------------+
示例 4。在严格模式下,插入超出列范围的值会导致错误,而不是截断为合法值。
当 MySQL 未在严格模式下运行时,会发生截断为合法值的情况:
mysql> SET sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE t (i TINYINT);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO t SET i = 128;
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> SELECT i FROM t;
+------+
| i |
+------+
| 127 |
+------+
1 row in set (0.00 sec)
但是,如果启用了严格模式,则会发生错误:
mysql> SET sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE t (i TINYINT);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t SET i = 128;
ERROR 1264 (22003): Out of range value adjusted for column 'i' at row 1
mysql> SELECT i FROM t;
Empty set (0.00 sec)
示例 5:在严格模式下,并且设置了ERROR_FOR_DIVISION_BY_ZERO
,除以零会导致错误,而不是结果为NULL
。
在非严格模式下,除以零的结果为NULL
:
mysql> SET sql_mode='';
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE t (i TINYINT);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t SET i = 1 / 0;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT i FROM t;
+------+
| i |
+------+
| NULL |
+------+
1 row in set (0.03 sec)
但是,如果启用了正确的 SQL 模式,则除以零会导致错误:
mysql> SET sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE t (i TINYINT);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t SET i = 1 / 0;
ERROR 1365 (22012): Division by 0
mysql> SELECT i FROM t;
Empty set (0.01 sec)
示例 6。精确值文字被评估为精确值。
近似值文字使用浮点数进行评估,但精确值文字被处理为DECIMAL
- DECIMAL, NUMERIC"):
mysql> CREATE TABLE t SELECT 2.5 AS a, 25E-1 AS b;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> DESCRIBE t;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| a | decimal(2,1) unsigned | NO | | 0.0 | |
| b | double | NO | | 0 | |
+-------+-----------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
示例 7。如果聚合函数的参数是精确数值类型,则结果也是精确数值类型,其精度至少与参数相同。
考虑以下语句:
mysql> CREATE TABLE t (i INT, d DECIMAL, f FLOAT);
mysql> INSERT INTO t VALUES(1,1,1);
mysql> CREATE TABLE y SELECT AVG(i), AVG(d), AVG(f) FROM t;
结果仅对浮点参数为双精度。对于精确类型参数,结果也是精确类型:
mysql> DESCRIBE y;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| AVG(i) | decimal(14,4) | YES | | NULL | |
| AVG(d) | decimal(14,4) | YES | | NULL | |
| AVG(f) | double | YES | | NULL | |
+--------+---------------+------+-----+---------+-------+
结果仅对浮点参数为双精度。对于精确类型参数,结果也是精确类型。
第十五章 SQL 语句
目录
15.1 数据定义语句
15.1.1 原子数据定义语句支持
15.1.2 ALTER DATABASE 语句
15.1.3 ALTER EVENT 语句
15.1.4 ALTER FUNCTION 语句
15.1.5 ALTER INSTANCE 语句
15.1.6 ALTER LOGFILE GROUP 语句
15.1.7 ALTER PROCEDURE 语句
15.1.8 ALTER SERVER 语句
15.1.9 ALTER TABLE 语句
15.1.10 ALTER TABLESPACE 语句
15.1.11 ALTER VIEW 语句
15.1.12 CREATE DATABASE 语句
15.1.13 CREATE EVENT 语句
15.1.14 CREATE FUNCTION 语句
15.1.15 CREATE INDEX 语句
15.1.16 CREATE LOGFILE GROUP 语句
15.1.17 CREATE PROCEDURE 和 CREATE FUNCTION 语句
15.1.18 CREATE SERVER 语句
15.1.19 CREATE SPATIAL REFERENCE SYSTEM 语句
15.1.20 CREATE TABLE 语句
15.1.21 CREATE TABLESPACE 语句
15.1.22 CREATE TRIGGER 语句
15.1.23 CREATE VIEW 语句
15.1.24 DROP DATABASE 语句
15.1.25 DROP EVENT 语句
15.1.26 DROP FUNCTION 语句
15.1.27 DROP INDEX 语句
15.1.28 DROP LOGFILE GROUP 语句
15.1.29 DROP PROCEDURE 和 DROP FUNCTION 语句
15.1.30 DROP SERVER 语句
15.1.31 DROP SPATIAL REFERENCE SYSTEM 语句
15.1.32 DROP TABLE 语句
15.1.33 DROP TABLESPACE 语句
15.1.34 DROP TRIGGER 语句
15.1.35 DROP VIEW 语句
15.1.36 重命名表 语句
15.1.37 TRUNCATE TABLE 语句
15.2 数据操作语句
15.2.1 CALL 语句
15.2.2 DELETE 语句
15.2.3 DO 语句
15.2.4 EXCEPT 子句
15.2.5 HANDLER 语句
15.2.6 IMPORT TABLE 语句
15.2.7 INSERT 语句
15.2.8 INTERSECT 子句
15.2.9 LOAD DATA 语句
15.2.10 LOAD XML 语句
15.2.11 带括号的查询表达式
15.2.12 REPLACE 语句
15.2.13 SELECT 语句
15.2.14 使用 UNION、INTERSECT 和 EXCEPT 的集合操作
15.2.15 子查询
15.2.16 TABLE 语句
15.2.17 UPDATE 语句
15.2.18 UNION 子句
15.2.19 VALUES 语句
15.2.20 WITH(公共表达式)
15.3 事务和锁定语句
15.3.1 START TRANSACTION、COMMIT 和 ROLLBACK 语句
15.3.2 无法回滚的语句
15.3.3 导致隐式提交的语句
15.3.4 SAVEPOINT、ROLLBACK TO SAVEPOINT 和 RELEASE SAVEPOINT 语句
15.3.5 LOCK INSTANCE FOR BACKUP 和 UNLOCK INSTANCE 语句
15.3.6 LOCK TABLES 和 UNLOCK TABLES 语句
15.3.7 SET TRANSACTION 语句
15.3.8 XA 事务
15.4 复制语句
15.4.1 控制源服务器的 SQL 语句
15.4.2 控制副本服务器的 SQL 语句
15.4.3 控制组复制的 SQL 语句
15.5 预处理语句
15.5.1 PREPARE 语句
15.5.2 EXECUTE 语句
15.5.3 DEALLOCATE PREPARE 语句
15.6 复合语句语法
15.6.1 BEGIN ... END 复合语句
15.6.2 语句标签
15.6.3 DECLARE 语句
15.6.4 存储程序中的变量
15.6.5 流程控制语句
15.6.6 游标
15.6.7 条件处理
15.6.8 条件处理的限制
15.7 数据库管理语句
15.7.1 账户管理语句
15.7.2 资源组管理语句
15.7.3 表维护语句
15.7.4 组件、插件和可加载函数语句
15.7.5 CLONE 语句
15.7.6 SET 语句
15.7.7 SHOW 语句
15.7.8 其他管理语句
15.8 实用程序语句
15.8.1 DESCRIBE 语句
15.8.2 EXPLAIN 语句
15.8.3 HELP 语句
15.8.4 USE 语句
本章描述了 MySQL 支持的 SQL 语句的语法。
15.1 数据定义语句
原文:
dev.mysql.com/doc/refman/8.0/en/sql-data-definition-statements.html
15.1.1 原子数据定义语句支持
15.1.2 修改数据库语句
15.1.3 修改事件语句
15.1.4 修改函数语句
15.1.5 修改实例语句
15.1.6 修改日志文件组语句
15.1.7 修改存储过程语句
15.1.8 修改服务器语句
15.1.9 修改表语句
15.1.10 修改表空间语句
15.1.11 修改视图语句
15.1.12 创建数据库语句
15.1.13 创建事件语句
15.1.14 创建函数语句
15.1.15 创建索引语句
15.1.16 创建日志文件组语句
15.1.17 创建存储过程和创建函数语句
15.1.18 创建服务器语句
15.1.19 创建空间参考系统语句
15.1.20 创建表语句
15.1.21 创建表空间语句
15.1.22 创建触发器语句
15.1.23 创建视图语句
15.1.24 删除数据库语句
15.1.25 删除事件语句
15.1.26 删除函数语句
15.1.27 删除索引语句
15.1.28 删除日志文件组语句
15.1.29 删除存储过程和删除函数语句
15.1.30 删除服务器语句
15.1.31 删除空间参考系统语句
15.1.32 删除表语句
15.1.33 删除表空间语句
15.1.34 删除触发器语句
15.1.35 删除视图语句
15.1.36 重命名表语句
15.1.37 截断表语句
15.1.1 原子数据定义语句支持
MySQL 8.0 支持原子数据定义语言(DDL)语句。这一特性被称为原子 DDL。原子 DDL 语句将与 DDL 操作相关的数据字典更新、存储引擎操作和二进制日志写入合并为单个原子操作。该操作要么被提交,适用更改被持久化到数据字典、存储引擎和二进制日志中,要么被回滚,即使服务器在操作过程中停止。
注意
原子 DDL不是事务性 DDL。DDL 语句,无论是原子的还是其他的,都会隐式结束当前会话中活动的任何事务,就好像在执行该语句之前执行了COMMIT
一样。这意味着 DDL 语句不能在另一个事务中执行,在事务控制语句中(如START TRANSACTION ... COMMIT
)中执行,或者与同一事务中的其他语句组合。
MySQL 8.0 中引入的 MySQL 数据字典使原子 DDL 成为可能。在早期的 MySQL 版本中,元数据存储在元数据文件、非事务表和存储引擎特定的字典中,这需要中间提交。MySQL 数据字典提供的集中式、事务性元数据存储消除了这一障碍,使得重构 DDL 语句操作成为原子操作成为可能。
本节中以下主题描述了原子 DDL 特性:
-
支持的 DDL 语句
-
原子 DDL 特性
-
DDL 语句行为变化
-
存储引擎支持
-
查看 DDL 日志
支持的 DDL 语句
原子 DDL 特性支持表和非表 DDL 语句。与表相关的 DDL 操作需要存储引擎支持,而非表 DDL 操作则不需要。目前,只有InnoDB
存储引擎支持原子 DDL。
-
支持的表 DDL 语句包括对数据库、表空间、表和索引的
CREATE
、ALTER
和DROP
语句,以及TRUNCATE TABLE
语句。 -
支持的非表 DDL 语句包括:
-
CREATE
和DROP
语句,以及(如果适用)用于存储过程、触发器、视图和可加载函数的ALTER
语句。 -
帐户管理语句:用户和角色的
CREATE
,ALTER
,DROP
,以及如果适用的RENAME
语句,以及GRANT
和REVOKE
语句。
-
原子 DDL 功能不支持以下语句:
-
与
InnoDB
不同的存储引擎涉及的与表相关的 DDL 语句。 -
INSTALL PLUGIN
和UNINSTALL PLUGIN
语句。 -
INSTALL COMPONENT
和UNINSTALL COMPONENT
语句。 -
CREATE SERVER
,ALTER SERVER
和DROP SERVER
语句。
原子 DDL 特性
原子 DDL 语句的特性包括以下内容:
-
元数据更新,二进制日志写入和存储引擎操作(如果适用)被合并为单个原子操作。
-
在 DDL 操作期间,SQL 层没有中间提交。
-
如果适用:
-
数据字典,例程,事件和可加载函数缓存的状态与 DDL 操作的状态一致,这意味着缓存将根据 DDL 操作是否成功完成或回滚而更新。
-
DDL 操作涉及的存储引擎方法不执行中间提交,并且存储引擎将自身注册为 DDL 操作的一部分。
-
存储引擎支持 DDL 操作的重做和回滚,这是在 DDL 操作的后 DDL阶段执行的。
-
-
DDL 操作的可见行为是原子的,这改变了一些 DDL 语句的行为。请参阅 DDL 语句行为的变化。
DDL 语句行为的变化
本节描述了由于引入原子 DDL 支持而导致的 DDL 语句行为的变化。
-
如果所有命名表使用支持原子 DDL 的存储引擎,则
DROP TABLE
操作是完全原子的。该语句要么成功删除所有表,要么回滚。如果命名表不存在,则
DROP TABLE
将失败并显示错误,无论存储引擎如何,都不会进行任何更改。下面的示例演示了行为的变化,DROP TABLE
语句因为命名表不存在而失败:mysql> CREATE TABLE t1 (c1 INT); mysql> DROP TABLE t1, t2; ERROR 1051 (42S02): Unknown table 'test.t2' mysql> SHOW TABLES; +----------------+ | Tables_in_test | +----------------+ | t1 | +----------------+
在引入原子 DDL 之前,
DROP TABLE
对于不存在的命名表报告错误,但对于存在的命名表成功:mysql> CREATE TABLE t1 (c1 INT); mysql> DROP TABLE t1, t2; ERROR 1051 (42S02): Unknown table 'test.t2' mysql> SHOW TABLES; Empty set (0.00 sec)
注意
由于这种行为变化,当在 MySQL 5.7 复制源服务器上复制到 MySQL 8.0 副本时,部分完成的
DROP TABLE
语句会失败。为避免此失败场景,在DROP TABLE
语句中使用IF EXISTS
语法,以防止对不存在的表发生错误。 -
如果所有表使用支持原子 DDL 的存储引擎,则
DROP DATABASE
是原子的。该语句要么成功删除所有对象,要么回滚。然而,从文件系统中删除数据库目录是最后发生的,不是原子操作的一部分。如果由于文件系统错误或服务器停止而导致无法删除数据库目录,则不会回滚DROP DATABASE
事务。 -
对于不使用支持原子 DDL 的存储引擎的表,表删除发生在原子
DROP TABLE
或DROP DATABASE
事务之外。这种表删除会单独写入二进制日志,这限制了在中断的DROP TABLE
或DROP DATABASE
操作中存储引擎、数据字典和二进制日志之间的差异至多为一个表。对于删除多个表的操作,不使用支持原子 DDL 的存储引擎的表会在支持原子 DDL 的表之前被删除。 -
对于使用支持原子 DDL 的存储引擎的表,
CREATE TABLE
、ALTER TABLE
、RENAME TABLE
、TRUNCATE TABLE
、CREATE TABLESPACE
和DROP TABLESPACE
操作在操作过程中如果服务器停止,要么完全提交,要么回滚。在早期的 MySQL 版本中,这些操作的中断可能导致存储引擎、数据字典和二进制日志之间的差异,或留下孤立文件。只有所有命名表使用支持原子 DDL 的存储引擎时,RENAME TABLE
操作才是原子的。 -
从 MySQL 8.0.21 开始,在支持原子 DDL 的存储引擎上,当使用基于行的复制时,
CREATE TABLE ... SELECT
语句被记录为二进制日志中的一个事务。以前,它被记录为两个事务,一个用于创建表,另一个用于插入数据。在两个事务之间或插入数据时发生服务器故障可能导致复制一个空表。随着原子 DDL 支持的引入,CREATE TABLE ... SELECT
语句现在对基于行的复制是安全的,并且允许在基于 GTID 的复制中使用。在支持原子 DDL 和外键约束的存储引擎上,在使用基于行的复制时,不允许在
CREATE TABLE ... SELECT
语句中创建外键约束。可以稍后使用ALTER TABLE
添加外键约束。当
CREATE TABLE ... SELECT
作为原子操作应用时,插入数据时会在表上持有元数据锁,这会阻止在操作期间对表的并发访问。 -
如果命名视图不存在,
DROP VIEW
失败,并且不会进行任何更改。行为变更在这个例子中得到展示,DROP VIEW
语句因为命名视图不存在而失败:mysql> CREATE VIEW test.viewA AS SELECT * FROM t; mysql> DROP VIEW test.viewA, test.viewB; ERROR 1051 (42S02): Unknown table 'test.viewB' mysql> SHOW FULL TABLES IN test WHERE TABLE_TYPE LIKE 'VIEW'; +----------------+------------+ | Tables_in_test | Table_type | +----------------+------------+ | viewA | VIEW | +----------------+------------+
在引入原子 DDL 之前,
DROP VIEW
对不存在的命名视图返回错误,但对存在的命名视图成功:mysql> CREATE VIEW test.viewA AS SELECT * FROM t; mysql> DROP VIEW test.viewA, test.viewB; ERROR 1051 (42S02): Unknown table 'test.viewB' mysql> SHOW FULL TABLES IN test WHERE TABLE_TYPE LIKE 'VIEW'; Empty set (0.00 sec)
注意
由于这种行为变更,在 MySQL 5.7 复制源服务器上部分完成的
DROP VIEW
操作在 MySQL 8.0 复制品上复制时会失败。为避免这种失败场景,在DROP VIEW
语句中使用IF EXISTS
语法,以防止对不存在的视图发生错误。 -
部分执行账户管理语句不再被允许。如果发生错误,账户管理语句要么对所有命名用户成功,要么回滚并且没有效果。在早期的 MySQL 版本中,命名多个用户的账户管理语句可能对一些用户成功,对另一些用户失败。
行为变更在这个例子中得到展示,第二个
CREATE USER
语句返回错误但失败,因为它无法对所有命名用户成功。mysql> CREATE USER userA; mysql> CREATE USER userA, userB; ERROR 1396 (HY000): Operation CREATE USER failed for 'userA'@'%' mysql> SELECT User FROM mysql.user WHERE User LIKE 'user%'; +-------+ | User | +-------+ | userA | +-------+
在引入原子 DDL 之前,第二个
CREATE USER
语句对于不存在的命名用户返回错误,但对于已存在的命名用户成功:mysql> CREATE USER userA; mysql> CREATE USER userA, userB; ERROR 1396 (HY000): Operation CREATE USER failed for 'userA'@'%' mysql> SELECT User FROM mysql.user WHERE User LIKE 'user%'; +-------+ | User | +-------+ | userA | | userB | +-------+
注意
由于这种行为变化,MySQL 5.7 复制源服务器上部分完成的帐户管理语句在 MySQL 8.0 副本上复制时会失败。为避免此失败场景,在帐户管理语句中使用
IF EXISTS
或IF NOT EXISTS
语法,适当地防止与命名用户相关的错误。
存储引擎支持
目前,只有InnoDB
存储引擎支持原子 DDL。不支持原子 DDL 的存储引擎不受 DDL 原子性的约束。涉及免除的存储引擎的 DDL 操作仍然可能引入不一致性,当操作被中断或仅部分完成时可能发生。
为支持 DDL 操作的重做和回滚,InnoDB
将 DDL 日志写入mysql.innodb_ddl_log
表,这是一个隐藏的数据字典表,驻留在mysql.ibd
数据字典表空间中。
要查看在 DDL 操作期间写入mysql.innodb_ddl_log
表的 DDL 日志,请启用innodb_print_ddl_logs
配置选项。有关更多信息,请参见查看 DDL 日志。
注意
对于对mysql.innodb_ddl_log
表的更改的重做日志会立即刷新到磁盘,不受innodb_flush_log_at_trx_commit
设置的影响。立即刷新重做日志可以避免数据文件被 DDL 操作修改,但由于这些操作导致的对mysql.innodb_ddl_log
表的重做日志未持久化到磁盘。这种情况可能导致回滚或恢复时出现错误。
InnoDB
存储引擎在阶段中执行 DDL 操作。DDL 操作,如ALTER TABLE
可能在提交阶段之前多次执行准备和执行阶段。
-
准备: 创建所需对象并将 DDL 日志写入
mysql.innodb_ddl_log
表。DDL 日志定义了如何前滚和回滚 DDL 操作。 -
执行: 执行 DDL 操作。例如,执行
CREATE TABLE
操作的创建例程。 -
提交: 更新数据字典并提交数据字典事务。
-
后 DDL:从
mysql.innodb_ddl_log
表中重放和删除 DDL 日志。为了确保可以安全地执行回滚而不引入不一致性,文件操作(如重命名或删除数据文件)在这个最终阶段执行。此阶段还会从mysql.innodb_dynamic_metadata
数据字典表中删除DROP TABLE
、TRUNCATE TABLE
和其他重建表的 DDL 操作的动态元数据。
无论 DDL 操作是提交还是回滚,在后 DDL阶段都会重放和删除mysql.innodb_ddl_log
表中的 DDL 日志。只有在服务器在 DDL 操作期间停止时,DDL 日志才应该保留在mysql.innodb_ddl_log
表中。在这种情况下,恢复后会重放和删除 DDL 日志。
在恢复情况下,当服务器重新启动时,DDL 操作可能会提交或回滚。如果在 DDL 操作的提交阶段执行的数据字典事务存在于重做日志和二进制日志中,则认为操作成功,并向前滚动。否则,当InnoDB
重放数据字典重做日志时,不完整的数据字典事务将被回滚,DDL 操作将被回滚。
查看 DDL 日志
要查看写入mysql.innodb_ddl_log
数据字典表的与涉及InnoDB
存储引擎的原子 DDL 操作相关的 DDL 日志,请启用innodb_print_ddl_logs
以使 MySQL 将 DDL 日志写入stderr
。根据主机操作系统和 MySQL 配置,stderr
可能是错误日志、终端或控制台窗口。请参见第 7.4.2.2 节,“默认错误日志目标配置”。
InnoDB
将 DDL 日志写入mysql.innodb_ddl_log
表,以支持 DDL 操作的重做和回滚。mysql.innodb_ddl_log
表是一个隐藏的数据字典表,驻留在mysql.ibd
数据字典表空间中。与其他隐藏的数据字典表一样,在非调试版本的 MySQL 中无法直接访问mysql.innodb_ddl_log
表。(参见第 16.1 节,“数据字典模式”。)mysql.innodb_ddl_log
表的结构对应于以下定义:
CREATE TABLE mysql.innodb_ddl_log (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
thread_id BIGINT UNSIGNED NOT NULL,
type INT UNSIGNED NOT NULL,
space_id INT UNSIGNED,
page_no INT UNSIGNED,
index_id BIGINT UNSIGNED,
table_id BIGINT UNSIGNED,
old_file_path VARCHAR(512) COLLATE utf8mb4_bin,
new_file_path VARCHAR(512) COLLATE utf8mb4_bin,
KEY(thread_id)
);
-
id
: 用于标识 DDL 日志记录的唯一标识符。 -
thread_id
:每个 DDL 日志记录都被分配一个thread_id
,用于重放和删除属于特定 DDL 操作的 DDL 日志。涉及多个数据文件操作的 DDL 操作会生成多个 DDL 日志记录。 -
type
:DDL 操作类型。类型包括FREE
(删除索引树)、DELETE
(删除文件)、RENAME
(重命名文件)或DROP
(从mysql.innodb_dynamic_metadata
数据字典表中删除元数据)。 -
space_id
:表空间 ID。 -
page_no
:包含分配信息的页面;例如,索引树根页面。 -
index_id
:索引 ID。 -
table_id
:表 ID。 -
old_file_path
:旧表空间文件路径。用于创建或删除表空间文件的 DDL 操作;也用于重命名表空间的 DDL 操作。 -
new_file_path
:新表空间文件路径。用于重命名表空间文件的 DDL 操作。
这个示例演示了启用innodb_print_ddl_logs
以查看写入strderr
的 DDL 日志,用于CREATE TABLE
操作。
mysql> SET GLOBAL innodb_print_ddl_logs=1;
mysql> CREATE TABLE t1 (c1 INT) ENGINE = InnoDB;
[Note] [000000] InnoDB: DDL log insert : [DDL record: DELETE SPACE, id=18, thread_id=7,
space_id=5, old_file_path=./test/t1.ibd]
[Note] [000000] InnoDB: DDL log delete : by id 18
[Note] [000000] InnoDB: DDL log insert : [DDL record: REMOVE CACHE, id=19, thread_id=7,
table_id=1058, new_file_path=test/t1]
[Note] [000000] InnoDB: DDL log delete : by id 19
[Note] [000000] InnoDB: DDL log insert : [DDL record: FREE, id=20, thread_id=7,
space_id=5, index_id=132, page_no=4]
[Note] [000000] InnoDB: DDL log delete : by id 20
[Note] [000000] InnoDB: DDL log post ddl : begin for thread id : 7
[Note] [000000] InnoDB: DDL log post ddl : end for thread id : 7
15.1.2 ALTER DATABASE 语句
ALTER {DATABASE | SCHEMA} [*db_name*]
*alter_option* ...
*alter_option*: {
[DEFAULT] CHARACTER SET [=] *charset_name*
| [DEFAULT] COLLATE [=] *collation_name*
| [DEFAULT] ENCRYPTION [=] {'Y' | 'N'}
| READ ONLY [=] {DEFAULT | 0 | 1}
}
ALTER DATABASE
允许您更改数据库的整体特性。这些特性存储在数据字典中。此语句需要对数据库具有ALTER
权限。ALTER SCHEMA
是ALTER DATABASE
的同义词。
如果省略了数据库名称,则该语句适用于默认数据库。在这种情况下,如果没有默认数据库,则会发生错误。
对于语句中省略的任何alter_option
,数据库将保留其当前选项值,但更改字符集可能会更改校对规则,反之亦然。
-
字符集和校对选项
-
加密选项
-
只读选项
字符集和校对选项
CHARACTER SET
选项更改默认数据库字符集。COLLATE
选项更改默认数据库校对规则。有关字符集和校对规则名称的信息,请参见第十二章,字符集,校对规则,Unicode。
要查看可用的字符集和校对规则,请分别使用SHOW CHARACTER SET
和SHOW COLLATION
语句。请参见第 15.7.7.3 节,“SHOW CHARACTER SET 语句”,以及第 15.7.7.4 节,“SHOW COLLATION 语句”。
在创建存储过程时使用数据库默认值的存储过程将这些默认值作为其定义的一部分。 (在存储过程中,如果未明确指定字符集或校对规则,则具有字符数据类型的变量将使用数据库默认值。请参见第 15.1.17 节,“CREATE PROCEDURE 和 CREATE FUNCTION 语句”。)如果更改数据库的默认字符集或校对规则,则必须删除并重新创建任何要使用新默认值的存储过程。
加密选项
ENCRYPTION
选项是在 MySQL 8.0.16 中引入的,定义了默认数据库加密,该加密会被创建在数据库中的表继承。允许的值为'Y'
(启用加密)和'N'
(禁用加密)。
mysql
系统模式无法设置为默认加密。其中现有的表属于通用mysql
表空间,可能已加密。information_schema
仅包含视图。不可能在其中创建任何表。磁盘上没有任何内容可供加密。performance_schema
中的所有表都使用PERFORMANCE_SCHEMA
引擎,纯粹是内存中的。不可能在其中创建任何其他表。磁盘上没有任何内容可供加密。
仅新创建的表继承默认数据库加密。对于与数据库关联的现有表,它们的加密保持不变。如果启用了table_encryption_privilege_check
系统变量,则需要TABLE_ENCRYPTION_ADMIN
权限来指定与default_table_encryption
系统变量的值不同的默认加密设置。有关更多信息,请参阅为模式和通用表空间定义加密默认值。
只读选项
READ ONLY
选项在 MySQL 8.0.22 中引入,控制是否允许修改数据库及其中的对象。允许的值为DEFAULT
或0
(非只读)和1
(只读)。此选项对数据库迁移很有用,因为启用READ ONLY
的数据库可以在迁移至另一个 MySQL 实例时,无需担心数据库在操作期间会被更改。
对于 NDB Cluster,在一个mysqld服务器上将数据库设置为只读会同步到同一集群中的其他mysqld服务器,使得数据库在所有mysqld服务器上都变为只读。
如果启用了READ ONLY
选项,则会在INFORMATION_SCHEMA
的SCHEMATA_EXTENSIONS
表中显示。请参阅第 28.3.32 节,“INFORMATION_SCHEMA SCHEMATA_EXTENSIONS 表”。
不能为这些系统模式启用READ ONLY
选项:mysql
、information_schema
、performance_schema
。
在ALTER DATABASE
语句中,READ ONLY
选项与其他实例及其他选项的交互如下:
-
如果多个
READ ONLY
实例发生冲突(例如,READ ONLY = 1 READ ONLY = 0
),则会发生错误。 -
即使是只读数据库,也允许包含(不冲突的)
READ ONLY
选项的ALTER DATABASE
语句。 -
如果数据库在语句执行前或执行后的只读状态允许修改,则允许将(不冲突的)
READ ONLY
选项与其他选项混合使用。如果数据库在执行前后的只读状态都禁止更改,则会发生错误。无论数据库是否只读,此语句都会成功:
ALTER DATABASE mydb READ ONLY = 0 DEFAULT COLLATE utf8mb4_bin;
如果数据库不是只读,则此语句成功,但如果数据库已经是只读,则失败:
ALTER DATABASE mydb READ ONLY = 1 DEFAULT COLLATE utf8mb4_bin;
启用READ ONLY
会影响数据库的所有用户,以下情况不受只读检查限制:
-
作为服务器初始化、重启、升级或复制的一部分由服务器执行的语句。
-
由
init_file
系统变量在服务器启动时命名的文件中的语句。 -
TEMPORARY
表;在只读数据库中可以创建、修改、删除和写入TEMPORARY
表。 -
NDB Cluster 非 SQL 插入和更新。
除了刚刚列出的例外操作外,启用READ ONLY
会禁止对数据库及其对象(包括定义、数据和元数据)进行写操作。以下列表详细说明受影响的 SQL 语句和操作:
-
数据库本身:
-
CREATE DATABASE
-
ALTER DATABASE
(除了更改READ ONLY
选项) -
DROP DATABASE
-
-
视图:
-
CREATE VIEW
-
ALTER VIEW
-
DROP VIEW
-
从调用具有副作用的函数的视图中进行选择。
-
更新可更新的视图。
-
如果影响只读数据库中视图的元数据(例如,使视图有效或无效)的对象在可写数据库中创建或删除,则会拒绝这些语句。
-
-
存储过程:
-
CREATE PROCEDURE
-
DROP PROCEDURE
-
CALL
(具有副作用的过程调用) -
CREATE FUNCTION
-
DROP FUNCTION
-
SELECT
(具有副作用的函数选择) -
对于存储过程和函数,只读检查遵循预锁定行为。对于
CALL
语句,只读检查是基于每个语句进行的,因此如果某个有条件执行的写入只读数据库的语句实际上没有执行,调用仍然成功。另一方面,在SELECT
中调用的函数,函数体的执行是在预锁定模式下进行的。只要函数中的某个语句写入只读数据库,无论该语句是否实际执行,函数的执行都会因错误而失败。
-
-
触发器:
-
CREATE TRIGGER
-
DROP TRIGGER
-
触发器调用。
-
-
事件:
-
CREATE EVENT
-
ALTER EVENT
-
DROP EVENT
-
事件执行:
-
在数据库中执行事件会失败,因为这会更改最后执行时间戳,这是存储在数据字典中的事件元数据。事件执行失败还会导致事件调度程序停止。
-
如果事件写入只读数据库中的对象,则事件执行将因错误而失败,但事件调度程序不会停止。
-
-
-
表:
-
CREATE TABLE
-
ALTER TABLE
-
CREATE INDEX
-
DROP INDEX
-
RENAME TABLE
-
TRUNCATE TABLE
-
DROP TABLE
-
DELETE
-
INSERT
-
IMPORT TABLE
-
LOAD DATA
-
LOAD XML
-
REPLACE
-
UPDATE
-
对于级联外键,如果子表位于只读数据库中,则父表的更新和删除将被拒绝,即使子表并未直接受到影响。
-
对于
MERGE
表,例如CREATE TABLE s1.t(i int) ENGINE MERGE UNION (s2.t, s3.t), INSERT_METHOD=...
,以下行为适用:-
如果插入
MERGE
表(INSERT into s1.t
)时,s1
、s2
、s3
中至少有一个是只读的,则无论插入方法如何,插入都会失败。即使实际上插入的数据最终会进入可写表中,也会被拒绝。 -
删除
MERGE
表(DROP TABLE s1.t
)成功,只要s1
不是只读的。允许删除引用只读数据库的MERGE
表。
-
-
一个ALTER DATABASE
语句会阻塞,直到所有已经访问正在被更改的数据库中的对象的并发事务都已提交。相反,访问正在被并发ALTER DATABASE
更改的数据库中的对象的写事务会阻塞,直到ALTER DATABASE
已提交。
如果使用克隆插件克隆本地或远程数据目录,则克隆中的数据库保留其在源数据目录中的只读状态。只读状态不会影响克隆过程本身。如果不希望在克隆中具有相同的数据库只读状态,则必须在克隆过程完成后显式更改选项,使用克隆上的ALTER DATABASE
操作。
当从捐赠者克隆到接收者时,如果接收者有一个用户数据库是只读的,克隆将因错误消息而失败。在使数据库可写后,可以重试克隆。
READ ONLY
允许用于ALTER DATABASE
,但不允许用于CREATE DATABASE
。然而,对于只读数据库,由SHOW CREATE DATABASE
生成的语句在注释中包含READ ONLY=1
,以指示其只读状态:
mysql> ALTER DATABASE mydb READ ONLY = 1;
mysql> SHOW CREATE DATABASE mydb\G
*************************** 1\. row ***************************
Database: mydb
Create Database: CREATE DATABASE `mydb`
/*!40100 DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_0900_ai_ci */
/*!80016 DEFAULT ENCRYPTION='N' */
/* READ ONLY = 1 */
如果服务器执行包含这样一个注释的CREATE DATABASE
语句,服务器会忽略该注释,READ ONLY
选项不会被处理。这对于mysqldump和mysqlpump有影响,它们使用SHOW CREATE DATABASE
生成转储输出中的CREATE DATABASE
语句:
-
在转储文件中,只读数据库的
CREATE DATABASE
语句包含了注释的READ ONLY
选项。 -
转储文件可以像往常一样还原,但由于服务器忽略了注释的
READ ONLY
选项,还原的数据库不是只读的。如果在还原后要使数据库变为只读,必须手动执行ALTER DATABASE
来实现。
假设mydb
是只读的,并且您将其转储如下:
$> mysqldump --databases mydb > mydb.sql
之后的还原操作必须在mydb
仍然是只读时跟随ALTER DATABASE
:
$> mysql
mysql> SOURCE mydb.sql;
mysql> ALTER DATABASE mydb READ ONLY = 1;
MySQL 企业备份不受此问题影响。它备份和恢复只读数据库就像其他数据库一样,但如果备份时启用了READ ONLY
选项,则在恢复时会启用该选项。
ALTER DATABASE
会被写入二进制日志,因此在复制源服务器上对READ ONLY
选项进行更改也会影响副本。为了防止这种情况发生,在执行ALTER DATABASE
语句之前必须禁用二进制日志。例如,为了准备迁移数据库而不影响副本,执行以下操作:
-
在单个会话中,禁用二进制日志,并为数据库启用
READ ONLY
:mysql> SET sql_log_bin = OFF; mysql> ALTER DATABASE mydb READ ONLY = 1;
-
使用mysqldump或mysqlpump等工具对数据库进行转储:
$> mysqldump --databases mydb > mydb.sql
-
在单个会话中,禁用二进制日志并禁用数据库的
READ ONLY
:mysql> SET sql_log_bin = OFF; mysql> ALTER DATABASE mydb READ ONLY = 0;
15.1.3 ALTER EVENT Statement
ALTER
[DEFINER = *user*]
EVENT *event_name*
[ON SCHEDULE *schedule*]
[ON COMPLETION [NOT] PRESERVE]
[RENAME TO *new_event_name*]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT '*string*']
[DO *event_body*]
ALTER EVENT
语句更改现有事件的一个或多个特征,无需删除和重新创建。每个DEFINER
、ON SCHEDULE
、ON COMPLETION
、COMMENT
、ENABLE
/ DISABLE
和DO
子句的语法与与CREATE EVENT
一起使用时完全相同。 (请参见第 15.1.13 节,“CREATE EVENT Statement”。)
任何用户都可以修改在其具有EVENT
权限的数据库上定义的事件。当用户执行成功的ALTER EVENT
语句时,该用户将成为受影响事件的定义者。
ALTER EVENT
仅适用于现有事件:
mysql> ALTER EVENT no_such_event
> ON SCHEDULE
> EVERY '2:3' DAY_HOUR;
ERROR 1517 (HY000): Unknown event 'no_such_event'
在以下每个示例中,假设名为myevent
的事件定义如下所示:
CREATE EVENT myevent
ON SCHEDULE
EVERY 6 HOUR
COMMENT 'A sample comment.'
DO
UPDATE myschema.mytable SET mycol = mycol + 1;
以下语句将myevent
的计划从立即开始的每六小时一次更改为从运行语句时开始的每十二小时一次,四小时后开始:
ALTER EVENT myevent
ON SCHEDULE
EVERY 12 HOUR
STARTS CURRENT_TIMESTAMP + INTERVAL 4 HOUR;
可以在单个语句中更改事件的多个特征。此示例将myevent
执行的 SQL 语句更改为删除mytable
中的所有记录;还更改了事件的计划,使其在此ALTER EVENT
语句运行后一天执行一次。
ALTER EVENT myevent
ON SCHEDULE
AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
DO
TRUNCATE TABLE myschema.mytable;
仅为要更改的特征在ALTER EVENT
语句中指定选项;省略的选项保留其现有值。这包括CREATE EVENT
的任何默认值,如ENABLE
。
要禁用myevent
,请使用此ALTER EVENT
语句:
ALTER EVENT myevent
DISABLE;
ON SCHEDULE
子句可以使用涉及内置 MySQL 函数和用户变量的表达式来获取其中包含的任何timestamp
或interval
值。您不能在这些表达式中使用存储过程或可加载函数,也不能使用任何表引用;但是,您可以使用SELECT FROM DUAL
。这对ALTER EVENT
和CREATE EVENT
语句都适用。在这种情况下,对存储过程、可加载函数和表的引用是明确不允许的,并且会因错误而失败(请参见 Bug #22830)。
尽管包含另一个ALTER EVENT
语句的DO
子句的ALTER EVENT
语句似乎成功了,但当服务器尝试执行生成的计划事件时,执行会因错误而失败。
要重命名事件,请使用ALTER EVENT
语句的RENAME TO
子句。此语句将事件myevent
重命名为yourevent
:
ALTER EVENT myevent
RENAME TO yourevent;
您还可以使用ALTER EVENT ... RENAME TO ...
和*
db_name.event_name*
表示法将事件移动到不同的数据库,如下所示:
ALTER EVENT olddb.myevent
RENAME TO newdb.myevent;
要执行上述语句,执行它的用户必须在olddb
和newdb
数据库上都具有EVENT
权限。
注意
没有RENAME EVENT
语句。
值DISABLE ON SLAVE
在副本上使用,而不是ENABLE
或DISABLE
,以指示在复制源服务器上创建并复制到副本的事件,在副本上不执行。通常,DISABLE ON SLAVE
会根据需要自动设置;但是,在某些情况下,您可能希望或需要手动更改它。有关更多信息,请参见 Section 19.5.1.16, “Replication of Invoked Features”。
15.1.4 ALTER FUNCTION Statement
ALTER FUNCTION *func_name* [*characteristic* ...]
*characteristic*: {
COMMENT '*string*'
| LANGUAGE SQL
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
}
此语句可用于更改存储函数的特性。ALTER FUNCTION
语句中可以指定多个更改。但是,您不能使用此语句更改存储函数的参数或主体;要进行此类更改,必须使用DROP FUNCTION
和CREATE FUNCTION
删除并重新创建函数。
对于该函数,您必须拥有ALTER ROUTINE
权限。(该权限会自动授予函数创建者。)如果启用了二进制日志记录,ALTER FUNCTION
语句可能还需要SUPER
权限,如第 27.7 节“存储程序二进制日志记录”中所述。
15.1.5 ALTER INSTANCE 语句
ALTER INSTANCE *instance_action*
*instance_action*: {
| {ENABLE|DISABLE} INNODB REDO_LOG
| ROTATE INNODB MASTER KEY
| ROTATE BINLOG MASTER KEY
| RELOAD TLS
[FOR CHANNEL {mysql_main | mysql_admin}]
[NO ROLLBACK ON ERROR]
| RELOAD KEYRING
}
ALTER INSTANCE
定义适用于 MySQL 服务器实例的操作。该语句支持以下操作:
-
ALTER INSTANCE {ENABLE | DISABLE} INNODB REDO_LOG
此操作启用或禁用
InnoDB
重做日志记录。默认情况下启用重做日志记录。此功能仅用于将数据加载到新的 MySQL 实例中。该语句不会写入二进制日志。此操作在 MySQL 8.0.21 中引入。警告
不要在生产系统上禁用重做日志记录。虽然允许在禁用重做日志记录时关闭并重新启动服务器,但在禁用重做日志记录时发生意外服务器停止可能会导致数据丢失和实例损坏。
ALTER INSTANCE [ENABLE|DISABLE] INNODB REDO_LOG
操作需要独占备份锁,这会阻止其他ALTER INSTANCE
操作同时执行。其他ALTER INSTANCE
操作必须等待锁被释放后才能执行。更多信息,请参见禁用重做日志记录。
-
ALTER INSTANCE ROTATE INNODB MASTER KEY
此操作旋转用于
InnoDB
表空间加密的主加密密钥。密钥旋转需要ENCRYPTION_KEY_ADMIN
或SUPER
权限。要执行此操作,必须安装和配置一个密钥环插件。有关说明,请参见第 8.4.4 节,“MySQL 密钥环”。ALTER INSTANCE ROTATE INNODB MASTER KEY
支持并发 DML。但是,它不能与CREATE TABLE ... ENCRYPTION
或ALTER TABLE ... ENCRYPTION
操作同时运行,并且会获取锁以防止这些语句的并发执行可能引起的冲突。如果其中一个冲突的语句正在运行,则必须等待其完成后才能继续执行另一个。ALTER INSTANCE ROTATE INNODB MASTER KEY
语句会写入二进制日志,以便在复制服务器上执行。有关额外的
ALTER INSTANCE ROTATE INNODB MASTER KEY
使用信息,请参见第 17.13 节,“InnoDB 数据静止加密”。 -
ALTER INSTANCE ROTATE BINLOG MASTER KEY
此操作旋转用于二进制日志加密的二进制日志主密钥。二进制日志主密钥的密钥轮换需要
BINLOG_ENCRYPTION_ADMIN
或SUPER
权限。如果binlog_encryption
系统变量设置为OFF
,则不能使用该语句。要执行此操作,必须安装和配置一个密钥环插件。有关说明,请参阅第 8.4.4 节,“MySQL 密钥环”。ALTER INSTANCE ROTATE BINLOG MASTER KEY
操作不会写入二进制日志,也不会在副本上执行。因此,二进制日志主密钥轮换可以在包含不同 MySQL 版本的复制环境中进行。要在所有适用的源和副本服务器上安排定期轮换二进制日志主密钥,您可以在每个服务器上启用 MySQL 事件调度程序,并使用CREATE EVENT
语句发出ALTER INSTANCE ROTATE BINLOG MASTER KEY
语句。如果您因为怀疑当前或任何以前的二进制日志主密钥可能已被泄露而轮换二进制日志主密钥,则在每个适用的源和副本服务器上发出该语句,这样可以验证立即的合规性。有关其他
ALTER INSTANCE ROTATE BINLOG MASTER KEY
使用信息,包括如果进程未正确完成或被意外服务器停机中断时该怎么办,请参阅第 19.3.2 节,“加密二进制日志文件和中继日志文件”。 -
ALTER INSTANCE RELOAD TLS
这个操作重新配置了 TLS 上下文,使用当前定义上下文的系统变量的值。它还更新了反映活动上下文值的状态变量。此操作需要
CONNECTION_ADMIN
权限。有关重新配置 TLS 上下文的其他信息,包括哪些系统和状态变量与上下文相关,请参阅服务器端加密连接的运行时配置和监控。默认情况下,该语句重新加载主连接接口的 TLS 上下文。如果提供了
FOR CHANNEL
子句(自 MySQL 8.0.21 起可用),该语句将重新加载命名通道的 TLS 上下文:mysql_main
用于主连接接口,mysql_admin
用于管理连接接口。有关不同接口的信息,请参见第 7.1.12.1 节,“连接接口”。更新后的 TLS 上下文属性在 Performance Schematls_channel_status
表中公开。请参见第 29.12.21.9 节,“tls_channel_status 表”。更新主接口的 TLS 上下文也可能会影响管理接口,因为除非为该接口配置了一些非默认的 TLS 值,否则它将使用与主接口相同的 TLS 上下文。
注意
当重新加载 TLS 上下文时,OpenSSL 会重新加载包含 CRL(证书吊销列表)的文件作为过程的一部分。如果 CRL 文件很大,服务器会分配大块内存(文件大小的十倍),在加载新实例并且旧实例尚未释放时会将其加倍。大量分配被释放后,进程驻留内存不会立即减少,因此如果反复使用带有大型 CRL 文件的
ALTER INSTANCE RELOAD TLS
语句,进程驻留内存使用量可能会增加。默认情况下,如果配置值不允许创建新的 TLS 上下文,则
RELOAD TLS
操作会回滚并显示错误,不会产生任何效果。先前的上下文值将继续用于新连接。如果给出了可选的NO ROLLBACK ON ERROR
子句并且无法创建新上下文,则不会发生回滚。相反,会生成警告,并且对语句适用的接口上的新连接将禁用加密。ALTER INSTANCE RELOAD TLS
语句不会写入二进制日志(因此不会被复制)。TLS 配置是本地的,并且依赖于本地文件,不一定存在于所有涉及的服务器上。 -
ALTER INSTANCE RELOAD KEYRING
如果安装了密钥环组件,则此操作会告诉组件重新读取其配置文件并重新初始化任何密钥环内存数据。如果您在运行时修改了组件配置,则新配置在执行此操作之前不会生效。重新加载密钥环需要
ENCRYPTION_KEY_ADMIN
权限。此操作是在 MySQL 8.0.24 中添加的。此操作仅允许重新配置当前安装的密钥环组件。它不允许更改已安装的组件。例如,如果您更改了已安装的密钥环组件的配置,
ALTER INSTANCE RELOAD KEYRING
会使新配置生效。另一方面,如果您更改了服务器清单文件中命名的密钥环组件,ALTER INSTANCE RELOAD KEYRING
没有效果,当前组件仍然安装。ALTER INSTANCE RELOAD KEYRING
语句不会写入二进制日志(因此不会被复制)。