mysql5.7报错: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated... (2)

大概意思就是说,SQL语句中包含了GROUP BY聚合操作的情况下,如果在SELECT中的列没有出现在GROUP BY中,那么这个SQL就是不合法的,因为列不在GROUP BY从句中。

原来是因为mysql中配置的sql_model包含了ONLY_FULL_GROUP_BY,所以对于SQL语句的检查比较严格。

查询sql_model配置: select @@sql_mode;

mysql5.7报错: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated...

在通过分析日志,查看请求中查询数据库的sql语句: ### SQL: SELECT doctor_code AS doctorCode, doctor_name AS doctorName, count(doctor_code) AS illegalTimes FROM file_illegal_record WHERE (doctor_code IS NOT NULL AND doctor_code != '' AND outpatient_date BETWEEN ? AND ?) GROUP BY doctor_code ORDER BY illegalTimes desc LIMIT ?, ?

的确是SELECT中的列没有出现在GROUP BY中。

解决方案:

去掉ONLY_FULL_GROUP_BY的默认配置

编辑mysql的配置文件(Windows系统在mysql安装目录下的my.ini进行编辑,Linux系统在/etc目录下的my.cnf中进行编辑)

在[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

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/wpjxff.html