一、完整报错内容
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