Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column

### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'xx_bidding.p.gmt_create' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by ### The error may exist in class path resource [dao/project/ProjectVerify.xml] ### The error may involve defaultParameterMap ### The error occurred while setting parameters ### SQL: SELECT LEFT(p.gmt_create,7) as month ,p.custom_type,count(id) as count FROM `project` p where p.is_deleted = 0 and p.project_status =4 and p.is_deleted = 0 and UNIX_TIMESTAMP(p.gmt_create) >= ? and UNIX_TIMESTAMP(p.gmt_create ) <= ? GROUP BY LEFT(p.gmt_create,7),p.custom_type Order BY p.gmt_create Asc,p.custom_type asc ### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'xx_bidding.p.gmt_create' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by ; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'xx_bidding.p.gmt_create' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

这个错误的意思：选择的列没有包含在GROUP BY子句中，而且包含了非聚合列，它在功能上不依赖于GROUP BY子句的列;这与sql mode=only_full_group_by完全不兼容。

简单来说，就是查出来的字段必须是需要group by的这些字段中的一部分（隐约记得ORACLE是必须符合这个规则的）。当前数据库sql_mode确实包括了ONLY_FULL_GROUP_BY。开启了这个模式，那么group by和select的字段就会严格匹配。

[SQL]select id,real_name from sys_user GROUP BY real_name

[Err] 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column '.sys_user.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

表名大小区分

另外，mysql5.7默认也是开启了表明的大小写区分的，也可以通过修改配置文件来关闭，具体如下：

打开/etc/mysql/my.cnf配置文件，在mysqld节点下添加如下配置：

lower_case_table_names=1

1代表不区分大小写，0代表区分，改完后，重启mysql。

记录下mysql的一次语句运行错误操作，

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'XXX'

which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
解决方法:在mysql的my.ini配置文件中[mysqld]后面增加 一行

sql_mode=NO_ENGINE_SUBSTITUTION,STRI
————————————————
版权声明：本文为CSDN博主「detity」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_39771749/article/details/115382616