解决MySQL only_full_group_by错误

解决MySQL only_full_group_by错误

参考文章：https://blog.csdn.net/weixin_43064185/article/details/99646535

1. 问题

在执行sql语句的分组时我们有时会遇到以下的问题：

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘trial.B.dname’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
​

错误1055 (42000):SELECT list的表达式#1不在GROUP BY子句中，并且包含非聚合列的trial.B。dname不依赖于GROUP BY子句中的列;这与sql_mode=only_full_group_by不兼容

原因 ：mysql 5.7及以上版本默认的sql配置是:sql_mode=“ONLY_FULL_GROUP_BY”，这个配置严格执行了"SQL92标准"
可以通过SELECT VERSION()查询是否是版本问题
通俗的解释就是：
输出的结果是叫target list，就是select后面跟着的字段，还有一个地方group by column，就是
group by后面跟着的字段。由于开启了ONLY_FULL_GROUP_BY的设置，如果一个字段没有在target list和group by字段中同时出现，或者是聚合函数的值的话，那么这条sql查询是被mysql认为非法的，会报错误。

聚合字段：可以理解为结果很重要的
非聚合字段：取值是其第一个匹配到的字段内容，可以理解为结果很随意-不重要

那么就直接进入正题

2. 解决

在解决问题之前我们先查看一下我们MySQL的模式（方便copy）

查看sql_mode

SELECT @@sql_mode;

我们就会得到如下的结果

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

接下来就是解决问题了,我们有三种解决该问题的方法

第一种-永久关闭only_full_group_by模式：

• 去掉ONLY_FULL_GROUP_BY
• 修改配置文件：
  
• 添加这个字段

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION  

第二种-临时关闭only_full_group_by模式：

• 打开cmd登录进数据库
• 然后执行

set global sql_mode=‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION’;

这个方法只在当前登录有效，数据库重启就需要重新设置
第三种-使用ANY_VALUE(非聚合列)：

SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

原理就是从分组中，随意获取一个数据，前提也就是你不关心组数据中的不同情况。简单点就是和去重差不多的

3. 总结

来个小小的总结：

这个问题最经常出现的地方是分组时在同一个组里面的数据是各不相同的（这个问题可以使用ANY_VALUE(非聚合列)解决，但是前提是不关心这个结果的）和 查询多个字段并且对一个字段使用聚合函数，那么其他字段有多个值而使用聚合函数的就只有一个值（这种情况可以考虑进行联表查询）

4. 扩展

SQL92标准的group by :
SELECT、HAVING、ORDER后的非聚合字段必须和GROUP BY后的字段保持完全一致

SQL 99标准的group by:
如果group by后面的字段是主键（唯一键），而且非聚合字段是函数依赖group by后字段的，那么可以将这些非聚合字段放在SELECT、HAVING、ORDER BY的语句之后。