项目出现SELECT list is not in GROUP BY clause and contains nonaggregated column时的解决方案

- 日理万妓 2023-03-13 06:35 78阅读 0赞

## 引言 ##

今天正在部署开源项目的时候，小猿正常的完成了程序导入，数据库安装、以及各项配置均正常，整个项目也能正常启动运行，但是当我访问系统时就出现了一个莫名其妙的问题

## 问题描述 ##

查看idea后台，系统提示的错误如下：

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression \#1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘wms.wm\_to\_down\_goods.goods\_name’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql\_mode=only\_full\_group\_by  
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(NativeMethod)  
…  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70]  
查看了网上的若干网上的内容，小猿参考了两片比较重要的博文:  
https://blog.csdn.net/weixin\_41991232/article/details/82803170  
https://blog.csdn.net/newmemory/article/details/84964617  
总结后确定问题的原因的是mysql 5.7.5之后版本默认启用了only\_full\_group\_by SQL模式，所以MySQL会不识别选择列表、条件或顺序列表引用的查询 ，当jdbc发送一个GROUP BY语句时，则表示sql数据库识别group by时就会出现问题。

## 问题解决步骤 ##

对于这个问题网上大概提出两种方式，一种是用sql语句来修改默认配置，另一种是直接修改mysql的配置文件。

### sql语句修改默认配置 ###

首先查询global.sql\_mode，执行：

select @@global.sql_mode

结果如下  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 1]  
我们先在要做的就是关掉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’;

即可完成对mysql的全局sql模块的本次配置，但当再次启动sql时同会出现上述问题。

### 通过配置文件修改global.sql\_mode配置 ###

1.用xshell打开sql配置文件  
打开sql所在的文件，小猿的数据库版本是sql 5.7  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 2]  
所以必须配置global.sql\_mode  
小猿的文件配置都放在my.cnf文件中  
![在这里插入图片描述][20200512170143173.png]  
执行命令

vim my.cnf

开始修改，在文件的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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 3]  
重新启动mysql执行命令

service restart mysql

![在这里插入图片描述][20200512170624557.png]  
再次启动项目就发现没有任何错误。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 4]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70]: /images/20230312/e5005549cf3c4de5b936361597056583.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 1]: /images/20230312/7bdb19cab790448d897454d807a86b96.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 2]: /images/20230312/76837efe6e8245fc89983abe732f0a02.png
[20200512170143173.png]: /images/20230312/4d02a9927b5545a3b8ec2bd9e5d5aa43.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 3]: /images/20230312/490e25497377457e8a0f8b051879f9a1.png
[20200512170624557.png]: /images/20230312/ff564e7005a94dd89449e48bfe16ed60.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70 4]: /images/20230312/781bac8d86fc4c34b6209da2cd945273.png

项目出现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.

相关项目出现SELECT list is not in GROUP BY clause and contains nonaggregated column时的解决方案

相关 Expression #2 of 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.

相关【MySQL】Expression #1 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 #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘sss.mon

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