真正有效解决ONLY_FULL_GROUP_BY的问题

itlao6 DB 工具&方法评论741字数 1892阅读6分18秒阅读模式

前言:如果碰到该问题,建议其使用方案二,修改sql语句;方案一只是临时解决方式,方案二才是正途。

问题描述

报错: 如上图 在mysql 5.7版本下可能会报如下错误文章源自IT老刘-https://itlao6.com/9200.html

Error Code: 1055. Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘×××’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by文章源自IT老刘-https://itlao6.com/9200.html

原因: 是在5.7下mysql的model默认为文章源自IT老刘-https://itlao6.com/9200.html

ONLY_FULL_GROUP_BY文章源自IT老刘-https://itlao6.com/9200.html

含义 sql中select后面的字段必须出现在group by后面,或者被聚合函数包裹,不然会抛出上面的错误 如以下sql文章源自IT老刘-https://itlao6.com/9200.html

select A.column1, A.column2 from table as A group by column1 //会因为ONLY_FULL_GROUP_BY的缘故抛出上面错误
文章源自IT老刘-https://itlao6.com/9200.html

问题解决

方案一

既然是这个模式限制的问题 我们可以修改该模式,找到配置文件 修改配置即可。文章源自IT老刘-https://itlao6.com/9200.html

vim /etc/mysql/my.cnf
文件名如上所示,但是地址可能得看具体环境了文章源自IT老刘-https://itlao6.com/9200.html

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=%MYSQL_HOME%
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 设置模式
sql_mode =STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION文章源自IT老刘-https://itlao6.com/9200.html

datadir = D:\mysql-5.7.27-winx64\data文章源自IT老刘-https://itlao6.com/9200.html


注意这里的sql_mode 是配置在[mysqld]下面的
然后就是重启mysql服务文章源自IT老刘-https://itlao6.com/9200.html

方案二

我们得承认这个模式是很有意义的。而我们又想实现如下sql的效果文章源自IT老刘-https://itlao6.com/9200.html

select A.column1, A.column2 from table as A group by column1

首先我们得理解我们写的上面的sql的含义 按column1进行分组 那么结果就是这个组内的cloumn1都是相同的,但是column2却没有限制,也就是说可能相同 也可能不同,那么数据库不知道column2该返回什么值给你文章源自IT老刘-https://itlao6.com/9200.html

那么我们有两种方案文章源自IT老刘-https://itlao6.com/9200.html

在group by后面追加上column2就可以了,意义相当于按照 由column1和column2拼接成的联合字段进行分组
select A.column1, A.column2 from table as A group by column1 , column2

或者在内查询里只查询分组的字段, 各位可以参考如下形式
SELECT A.column1, A.column2 FROM TABLE A JOIN ( SELECT max(id) id, column1 FROM TABLE b GROUP BY column_1 ) C ON A.column1 = C.column1 and C.id = A.id

以上是我的一些经验 如果不对 还请留言告知文章源自IT老刘-https://itlao6.com/9200.html

补充
以下内容来之评论区,一个很好的补充和总结,谢谢 @以太的咸鱼日常文章源自IT老刘-https://itlao6.com/9200.html

MySQL5.7之后,sql_mode中ONLY_FULL_GROUP_BY模式默认设置为打开状态。
ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义,简单的说来,在此模式下,target list中的值要么是来自于聚合函数(sum、avg、max等)的结果,要么是来自于group by list中的表达式的值
MySQL提供了any_value()函数来抑制ONLY_FULL_GROUP_BY值被拒绝
any_value()会选择被分到同一组的数据里第一条数据的指定列值作为返回数据
————————————————
版权声明:本文为CSDN博主「量子玫瑰」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/qq_34637782/article/details/101029487文章源自IT老刘-https://itlao6.com/9200.html

文章源自IT老刘-https://itlao6.com/9200.html文章源自IT老刘-https://itlao6.com/9200.html
继续阅读
weinxin
我的微信公众号
微信扫一扫关注公众号,不定时更新
itlao6
  • 本文由 发表于 2022年 9月 3日 09:57:45
  • 转载请务必保留本文链接:https://itlao6.com/9200.html
评论  0  访客  0
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定