MySQL-ONLY_FULL_GROUP_BY模式问题

Author Avatar
安 阳 3月 29, 2018

数据库刷在mysql5.5版本上没有问题,但刷在5.7上之后出了这个问题,导致页面请求出错。

错误信息:

Expression #1 of SELECT list is not in GROUP BY clause and contains 
nonaggregated column 'dept.label' which is not functionally 
dependent on columns in GROUP BY clause; this is incompatible with 
sql_mode=only_full_group_by

问题原因:

MySQL 5.7.5及以上功能依赖检测功能。默认情况下启用ONLY_FULL_GROUP_BY SQL模式,MySQL将拒绝选择列表,HAVING条件或ORDER BY列表的查询引用在GROUP BY子句中既未命名的非集合列,也不在功能上依赖于它们。(5.7.5之前,MySQL没有检测到功能依赖关系,默认情况下不启用ONLY_FULL_GROUP_BY)。

解决方案:
(关闭ONLY_FULL_GROUP_BY模式)

set @@global.sql_mode 
='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
FLUSH PRIVILEGES;
(以前是ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,可用select @@global.sql_mode查出来)

我在执行过程中报了错,说没有NO_AUTO_CREATE_USER,于是把这个也给去了,之后页面就正常显示了。

set @@global.sql_mode
='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

这里作为个人笔记,其他解决办法及介绍可移步:
Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated


2018.03.29 13:44

Licensed under CC BY-NC-SA 4.0
本文链接:http://JohnneyAnn.github.io/2018/03/29/MySQL-ONLY_FULL_GROUP_BY模式问题/