MySQL执行报错: [Err] 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'cerebrum_sit.s.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
项目场景:
MySQL 5.7.5执行group by column_name 报错,执行sql报错信息如上,具体sql如下:
SELECT
s.id,
s. CODE,
s. NAME,
s.start_date startDate,
s.end_date endDate
FROM
store s
WHERE
CODE IN (
'FTSNCWLJ1561',
'FTSSCWLJ0316',
'FTSNCWLJ2250',
'FTSECWLJ1013',
'FTSECWLJ1235',
'FTSECWLJ2253',
'FTSECWLJ2362',
'FTSNCWLJ1873',
'FTSECWLJ0154',
'FTSECWLJ0981',
'FTSSCWLJ1225',
'FTSNCWLJ1561',
'FTSNCWLJ1561',
'FTSNCWLJ1547',
'FTSCCWLJ0264',
'FTSECWLJ1289',
'FTSECWLJ1003',
'FTSNCWLJ1996'
)
GROUP BY CODE
这段sql看着确实没有问题,包括很多人在大学学习的都是这种写法,但是运行却会报错,如下图:
开始我也百思不得其解,经过各种资料查阅,才发现该报错主要是MySQL的版本,这个问题主要发生在mysql5.7.5及以上,因为MySQL5.7.5的sql模式配置是“only_full_group_by”,这个我们在报错信息中也可以看出来,这个配置启用时“严格ANSI sql规则”,该规则要求在group by的时候,没有聚合的列,在group by 的时候,必须全部包含在group by的字段中。没有聚合的列,指的是没有使用 max, min, count, sum....这些函数的列,直接查询出字段的列。
原因分析:
提示:这里,我们首先了解下group by 的用法,具体如下:
GROUP BY子句重要规则:
- 包含任意数目的列,
- 如果在GROUP BY 子句中套入分组,数据将会最后规定的分组上进行总汇。
- GROUP BY 子句中列出的没列都必须是检索的列,有效的表达式,不能聚集函数。
- 大多数SQL不允许GROUP BY 带有长度可变的数据类型(文本,备注型字段)
- 除聚集计算语句外,SELECT 语句中,每个列都必须在GROUP BY子句中给出。
- 如果分组带有NULL值,将作为一个分组返回,如果多个将成一组。
- GROUP BY 子句必须出现在WHERE子句之后,
看到上面标红的规则,再结合我们的sql,我们就能很轻易的找出问题的所在,原因是我们group by 后只有code字段,但是我们select的却是多个字段,问题一目了然。
解决方案:
找到了问题的原因,那我们应该怎么办呢,不可能只查一个code字段,与开发的需求不符,但是group by 所以查询字段,又无法达到通过code去重的需求,这时我们可以使用max()函数进行处理,具体写法如下:
SELECT
max(id) id,
code,
max(name) name,
max(start_date) startDate,
max(end_date) endDate
FROM
store s
WHERE
CODE IN (
'FTSNCWLJ1561',
'FTSSCWLJ0316',
'FTSNCWLJ2250',
'FTSECWLJ1013',
'FTSECWLJ1235',
'FTSECWLJ2253',
'FTSECWLJ2362',
'FTSNCWLJ1873',
'FTSECWLJ0154',
'FTSECWLJ0981',
'FTSSCWLJ1225',
'FTSNCWLJ1561',
'FTSNCWLJ1561',
'FTSNCWLJ1547',
'FTSCCWLJ0264',
'FTSECWLJ1289',
'FTSECWLJ1003',
'FTSNCWLJ1996'
)
GROUP BY CODE
该sql可正确的查出我们想要的结果,如下图:
由于mysql版本的提升,很多使用group by的场景,我们都可以使用类似的写法,但是使用这种方法虽然能实现功能需求,但是查询速度却比较慢