MySQL报错：Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘xx

一、完整报错内容

1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘test.test_baobiao.qty’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by, Time: 0.000000s

二、原因分析

1、表描述

1> 表结构

2> sql语句

select qty,businessDate,flag, sum(IF(flag=1, 0 - reverseQty, qty)) as sum from test_baobiao GROUP BY tradeOrderLineBO, businessDate;

3> 返回的报错信息

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘test.test_baobiao.qty’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

2、分析

从报错信息来看，它告诉我们：select字段里包含了没有被group by 条件唯一确定的字段qty

因为执行sql_make_group语句时会把两行纪录id=1和id=2两行记录合并成一行，但是搜索引擎不知道该返回哪一条，所以报错。

3、原因总结

MySQL 5.7.5后only_full_group_by成为sql_mode的默认选项之一，这可能导致一些sql语句失效。我们只需要把only_full_group_by从sql_mode中移除即可。

三、解决方案

1、直接在mysql-cli层面做设置

1> 先查询出所有的sql_mode

select @@global.sql_mode;

2> 将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_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

3> 重启MySQL客户端

如果此种方法无效（博主在navicat上操作有效），采用方法二、直接再MySQL-server侧修改sql_mode的配置

二、在MySQL-server层面修改sql_mode

vi /etc/mysql/conf.d/mysql.cnf

在配置文件my.cnf中关闭sql_mode=ONLY_FULL_GROUP_BY。
将sql_mode配置放在 [mysqld] 最后，如下：

[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

wq保存文件并退出，最后重启MySQL即可。

service mysql restart

四、效果