MySQL错误SELECT list is not in GROUP BY clause and contains nonaggregated column解决”

桃扇骨 2022-04-05 10:25 207阅读 0赞

MySQL报错如下：

> Expression \#12 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘shaogang.role\_menu.role\_menu\_id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql\_mode=only\_full\_group\_by

相同的数据库，相同的工程，在本地就好使，但是传到服务器就不行，一开始以为是表和工程的问题，各种删，重建，结果发现不是！  
[转载博客][Link 1]，后来在这篇博客上发现了解决办法，实测方法1不好用，用方法2成功解决，完成部署！  
说好像是因为MySQL版本的问题，可是我的MySQL本地版本和服务器版本都是MySQL5.7，然而不知道为啥不好使！  
解决办法：

1.  用vim打开mysql.cnf文件

> sudo vim /etc/mysql/conf.d/mysql.cnf

1.  滚动到文件底部赋值如下内容，然后保存并退出输入模式

> \[mysqld\]  
> sql\_mode=STRICT\_TRANS\_TABLES,NO\_ZERO\_IN\_DATE,NO\_ZERO\_DATE,ERROR\_FOR\_DIVISION\_BY\_ZERO,NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION

1.  重启mysql服务器

> sudo service mysql restart

**总结：**  
遇到bug时，千万不要自以为是的去解决，一定要先仔细查看bug是什么，然后去网上找对应的有没有解决办法！

## 后记 ##

对于MySQL 5.7版本的这个问题的处理，网上还有另外一种办法：

> 打开navcat，  
> 用sql查询：  
> select @@global.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，重新设置值。  
> set @@global.sql\_mode  
> =‘STRICT\_TRANS\_TABLES,NO\_ZERO\_IN\_DATE,NO\_ZERO\_DATE,ERROR\_FOR\_DIVISION\_BY\_ZERO,NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION’;

但亲测该方法**并不实用**，因为这种修改只是暂时性的，一旦重启服务器就会变回原来的样子，即  
select @@global.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值仍然存在！**

[Link 1]: https://blog.csdn.net/hq091117/article/details/79065199

MySQL错误SELECT list is not in GROUP BY clause and contains nonaggregated column解决”

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Expression #7 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘studentsc

相关 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

相关 MySQL5.7:of SELECT list is not in GROUP BY clause and contains nonaggregated column 'userinfo.

相关 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column

相关 mysql无法使用分组查询 SELECT list is not in GROUP BY clause and contains nonaggregated column

相关 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'userinfo.

相关 mysql5.7 ORDER BY clause is not in GROUP BY clause and contains nonaggregated column

相关【MySQL】Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 异常解决

相关 MySQL：错误Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregate

相关 MySQL错误SELECT list is not in GROUP BY clause and contains nonaggregated column解决”