MySQL——MySQL SELECT查询非分组聚合列（sql_mode=only_full_group_by）

问题描述

业务中有这样的逻辑：通过某一列col1分组之后查询col1，col2，col3这三个列，SQL语句如下：

select company_name, department, employee from company group by company_name

如果是MySQL 5.7以前的版本，是可以执行成功的；如果是MySQL 5.7及以上版本，就会报错：

ERROR 1140 (42000): In aggregated query without GROUP BY, expression... of SELECT list contains nonaggregated column 'col_name'; this is incompatible with sql_mode=only_full_group_by

 

原因分析

在标准的 SQL-92中，上述的查询是不被支持的。也就是说，select列表、having条件和order by列表中是不允许出现非聚合列的，所谓非聚合列就是group by子句中的列。换句话说，就是select中查询的列，必须出现在group by子句中，否则就必须在非聚合列上使用聚合函数（比如sum()、max()等）。

为什么不允许这样查询呢？主要是因为分组之后，要查询非聚合列，其实是不知道该取那个值的。

但是MySQL对标准的SQL-92做了扩展，使得select列表、having条件和order by列表中可以引用非聚合列，这个功能可以带来更好的性能，因为它需要对非聚合列进行分组排序。不过在分组之后，查询非聚合列时，其取值是随机的，也就是组内随机取一个，就算是先通过order by进行排序之后，也是随机取值的。

因为MySQL 5.7之前，配置项ONLY_FULL_GROUP_BY是默认禁用的，也就是支持在select列表、having条件和order by列表中引用非聚合列且不适用聚合函数；而MySQL 5.7及之后的版本中，配置项ONLY_FULL_GROUP_BY默认是开启的，则不支持上述功能。

可以通过下面SQL查看ONLY_FULL_GROUP_BY是否启用：

-- 查询的结果中包含ONLY_FULL_GROUP_BY就说明是启用的，否则就是被禁用的
select @@global.sql_mode;

由于本人使用的是MySQL 5.7，所以在select中查询非聚合列就出现了上述的报错

 

解决方案

了解了问题原因和原理之后，解决起来就比较简单了。

通过下面SQL将sql_mode中的ONLY_FULL_GROUP_BY替换成空即可：

set global sql_mode=(select replace(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));

当然也可以设置会话级别的：

set session sql_mode=(select replace(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));

或者是通过MySQL配置文件my.cnf进行修改（需重启mysql实例）。

 

参考

1. https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html
2. https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
3. https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by