关于使用group by 报错

本知识点曾经作为面试题存在

报错

常出现在命令行使用group by会报错，在Navicat等数据库工具使用不会报错。

mysql> select user,host from mysql.user group by host;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mysql.user.User' 
which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

原因

sql_mode设置了only_full_group_by这个参数。

此参数存在于mysql 5.7版本中，于5.6跟8.0版本取消。

ONLY_FULL_GROUP_BY的作用
使用group by 时select后的条件列(即非主键列)需要被函数使用否则会报错，因为数据可能有重复，重复的数据需要函数来处理。

#如下语句会在mysql 5.7中在命令行使用时会报错
mysql> select user,host from mysql.user group by host;

#host使用group_concat()函数包裹就不会出错
mysql> select user,group_concat(host) from mysql.user group by user;

查看sql_mode参数

mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@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 |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
#如上表示开启了ONLY_FULL_GROUP_BY

解决方法一

修改配置文件

#修改配置文件为
$ vim /etc/my.conf
[mysqld]
user=mysql
basedir=/usr/local/mysql5.7
datadir=/usr/local/mysql5.7/data
socket=/tmp/mysql.sock
server_id=6
port=3306
#这里添加sql_mode参数，去掉ONLY_FULL_GROUP_BY
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]
socket=/tmp/mysql.sock

$ /etc/init.d/mysqld stop
#以配置文件重新启动
$ /usr/local/mysql5.7/bin/mysqld --defaults-file=/etc/my.conf --user=root

解决方法二

修改global.sql_mode变量

mysql> 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_mode的only_full_group_by，那么使用group by有重复数据的只会显示一条。

学习来自：猿医生博客，老男孩深标DBA课程 第三章