MySQL 中的 “异常” 行为？

查看以下 SQL 语句：

select version(), gv.variable_value
from information_schema.global_variables gv
where gv.variable_name = 'sql_mode';
-- 5.6.49,NO_ENGINE_SUBSTITUTION

select user, host, count(*) cnt
from mysql.user
group by user;
-- root,%,2

以上 SQL 语句于 MySQL 5.6.49 版本中运行成功，而在其他大部分关系型数据库中运行都会报错（Oracle、PostgreSQL 及 SQL Server），而常规写法（full group by）应该如下所示：

select user, host, count(*) cnt
from mysql.user
group by user, host;

即：group by 应该包括 select 中的所有非聚合列。

为什么 MySQL 有些版本中允许非常规写法呢？这就不得不提到 mysql 的 sql mode 特性。

sql mode 简介

MySQL 服务器可以以不同的 SQL 模式运行，并且可以根据 sql_mode 系统变量的值为不同的客户机应用不同的模式。dba 可以设置全局 SQL 模式以匹配站点服务器的运行需求，每个应用程序可以根据自己的需求设置会话级 SQL 模式。

会话级配置 sql_mode 不需要特殊的权限：

set session sql_mode='';
select user,host,count(*) from mysql.user group by user;

show grants for u01@'%';
+-----------------------------------------------------------------+
| Grants for u01@%                                                |
+-----------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'u01'@'%' IDENTIFIED BY PASSWORD <secret> |
| GRANT SELECT ON `mysql`.* TO 'u01'@'%'                          |
+-----------------------------------------------------------------+

模式影响 MySQL 支持的 SQL 语法和执行的数据验证检查。这使得在不同的环境中使用 MySQL 以及与其他数据库服务器一起使用 MySQL 更加容易。

sql mode 之 only_full_group_by

让 sql 语法常规化/规范化

只要将 sql_mode 参数包含 only_full_group_by 即可：

set session sql_mode = 'NO_ENGINE_SUBSTITUTION,only_full_group_by';

select user,host,count(*) from mysql.user group by user;
ERROR 1055 (42000): 'mysql.user.Host' isn't in GROUP BY

非常规写法为什么可以执行成功？

因为 MySQL 默认对查询做了转换，sql_mode 未指定 only_full_group_by 时的行为：

select version(),user,any_value(host),count(*) cnt from mysql.user group by user;
+------------+---------------+-----------------+-----+
| version()  | user          | any_value(host) | cnt |
+------------+---------------+-----------------+-----+
| 5.7.38-log | aa            | localhost       |   1 |
| 5.7.38-log | mysql.session | localhost       |   1 |
| 5.7.38-log | mysql.sys     | localhost       |   1 |
| 5.7.38-log | root          | %               |   2 |
| 5.7.38-log | soar          | %               |   1 |
| 5.7.38-log | tt            | localhost       |   1 |
+------------+---------------+-----------------+-----+
6 rows in set (0.00 sec)

即：sql_mode 未指定 only_full_group_by 时，group by 未包含非聚合列被隐式加上了 any_value 聚合函数。

注：any_value 在 MySQL 5.7 被支持。

总结

• sql_mode 可以在会话级别指定。
• sql_mode 未包含 only_full_group_by 时，group by 未包含的列被隐式加上了 any_value 聚合函数。
• any_value 聚合函数在 mysql 5.7 被支持。

关于我

来源：简书 - linora

作者：EZy-1990（ixdba/linora）

关于作者：DBA 一枚，09 年开始接触数据库。早年间从事 Oracle DBA 一职，目前专注于开源数据库领域，混过很多家公司，玩过多种数据库。

其他说明：不能保证全文没有任何不妥之处，如有发现，不吝赐教。

🙊🙊🙊🙊🙊🙊🙊

---