MySQL版本导致的问题（Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated）

遇到的问题

mysql执行语句的时候报错：

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘study_mysql.emp.EMPNO’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

---

原因：（主要针对mysql5.7.5之后的版本，之前的版本没有这个问题）

SQL 标准中不允许 SELECT 列表，HAVING 条件语句，或 ORDER BY 语句中出现 GROUP BY 中未列表的可聚合列。而 MySQL 中有一个状态ONLY_FULL_GROUP_BY 来标识是否遵从这一标准，mysql5.7.5之后默认为开启状态。

所以像这样的语句是不可以的：

SELECT gender, 
       last_name 
FROM   employees 
GROUP  BY gender 

将 last_name 从 SELECT 中移除或将其添加到 GROUP BY 中都可以修复

#
SELECT gender,
FROM   employees 
GROUP  BY gender 

#
SELECT gender,
last_name
FROM employees
GROUP BY gender,
last_name

但这样的修改查询出来就可能就不是我们想要的结果了

---

解决问题

有三种方式

关闭ONLY_FULL_GROUP_BY

这个方式有两种方法

直接执行sql语句修改

修改后下次启动mysql依旧会报错

#查看变更前的sql_mode
SELECT @@sql_mode;
#修改sql_mode
SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY,',''));
#再次查看变更后的sql_mode
SELECT @@sql_mode;

找到 MySQL 配置文件修改并保存

mysql的配置文件名为my.ini或者my.cnf找到后修改配置即可

可能原来的sql_mode值：

[mysqld]
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

需要修改成

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

如果文件中没有 sql_mode 配置项可手动添加上，有的话则选择替换

因为 ONLY_FULL_GROUP_BY 更加符合 SQL 标准，所以不建议关掉

使用ANY_VALUE()

可以通过 ANY_VALUE() 来改造查询语句以避免报错。

使用 ANY_VALUE() 包裹的值不会被检查，跳过该错误。所以这样是可以的：

SELECT gender,ANY_VALUE(last_name) 
FROM   employees 
GROUP  BY gender 

添加列间的依赖

像这个示例中，

# 
SELECT gender, 
       last_name 
FROM   employees 
GROUP  BY gender 

假如我们让 gender 变成不重复的主键，last_name 便与 gender 产生了一种关系，即 gender 可唯一确定 last_name。此时便可进行 GROUP BY 了。因为，之所以报错是因为在进行聚合的时候有不能确定的列参与了进来。

总结

一般 GROUP BY 会与另外的聚合函数配合使用，比如 COUNT(), SUM() 等。查询所有列无差别地进行 GROUP BY 的情况并不是正常的使用姿势