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

安装了mysql5.7,用group by 查询时抛出如下异常:

Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'userinfo.t_long.user_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

原因:

      MySQL 5.7.5和up实现了对功能依赖的检测。如果启用了only_full_group_by SQL模式(在默认情况下是这样),那么MySQL就会拒绝选择列表、条件或顺序列表引用的查询,这些查询将引用组中未命名的非聚合列,而不是在功能上依赖于它们。(在5.7.5之前,MySQL没有检测到功能依赖项,only_full_group_by在默认情况下是不启用的。关于前5.7.5行为的描述,请参阅MySQL 5.6参考手册。)

执行以下个命令,可以查看 sql_mode 的内容。

mysql> SHOW SESSION VARIABLES;
mysql> SHOW GLOBAL VARIABLES;
mysql> select @@sql_mode;

可见session和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说明:
only_full_group_by :使用这个就是使用和oracle一样的group 规则, select的列都要在group中,或者本身是聚合列(SUM,AVG,MAX,MIN) 才行,其实这个配置目前个人感觉和distinct差不多的,所以去掉就好
官网摘抄:
官网:ONLY_FULL_GROUP_BY
Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.

As of MySQL 5.7.5, the default SQL mode includes ONLY_FULL_GROUP_BY. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default. For a description of pre-5.7.5 behavior, see the MySQL 5.6 Reference Manual.)

A MySQL extension to standard SQL permits references in the HAVING clause to aliased expressions in the select list. Before MySQL 5.7.5, enabling ONLY_FULL_GROUP_BY disables this extension, thus requiring the HAVING clause to be written using unaliased expressions. As of MySQL 5.7.5, this restriction is lifted so that the HAVING clause can refer to aliases regardless of whether ONLY_FULL_GROUP_BY is enabled.

解决:

执行以下两个命令:

mysql> 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> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

这两个命令,去掉 sql_mode 的 ONLY_FULL_GROUP_BY

见其他文章有说:
直接修改mysql配置文件(我的系统是Ubuntu16.04的,在/etc/mysql/mysql.conf.d/mysqld.cnf 中并没有sql_mode这个配置,所以直接加上就好,如果是其他系统有得修改就不用添加了)
这个方法暂时没有式。

mysql 配置信息读取顺序。

①ps aux|grep mysql|grep ‘my.cnf’

②mysql –help|grep ‘my.cnf’

/etc/my.cnf, /etc/mysql/my.cnf, /usr/local/etc/my.cnf, ~/.my.cnf 这些就是mysql默认会搜寻my.cnf的目录,顺序排前的优先。mysql按照上面的顺序加载配置文件,后面的配置项会覆盖前面的。

如果没有该文件可以自定义一个文件。然后回默认读取配置中的内容)
查看你需要修改的是哪个配置文件。我只有/etc/my.cnf 只修改这个文件即可

配置文件my.cnf通常会分成好几部分,如[client],[mysqld], [mysql]等等。MySQL程序通常是读取与它同名的分段部分,例如服务器mysqld通常读取[mysqld]分段下的相关配置项。如果配置项位置不正确,该配置是不会生效的

参考:https://stackoverflow.com/questions/37951742/1055-expression-of-select-list-is-not-in-group-by-clause-and-contains-nonaggr

这个语句没试过,先记录:

set @@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即可正常执行sql.

  • 30
    点赞
  • 113
    收藏
    觉得还不错? 一键收藏
  • 19
    评论
引用\[1\]、\[2\]和\[3\]都是关于MySQL报错的信息,其中提到了"Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column"的错误。这个错误是由于在使用GROUP BY子句进行分组查询时,SELECT列表中的某些列没有在GROUP BY子句中出现,并且这些列也没有被聚合函数包含,导致不兼容的错误。这个错误通常是由于MySQLsql_mode设置为only_full_group_by引起的。解决这个错误的方法是要么将SELECT列表中的列添加到GROUP BY子句中,要么将这些列用聚合函数进行处理,以确保查询的正确性。 #### 引用[.reference_title] - *1* *3* [MySQL报错:Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ...](https://blog.csdn.net/Saintmm/article/details/124491860)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^control_2,239^v4^insert_chatgpt"}} ] [.reference_item] - *2* [Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre](https://blog.csdn.net/weixin_44051060/article/details/105602832)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^control_2,239^v4^insert_chatgpt"}} ] [.reference_item] [ .reference_list ]
评论 19
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值