解决MySQL-this is incompatible with sql_mode=only_full_group_by 问题(提供window、Linux、docker解决方法和流程)

这篇具有很好参考价值的文章主要介绍了解决MySQL-this is incompatible with sql_mode=only_full_group_by 问题(提供window、Linux、docker解决方法和流程)。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

一、错误原因分析

出现这个问题是因为,对于GROUP BY操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中。
也就是说如果我分组查询,比如我表里有三个字段 id、name、type,我根据type分组查询,那么结果集里就不能包含name字段。

# 错误查询 会出现this is incompatible with sql_mode=only_full_group_by
SELECT `name`,`type` FROM user_01 GROUP BY `type`;

# 正确查询 不会出现问题
SELECT `type` FROM user_01 GROUP BY `type`;

二、原理分析&解决方案分析

  • 要想解决这个问题首先要知道MySQL的 sql_mode 是什么,sql_mode 是个很容易被忽视的变量,sql_mode 是规范一些数据库校验规则,比如这里出现的sql_mode=only_full_group_by 问题,only_full_group_by 就是一个校验规则,会规定分组查询结果集不能有GROUP BY中没有出现的列。

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

  • 有两种解决逻辑:

    • 第一种:就是顺应这个规则,别在GROUP BY是返回额外的列,当然,竟然SQL都这样写了肯定是需要额外的列了,我们看第二种方法
    • 第二种:放开限制就行,将sql_mode 中的 only_full_group_by 剔除就行。

二、临时解决(不需要重启MySQL,适配所有版本和任意安装方式的MySQL,重启MySQL后会失效)

临时解决这个问题可以不用重启MySQL服务,,通过 set global 命令直接改就行。

1、查看当前 sql_mode (我这里MySQL的版本是8.0,其它版本也是一样的做法)

登录数据库执行下面命令可以看到当前包含的 sql_mode ,不同版本MySQL结果可能不同,但是我们的问题是因为 only_full_group_by 引起的,我们看看里面是否有这个。

SHOW VARIABLES LIKE "sql_mode";

this is incompatible with sql_mode=only_full_group_by,运维,数据库,mysql,sql,linux,容器,docker
这里可以看到,我的 sql_mode 中第一个就是 only_full_group_by

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

2、临时修改 sql_mode

将刚刚查询到的 sql_mode 删除里面的 ONLY_FULL_GROUP_BY,重新设置到系统配置中即可。

# 修改全局配置 sql_mode 
set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
# 修改session配置 sql_mode 
set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

PS:如果是使用代码(JDBC)连接的数据库,修改完成之后最好重启一下代码服务,mysql服务不用重启,如果不重启可能还是会出现该问题,重启后生效。

三、彻底解决(需要重启MySQL才会生效)

要彻底解决这个问题就是将临时解决方案中的 sql_mode 通过配置文件设置即可,建议是和临时方案一起使用,先通过临时方案让业务服务可用,然后在配置文件也配置 sql_mode 下一次MySQL重启后生效。

1、window 配置 sql_mode

window 上的 MySQL 配置文件默认是在 C:\Program Files\MySQL\MySQL Server 8.0\my-default.ini,打开这个 my-default.ini 文件找到[mysql],在 [mysqld] 下设置 sql_mode ,将通过SHOW VARIABLES LIKE "sql_mode";查询出来的 sql_mode 剔除 ONLY_FULL_GROUP_BY 即可。

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

this is incompatible with sql_mode=only_full_group_by,运维,数据库,mysql,sql,linux,容器,docker

  • 重启MySQL:
    • 打开Windows的服务管理器,可以通过快捷键Win+R打开运行窗口,输入services.msc后按回车键。

    • 找到MySQL服务,服务名称可能会叫MySQLxx(xx是版本信息),右键点击,选择停止服务。

    • 等待MySQL服务停止后,再右键点击MySQL服务,选择启动服务。

    • 重启MySQL服务后,可以通过以下命令检查MySQL是否已正常运行

重启好在MySQL客户端执行SHOW VARIABLES LIKE "sql_mode";查询 sql_mode 是否已经改变。

2、Linux 配置 sql_mode

Linux 上的 MySQL 配置文件默认是在 /etc/mysql/my.cnf,打开这个 my.cnf 文件找到[mysql],在 [mysqld] 下设置 sql_mode ,将通过SHOW VARIABLES LIKE "sql_mode";查询出来的 sql_mode 剔除 ONLY_FULL_GROUP_BY 即可。

# 打开/etc/mysql/my.cnf
vi /etc/mysql/my.cnf
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

this is incompatible with sql_mode=only_full_group_by,运维,数据库,mysql,sql,linux,容器,docker

  • 重启MySQL:
    service mysqld restartservice mysql restart

3、docker 配置 sql_mode

docker 安装的 linux版本MySQL 配置文件默认是在也是容器的 /etc/mysql/my.cnf 中,不过有点区别的是我们一般去直接改容器中的配置文件,而是会将额外配置文件路径挂载出来,而且容器中是没有 vi 这种工具不方便直接改配置,这里会对有做挂载和没有做挂载的修改方式都说做说明。

使用docker安装的MySQL的额外配置文件是在容器的 /etc/mysql/conf.d 目录的,MySQL会读取这个目录下所以以 .cnf 结尾的文件,如果有对这个目录做挂载最好,如果没有做挂载也可用通过宿主机copy到容器内部。

1、配置文件准备

我的配置文件目录是挂载到了 /home/docker/mysql/conf.d,自己根据实际情况调整。

# 创建一个配置文件
vi /home/docker/mysql/conf.d/mysql.cnf

将下面的内容写入 mysql.cnf 保存即可

[mysqld]
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

2、如果没有做挂载需要将这个配置文件拷贝到容器内(有做挂载跳过)

docker cp /home/docker/mysql/conf.d/mysql.cnf 容器ID:/etc/mysql/conf.d/mysql.cnf

3、重启容器

docker restart 容器ID/容器名称

4、检验是否修改成功

# 进入容器
docker exec -it 容器ID/容器名称 /bin/bash
# 连接MySQL
mysql -h127.0.0.1 -uroot -p
# 查看sql_mode是否被修改成功
SHOW VARIABLES LIKE "sql_mode";

this is incompatible with sql_mode=only_full_group_by,运维,数据库,mysql,sql,linux,容器,docker文章来源地址https://www.toymoban.com/news/detail-761494.html

到了这里,关于解决MySQL-this is incompatible with sql_mode=only_full_group_by 问题(提供window、Linux、docker解决方法和流程)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包