MySql查询报错this is incompatible with sql_mode=only_full_group_by

错误示例

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘yiliaohaocai_new.a.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

原因

SQL 标准中不允许 SELECT 列表，HAVING 条件语句，或 ORDER BY 语句中出现 GROUP BY 中未列表的可聚合列。而 MySQL 中有一个状态 ONLY_FULL_GROUP_BY 来标识是否遵从这一标准，默认为开启状态。

解决方法

1. 临时关闭 ONLY_FULL_GROUP_BY

可以选择关掉 MySQL 的 ONLY_FULL_GROUP_BY 模式。

设置 sql_mode 来关闭。

首先查看变更前的 sql_mode：

select @@global.sql_mode

SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY,',''));

再次查询 @@sql_mode 返回中应该已经没有该模式了。

注意：重启服务后失效

2. 配置文件关闭

Windows下打开my.ini文件 ，在mysqlld模块中的首行添加

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

linux下 vim /etc/mysql/conf.d/mysql.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

然后重启MySQL服务就可以了。

3. ANY_VALUE()

通过 ANY_VALUE() 来改造查询语句以避免报错。

MySQL提供了any_value(field)函数允许非分组字段的出现
any_value()会选择被分到同一组的数据里第一条数据的指定列值作为返回数据

使用 ANY_VALUE() 包裹的值不会被检查，跳过该错误。

看报错信息，它提示你那个表的字段有问题，你就把哪个字段使用ANY_VALUE()框起来就可以

如下

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘zhihuishequ.a.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

示例

SELECT
 ANY_VALUE(a.id),
 d.`name`
FROM
 `biz_department_library` a
 LEFT JOIN sys_organization d on a.organization_id = d.id 
GROUP BY
 d.id