编程知识 cdmana.com

MySQL5.7之group by语法问题

一、基础环境

linux centos 7, mysql 5.7.26

二、问题描述

使用group by 进行分组查询时,提示异常: SELECT list is not in GROUP BY clause and contains nonaggregated column ‘XXX’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode =only_full_group_by

三、问题原因

大概查了一下相关原因,意思是:当你使用group by 做分组查询时,分组使用的列,那么你select就必须带上分组的列,是因为mysql5.7以上版本增加了sql_mode=ONLY_FULL_GROUP_BY 的设定,这一点在异常描述里面也提示了。

举个例子: select id,name from Users group by id,sex; 如果这里select的列不带上sex,那么就会报上面这个异常。

四、解决方式

按照实际业务操作,上面例子这种sql写法是存在的,并且应用挺多,那么我们想让例子的这种写法符合mysql规范的话,就需要更改mysql的sql_mode配置了,具体操作如下:

  1. 通过sql的方式更改sql_mode(当前生效,重启mysql后失效)

进入mysql客户端,执行以下命令:

select @@GLOBAL.sql_mode;
+--------------------------------------------------------------------------------------------------------------------------------------+
| @@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, 执行以下设置即可:

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';
  1. 通过mysql配置my.cnf更改sql_mode(一劳永逸)

找到服务器mysql对应的my.cnf配置文件,例如这里我的配置文件在/etc/my.cnf

  • vim /etc/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
  • 保存,退出,并重启mysql: service mysql restart (这里因为我配置了mysql为系统服务)
  • 重新查看sql_mode, 已经放开了ONLY_FULL_GROUP_BY设定

五、注意事项

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

版权声明
本文为[执笔记忆的空白]所创,转载请带上原文链接,感谢
https://cloud.tencent.com/developer/article/1764746

Scroll to Top