mysql：报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonag

缺乏、安全感 2023-09-29 15:46 12阅读 0赞

# 声明： #

*  原文作者【CSDN】：**lankun66**
 *  原文地址：**https://blog.csdn.net/qq\_44625080/article/details/104043682**

# 前言 #

*  最新在开发中遇到了一个问题，就是在代码书写中我用了 GroupBy做去重处理。在开发环境测试是好的，但是发布到测试就报了如下错误。
 *  报错：**Expression \#1 of SELECT list is not in GROUP BY clause and contains nonaggre**
 *  后来问运维兄弟才知道：测试环境的 mysql 升级了：**5.7.38版本**
 *  下面就是问题原因，大家可以参考一下。处理方法很简单，叫运维兄弟修改一下配置文件，重启mysql 即可。

#####  #####

#### 1.描述： ####

**数据库中的表grades为**

id	 |	gro	| name	| score
    ---------------------------
    1	 |	A	| 张三	| 20
    2	 |	B	| 李四	| 19
    3	 |  B	| 王五	| 17
    4	 |  C	| 赵六	| 18

**SQL语句**：

select name, gro from grades group by gro

**返回**：

**Expression \#1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘grades.name’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql\_mode=only\_full\_group\_by**

同样的SQL语句，在mysql5.5.43的数据库中报错，在5.7.5的mysql数据库中却正常。

#### 2.**分析**： ####

对于上述的报错信息，在下的理解是select字段里包含了没有被group by条件唯一确定的字段name。

因为执行sql\_make\_group语句实际上把两行纪录李四和王五合并成一行，搜索引擎不知道该返回哪一条，所以报错。

#### 3.**原因：** ####

MySQL 5.7.5后only\_full\_group\_by成为sql\_mode的默认选项之一，这可能导致一些sql语句失效。

#### 4.解决方法： ####

**vim /etc/mysql/conf.d/mysql.cnf**

`在配置文件my.cnf中关闭sql_mode=ONLY_FULL_GROUP_BY.。msqyl的默认配置是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去掉，也可以去掉所有选项设置成sql_mode=，如果你确信其他选项不会造成影响的话`。

复制并粘贴，如有\[mysqld\]，则放到 \[mysqld\]最后，如没有则放到配置文件最下方即可

[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

**wq**保存并退出

重启MySQL

*  **service mysql restart**

mysql：报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonag

发表评论取消回复

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

相关阅读

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

相关 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 nonaggre”解决方法

相关 mysql报错：ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nona

相关 mysql：报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonag

相关 MySQL报错：Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘xx

相关 Mysql 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregate 报错解决方案

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

相关解决MySQL报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonag...

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