先说我遇到的问题:
SELECT
c.numplate license,
count(p.pet) pet_count
FROM
car c
LEFT JOIN pet p ON c.perid = p.perid
GROUP BY p.pet;
单看这个查询语句其实没有任何问题,但是执行的时候报出下面异常:
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘mytest.c.numplate’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
这句异常的原因是group by 后面的分组字段与select后面的输出字段不一一对应,这样在mysql中就被认为是非法的
将语句修改为下面这样:
SELECT
c.numplate license,
count(p.pet) pet_count
FROM
car c
LEFT JOIN pet p ON c.perid = p.perid
GROUP BY p.pet,c.numplate;
得出结果:
那么为什么group by 后面的分组字段与select后面的输出字段不一一对应(或同时出现)在mysql中就被认为是非法的呐?通过各种百度找问题答案,终于明白了!
出现这种问题原因两种
1.原理方面:
mysql 5.7版本默认的sql配置是:“sql_mode=only_full_group_by”,这个配置严格执行了"SQL92标准。很多从5.6升级到5.7时,为了语法兼容,大部分都会选择调整sql_mode,使其保持跟5.6一致,为了尽量兼容程序。
2.sql方面:
从sql层面来说,输出的结果是target list,就是 select 后面跟着的字段,还有一个地方是 group by column,就是 group 后面跟着的字段,由于开启了 ONLY_FULL_GROUP_BY 的设置,如果没有字段在 target list 以及 group by 字段中同时出现,那么 sql 就认为此条语句是不合法的,就会抛出错误。
一、先查询一下
select @@GLOBAL.sql_mode;
得到结果:
二、解决方案
1)暂时性修改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
但是这种修改只是暂时的,当关闭mysql之后,这种设置就会失效
2)永久性的设置
在mysql的配置文件my.ini中,添加下面语句
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
重启mysql服务,这种问题就解决了!
有什么问题的小伙伴可以下方留言一起讨论啊!