关于 mysql 版本过低导致 group by 操作出现的问题

本贴最后更新于 406 天前,其中的信息可能已经天翻地覆

一、问题描述

场景是这样的,当天在部署正式库测试时发现页面报错,报错具体信息如下:

SQL_ERROR_INFO: "In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'base_table.user_id'; this is incompatible with sql_mode=only_full_group_by"

翻译过来就是: "在没有 GROUP BY 的聚合查询中,SELECT 列表的表达式 1 包含非聚合的列'base_table.user_id';这与 sql_mode=only_full_group_by 不兼容"。

二、原因分析

但在测试库运行时确没有这个问题,所以我就很纳闷,寻思是不是两个服务器不一样导致的,果然,查看 mysql 版本时,发现正式库服务器用的是 mysql5.7,测试库是 mysql8.0,然后查阅资料发现,mysql5.7 对于 group by 聚合操作,如果在 select 中的列没有在 group by 中出现,那么这个 SQL 是不合法的,因为列不在 group by 从句中,所以设置了 sql_mode=only_full_group_by 的数据库,在使用 group by 时就会报错。而 5.7 版本以后,默认是开启了该模式,所以不会出错。

三、解决方案

方案一:

把在 select 中的列且没有在 group by 中出现的列补齐
例:
image.png
改为
image.png

方案二:

1、执行以下命令看是否开启了 only_full_group_by 属性

select @@global.sql_mode

2、去掉 ONLY_FULL_GROUP_BY,重新设置值

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';

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

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';

方案三:

在配置文件下新增配置,然后保存重启即可

[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

  • MySQL

    MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    675 引用 • 535 回帖
  • 一些有用的避坑指南。

    69 引用 • 93 回帖

相关帖子

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...