MySQL错误-this is incompatible with sql_mode=only_full_group_by完美解决方案

news/发布时间2024/5/18 15:13:09

项目场景

有时候，遇到数据库重复数据，需要将数据进行分组，并取出其中一条来展示，这时就需要用到group by语句。
但是，如果mysql是高版本，当执行group by时，select的字段不属于group by的字段的话，sql语句就会报错。报错信息如下：

Expression #1 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

问题描述

1.表结构

CREATE TABLE `t_iov_help_feedback` (`ID` INT(11) NOT NULL  AUTO_INCREMENT COMMENT '主键ID',`USER_ID` INT(255) DEFAULT NULL  COMMENT '用户ID',`problems` VARCHAR(255) DEFAULT NULL COMMENT '问题描述',`last_updated_date` DATETIME DEFAULT NULL COMMENT '最后更新时间',PRIMARY KEY (`ID`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

2.表数据

在这里插入图片描述

3.sql语句

1）查询group by的字段（正常）

SELECT USER_ID  FROM  t_iov_help_feedback GROUP BY USER_ID;

在这里插入图片描述

SELECT MAX(ID),USER_ID  FROM  t_iov_help_feedback GROUP BY USER_ID;

在这里插入图片描述

2）查询非group by的字段（报错）

在这里插入图片描述

报错什么意思呢？
一句话概括：“错误代码1055与sql_mode = only_full_group_by不兼容”
翻译：

“错误代码：1055。SELECT列表的表达式＃1不在GROUP BY子句中，并且包含非聚合列’test.t_iov_help_feedback.ID’，它在功能上不依赖于GROUP BY子句中的列; 这与sql_mode = only_full_group_by不兼容”

原因分析

  • 一、原理层面
    这个错误发生在mysql 5.7.5 版本及以上版本会出现的问题：
    mysql 5.7.5版本以上默认的sql配置是:sql_mode=“ONLY_FULL_GROUP_BY”，这个配置严格执行了"SQL92标准"。
    很多从5.6升级到5.7时，为了语法兼容，大部分都会选择调整sql_mode，使其保持跟5.6一致，为了尽量兼容程序。

  • 二、sql层面
    在sql执行时，出现该原因，简单来说就是：
    由于开启了ONLY_FULL_GROUP_BY的设置，如果select 的字段不在 group by 中，
    并且select 的字段未使用聚合函数（SUM,AVG,MAX,MIN等）的话，那么这条sql查询是被mysql认为非法的，会报错误…

验证是否此原因：

1.查询数据库版本的语句

SELECT VERSION();

在这里插入图片描述

可以看到，我这里数据库版本是：8.0.16，大于5.7.5了

2. 查看sql_mode的语句

select @@GLOBAL.sql_mode;

在这里插入图片描述
查询出来的值为：

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

可以看到，sql_mode开启了only_full_group_by 属性

解决方案

解决方案一：使用函数ANY_VALUE()包含报错字段

将上述报错语句改成：

SELECT ANY_VALUE(ID),USER_ID,ANY_VALUE(problems),ANY_VALUE(last_updated_date) FROM  t_iov_help_feedback GROUP BY USER_ID;

在这里插入图片描述
可以看到，结果能正常查询了，根据需要自己改查询字段的别名就行。

ANY_VALUE()函数说明：

MySQL有any_value(field)函数，它主要的作用就是抑制ONLY_FULL_GROUP_BY值被拒绝。
这样sql语句不管是在ONLY_FULL_GROUP_BY模式关闭状态还是在开启模式都可以正常执行，不被mysql拒绝。
any_value()会选择被分到同一组的数据里第一条数据的指定列值作为返回数据。
官方有介绍，地址：https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value

解决方案二：通过sql语句暂时性修改sql_mode

去掉ONLY_FULL_GROUP_BY，重新设置值

SET @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

上面是改变了全局sql_mode，对于新建的数据库有效。对于已存在的数据库，则需要在对应的数据库下执行：

SET sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

问题：

重启mysql数据库服务之后，ONLY_FULL_GROUP_BY还会出现，所以这只是暂时性的。

备注：
网上有些朋友提供的sql语句如下：

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

但是却执行不了，报sql语法错误：
在这里插入图片描述

这时只需要加上单引号即可：
在这里插入图片描述

但是，添加了单引号仍然报错：
在这里插入图片描述

这里说sql_mode不能设置NO_AUTO_CREATE_USER这个值，那直接去掉这个值就行了呗，也就是上面我提供的值。

解决方案三：通过配置文件永久修改sql_mode

mysql安装在服务器上和安装在本地，修改配置文件的方式有点区别。

1、Linux下修改配置文件

1）登录进入MySQL
使用命令 mysql -u username -p 进行登陆，然后输入密码，输入SQL：

show variables like ‘%sql_mode’;

在这里插入图片描述

2）编辑my.cnf文件
文件地址一般在：/etc/my.cnf，/etc/mysql/my.cnf
使用vim命令编辑文件，不知道vim命令怎么使用的，可以参考我的另外篇文章：Linux中使用vi工具进行文本编辑
找到sql-mode的位置，去掉ONLY_FULL_GROUP_BY
然后重启MySQL；
有的my.cnf中可能没有sql-mode，需要追加：

sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

注意要加入到[mysqld]下面，如加入到其他地方，重启后也不生效，具体的如下图：
在这里插入图片描述3）

3）修改成功后重启MySQL服务

service mysql restart

重启好后，再登录mysql，输入SQL：show variables like ‘%sql_mode’; 如果没有ONLY_FULL_GROUP_BY，就说明已经成功了。
在这里插入图片描述

如果还不行，那么只保留STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION 即可
追加内容为：

sql-mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

2、window下修改配置文件

1）找到mysql安装目录，用记事本直接打开my.ini文件
在这里插入图片描述

2）编辑my.ini文件，在[mysql]标签下追加内容

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

在这里插入图片描述

3）重启mysql 服务
在这里插入图片描述

备注：
网上有些提供了sql_mode的值，却导致重启mysql服务启动不了

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 值中 “NO_AUTO_CREATE_USER” 这个属性去掉即可。

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.bcls.cn/KWLa/1500.shtml

如若内容造成侵权/违法违规/事实不符，请联系编程老四网进行投诉反馈email:xxxxxxxx@qq.com，一经查实，立即删除！

相关文章

MathType里怎么输入手写字体

MathType里怎么输入手写字体

这篇博客只是简单记录一下。有些论文的公式里会用到手写字体，例如这种： 在MathType里输入，首先输入一个正常字母，选中——样式——其他——对话框里选择“Euclid Math One”即可。
阅读更多...
pom.xml常见依赖及其作用

pom.xml常见依赖及其作用

1.org.mybatis.spring.boot下的mybatis-spring-boot-starter：这个依赖是mybatis和springboot的集成库，简化了springboot项目中使用mybatis进行持久化操作的配置和管理 2.org.projectlombok下的lombok：常用注解Data、NoArgsConstructor、AllA…
阅读更多...
高防服务器是怎样进行防御的？

高防服务器是怎样进行防御的？

随着互联网的发展，网络攻击和恶意流量也日益增多，高防服务器作为企业网络安全的重要保障，越来越受到关注。那么，高防服务器是怎样进行防御的呢？ 高防服务器主要是指具备防御DDoS攻击、CC攻击、7x24小时实时防御网站入…
阅读更多...
内核移植学习

内核移植学习

内核移植 内核移植就是指将RT-Thread内核在不同的芯片架构、不同的板卡上运行起来。 移植可分为CPU架构移植和BSP板级支持包移植两部分。 CPU架构移植 在嵌入式领域有多种不同CPU架构，例如Cortex-M、ARM920T、MIPS32、RISC-V等等。 为了使RT-Thread能够在不同C…
阅读更多...
知识图谱：py2neo将csv文件导入neo4j

知识图谱：py2neo将csv文件导入neo4j

文章目录 安装py2neo创建节点-连线关系图导入csv文件删除重复节点并连接边 安装py2neo 安装python中的neo4j操作库：pip install py2neo 安装py2neo后我们可以使用其中的函数对neo4j进行操作。 图数据库Neo4j中最重要的就是结点和边（关系）&a…
阅读更多...
22-k8s中pod的调度-亲和性affinity

22-k8s中pod的调度-亲和性affinity

一、概述 在k8s当中，“亲和性”分为三种，节点亲和性、pod亲和性、pod反亲和性； 亲和性分类名称解释说明nodeAffinity节点亲和性通过【节点】标签匹配，用于控制pod调度到哪些node节点上，以及不能调度到哪些node节点上&…
阅读更多...
C语言新手写函数中出现数组时运行bug的解决

C语言新手写函数中出现数组时运行bug的解决

一.发现问题&#xff1a; 这是我今天写代码的一小部分&#xff0c;是创建一个数组&#xff0c;然后函数init&#xff08;&#xff09;是初始化数组&#xff0c;代码如下&#xff1a; void init(int arr[10],unsigned int k) {int i 0;for (i 0; i < k; i) {arr[i] 0;} …
阅读更多...
C# OpenCvSharp DNN Low Light image Enhancement

C# OpenCvSharp DNN Low Light image Enhancement

目录 介绍 效果 模型信息 项目 代码 下载 C# OpenCvSharp DNN Low Light image Enhancement 介绍 github地址&#xff1a;https://github.com/zhenqifu/PairLIE 效果 模型信息 Model Properties ------------------------- ------------------------------------------…
阅读更多...
STM32——OLED菜单

STM32——OLED菜单

文章目录 一.补充二. 二级菜单代码 简介&#xff1a;首先在我的51 I2C里面有OLED详细讲解&#xff0c;本期代码从51OLED基础上移植过来的&#xff0c;可以先看完那篇文章&#xff0c;在看这个&#xff0c;然后按键我是用的定时器扫描不会堵塞程序,可以翻开我的文章有单独的定时…
阅读更多...
P2P 应用

P2P 应用

P2P 工作方式概述 在 P2P 工作方式下&#xff0c;所有的音频/视频文件都是在普通的互联网用户之间传输。 1 具有集中目录服务器的 P2P 工作方式 Napster 最早使用 P2P 技术&#xff0c;提供免费下载 MP3 音乐。 Napster 将所有音乐文件的索引信息都集中存放在 Napster 目录服…
阅读更多...
vulhub中Apache Log4j Server 反序列化命令执行漏洞复现（CVE-2017-5645）

vulhub中Apache Log4j Server 反序列化命令执行漏洞复现（CVE-2017-5645）

Apache Log4j是一个用于Java的日志记录库&#xff0c;其支持启动远程日志服务器。Apache Log4j 2.8.2之前的2.x版本中存在安全漏洞。攻击者可利用该漏洞执行任意代码。 1.我们使用ysoserial生成payload&#xff0c;然后直接发送给your-ip:4712端口即可。 java -jar ysoserial-…
阅读更多...
上网行为监控软件大盘点：好用的上网行为管理软件一览

上网行为监控软件大盘点：好用的上网行为管理软件一览

随着企业对于员工工作效率和网络安全性的日益关注&#xff0c;上网行为监控软件成为了许多企业不可或缺的管理工具。 这些软件能够全面记录和分析员工的上网行为&#xff0c;帮助企业提升工作效率、保障数据安全&#xff0c;并规范员工的上网习惯。 一、上网行为管理软件定义…
阅读更多...
Django模板（四）

Django模板（四）

一、include标签 加载一个模板，并在当前上下文中进行渲染。这是一种在模板中 “包含” 其他模板的方式 简单的理解：在当前模板中引入另外一个模板内容 1.1、使用方法 模板名称可以是变量，也可以是单引号或双引号的硬编码（带引号）的字符串 {% include "foo/bar.ht…
阅读更多...
网络协议与攻击模拟_17HTTPS 协议

网络协议与攻击模拟_17HTTPS 协议

HTTPShttpssl/tls 1、加密算法 2、PKI&#xff08;公钥基础设施&#xff09; 3、证书 4、部署HTTPS服务器 部署CA证书服务器 5、分析HTTPS流量 分析TLS的交互过程 一、HTTPS协议 在http的通道上增加了安全性&#xff0c;传输过程通过加密和身份认证来确保传输安全性 1、TLS …
阅读更多...
maven 打包命令

maven 打包命令

Maven是基于项目对象模型(POM project object model)&#xff0c;可以通过一小段描述信息&#xff08;配置&#xff09;来管理项目的构建&#xff0c;报告和文档的软件项目管理工具。 Maven的核心功能便是合理叙述项目间的依赖关系&#xff0c;通俗点讲&#xff0c;就是通过po…
阅读更多...
php基础学习之文件包含

php基础学习之文件包含

描述 在一个php脚本中&#xff0c;将另一个php文件包含进来&#xff0c;合作实现某种功能 这个描述看起来似乎和C/Java等语言的头文件/包有点类似&#xff0c;但本质是不一样的 打个比方&#xff1a; C/Java的头文件/包更像是一个工具箱&#xff0c;存放各种很完善的工具&#…
阅读更多...
【Linux】线程概念和线程控制

【Linux】线程概念和线程控制

目录 一、Linux 二、线程 三、线程控制 一、Linux Linux是一种开源的类Unix操作系统内核&#xff0c;它是由林纳斯托瓦兹&#xff08;Linus Torvalds&#xff09;在1991年首次发布的。Linux被广泛应用于各种设备和系统&#xff0c;包括个人电脑、服务器、嵌入式系统等。 L…
阅读更多...
机器人十大前沿技术（2023-2024年）

机器人十大前沿技术（2023-2024年）

2023-2024年机器人十大前沿技术 1. 具身智能与垂直大模型 具身智能是指拥有自主感知、交互和行动能力的智能体&#xff0c;能够与环境进行实时互动&#xff0c;从而实现对环境的理解和适应。 “大模型”是指在深度学习和人工智能领域中&#xff0c;使用大量参数和数据进行训…
阅读更多...
常见的几种Web安全问题测试简介

常见的几种Web安全问题测试简介

Web项目比较常见的安全问题 1.XSS(CrossSite Script)跨站脚本攻击 XSS(CrossSite Script)跨站脚本攻击。它指的是恶意攻击者往Web 页面里插入恶意html代码&#xff0c;当用户浏览该页之时&#xff0c;嵌入其中Web 里面的html 代码会被执行&#xff0c;从而达到恶意用户的特殊…
阅读更多...
GitCode配置ssh

GitCode配置ssh

下载SSH windows设置里选“应用” 选“可选功能” 添加功能 安装这个 坐等安装&#xff0c;安装好后可以关闭设置。 运行 打开cmd 执行如下指令&#xff0c;启动SSH服务。 net start sshd设置开机自启动 把OpenSSH服务添加到Windows自启动服务中&#xff0c;可避免每…
阅读更多...
推荐文章
最新文章

Copyright @ 2022~2023

友情链接： 我的编程经验分享 一条长河网 尧图网站开发 尧图建网站 尧图建网站 广利置业