MySQL——MySQL SELECT查询非分组聚合列（sql

理解数据库原理（SQL、MySQL） #生活知识# #编程教程#

问题描述

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

select company_name, department, employee from company group by company_name 1

如果是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 1

原因分析

在标准的 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; 12

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

解决方案

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

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

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

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

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

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

参考

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

网址：MySQL——MySQL SELECT查询非分组聚合列（sql https://www.yuejiaxmz.com/news/view/186478

相关内容

MySQL上亿数据查询优化：实践与技巧
mysql （8）=====用户授权管理
PyMySQL的使用：事务、索引、如何防止SQL注入
mysql视图知识点(补充ALGORITHM = MERGE/TEMPTABLE/UNDEFINED)
MySQL 快速删除大量数据（千万级别）的几种实践方案——附源码
MySQL实现序列（Sequence）效果以及在Mybatis中如何使用这种策略
SQL insert into 语句的写法
PowerBuilder连接SQLServer失败 SQL State：‘28000’
MySql错误：mysqld: Can't create directory '/usr/local/mysql/data/
SQL Server数据库性能优化（一）之 优化SQL 语句

随便看看