今天遇到个报错
发现原来是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
即可