mysql having报错this is incompatible with sql_mode=only_full_group_by

今天遇到个报错

发现原来是MYSQL 8不支持在sql_mode包含only_full_group_by时(默认包含)

HAVING的条件里有 非聚合字段 以外的字段

文档：

• ONLY_FULL_GROUP_BY Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns. A MySQL extension to standard SQL permits references in the HAVING clause to aliased expressions in the select list. The HAVING clause can refer to aliases regardless of whether ONLY_FULL_GROUP_BY is enabled. For additional discussion and examples, see Section 12.20.3, “MySQL Handling of GROUP BY”.

解决办法：

打开mysql的配置文件，修改或添加sql_mode

[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

只要确保没有ONLY_FULL_GROUP_BY即可