在Mysql(版本为8.0.25)中进行分组查询的时候,频繁报出如下的错误:
1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘aaaa.answers.answer_content’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
解决步骤:
1.先查询一下sql_mode的值(确认一下MySQL是不是真的默认给设置了mysql sql_mode = only_full_group_by 属性)
先新建查询,再输入
SELECT @@sql_mode;
通常情况下得到的结果如下:
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
果真出现了ONLY_FULL_GROUP_BY
2.重新设置sql_mode的值,去掉其中的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';
(注意一下,8及以上版本的MySQL在执行这条语句的时候还需要把NO_AUTO_CREATE_USER也给去掉,8版本以上的输入↓)
set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
附加:
上面的设置本质上是改变了全局的sql_mode,对于新建的数据库有效,而对于之前已经创建好了的数据库还需要再执行一下下面的设置语句:
set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
(同样,8及以上版本的MySQL在执行这条语句的时候也需要把NO_AUTO_CREATE_USER给去掉)
set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTIO
原文链接:https://blog.csdn.net/qq_46063016/article/details/124908165