总结ONLY_FULL_GROUP_BY的介绍和引起的错误

总结ONLY_FULL_GROUP_BY的介绍和引起的错误

起因

查询sql的配置

select @@sql_mode;

• 如果设置了ONLY_FULL_GROUP_BY会引起的错误

Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

• 而且,mysql5.7.5版本开始，sql_mode使用的是默认值( 如下),也是设置了ONLY_FULL_GROUP_BY

---

介绍

• ONLY_FULL_GROUP_BY：对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中
  (其实也是跟oracle差不多的)

举例子

• 正常情况下没有配置

select count(id) from table_demo order by rank -- ok
select count(id) from table_demo group by rank -- ok
select count(rank),id from table_demo group by rank -- ok
select count(rank),id from table_demo group by id -- ok

• 如果配置了ONLY_FULL_GROUP_BY

select count(id) from table_demo order by rank -- error
select count(id) from table_demo group by rank -- ok
select count(rank),id from table_demo group by rank -- error
select count(rank),id from table_demo group by id -- ok

• 会引起报错

错误代码： 1140
Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

解决

1. 重新设置

SET sql_mode = ''

1. 在使用聚合函数后加上group by

总结

• 如果配置了ONLY_FULL_GROUP_BY
  
  1. 后面不能加order by
  2. 如果使用了聚合函数，查询的字段还包括其他的字段，就需要使用group by,就跟oracle相似