groupby报错not dependent on columns in GROUP BY clause； this is incompatible with sql_mode=only_full

问题描述

MySQL执行如下group by 语句报错：1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘table_zjq.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

select * from table_zjq group by column_name;

解决方法

上述报错大致是group开启了严格约束，不符合sql_mode=only_full_group_by规范，only_full_group_by约束在分组时，只能对该分组的字段进行查询。（注：MySQL5.7之后的版本才会有这个问题，MySQL5.7后默认的sql_mode=only_full_group_by）
可以解决该报错的方法如下：

重新设置sql_mode

查询当前数据库的sql_mode设置：