关于MySQL8.x group_by报错的4种解决方案

点七五

MySQL8.x group_by报错解决方案

在我们使用MySQL的时候总是会遇到各种各样的报错，让人头痛不已。
其中有一种报错，sql_mode=only_full_group_by，十分常见，每次都是老长的一串出现，然后带走你所有的好心情，如：

1.  LIMIT 0, 1000 Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.tab_test.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 0.063 sec

复制代码

由于本地安装得mysql版本未报错，但是服务器上的版本报错：

• 本机版本：
  

• 服务器版本：
  

不知道是8.x哪个版本之后SQL_MODE解除了限制，还是安装与解压版的的区别，目前发现的问题是服务器解压版应该是ini未设置SQL_MODE模式。
出现这样的报错，并不是因为你的代码写得不好，而是因为在MySQL 5.7后，MySQL默认开启了SQL_MODE严格模式，对数据进行严格校验。如果代码中含有group by聚合操作，那么select中的列，除了使用聚合函数之外的，如max()、min()等，都必须出现在group by中。
比如说，出现下面这种情况，就会报错：

1. select id,aa from tab_test group by aa

复制代码

如果改成这个样子：

1. select id,aa from tab_test group by id,aa

复制代码

或者这个样子：

1. select aa from tab_test group by aa

复制代码

就不会报错了。
当然，这样子随意的改动代码，我们可能就无法得到想要的信息了。
我们可以通过以下四种方法，解决该问题：

方法一：直接修改数据库配置

首先，打开数据库，输入

1. select @@global.sql_mode;

复制代码

这个时候，就会返回得到以下的信息：（不同电脑返回的信息可能不同）

1. ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

复制代码

如果里面包含 ONLY_FULL_GROUP_BY，那么就重新设置，在数据库中输入以下代码，去掉ONLY_FULL_GROUP_BY即可：

1. SET GLOBAL sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

复制代码

但是，当我们再一次重新启动数据库时，可能会恢复原样，还是会出现ONLY_FULL_GROUP_BY的报错，这就需要我们再一次修改数据库配置。
那有没有可以永久生效的办法呢？当然有！参考方法二，就可以使修改数据库配置永久生效！

方法二：修改数据库配置（永久生效）

修改配置文件my.ini
在[mysqld]模块下新增一行配置：

1. sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

复制代码

运行后重启，即可生效

方法三：使用 any_value() 或 group_concat()

1. any_value()：将分到同一组的数据里第一条数据的指定列值作为返回数据。 （any_value()函数就是MySQL提供的用来抑制ONLY_FULL_GROUP_BY值被拒绝的）

1. select a,any_value(b) from test group by a

复制代码

2. group_concat()：将分到同一组的数据默认用逗号隔开作为返回数据,如下图：

方法四：开动脑筋，修改代码

举个例子，在牛客的第206题中，就出现了类似的情况
SQL206 获取每个部门中当前员工薪水最高的相关信息
题目如下：

示例：

如果我们不用上面的方法，直接用下面这种方式查询，很明显会出现 ONLY_FULL_GROUP_BY 的报错：

1. select dept_no,d.emp_no,max(s.salary)
   
2. from dept_emp d
   
3. join salaries s on d.emp_no = s.emp_no
   
4. group by d.dept_no

复制代码

我们可以换一种思路：
首先查询得到2张表，一张表为员工薪资表 表a，一张表为每个部门最高员工薪资表 表b。然后通过表连接 on d.emp_no = s.emp_no 使这两张表结合，通过on a.salary = b.salary使薪资统一，即这两张表连接后的表的薪资为每个部门的最高员工薪资。最后，再通过一次查询得到每个部门中当前员工薪水最高的相关信息。

1. select a.dept_no, a.emp_no, b.salary
   
2. from
   
3.     (select d.dept_no, s.emp_no, s.salary
   
4.      from dept_emp as d join salaries as s
   
5.      on d.emp_no = s.emp_no
   
6.     ) as a
   
7.     join
   
8.     (select d.dept_no, max(s.salary) as salary
   
9.      from dept_emp as d join salaries as s
   
10.      on d.emp_no = s.emp_no
    
11.      group by d.dept_no
    
12.     ) as b
    
13.     on a.salary = b.salary and a.dept_no = b.dept_no
    
14. order by a.dept_no

复制代码

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持脚本之家。

来源:https://www.jb51.net/database/322299lee.htm
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！