mysql查询报错sql_mode=only_full_group_by的解决方法以和案例

记录mysql报错sql_mode=only_full_group_by

1、报错信息

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

 
在MySQL 5.7后，MySQL默认开启了SQL_MODE严格模式，对数据进行严格校验。如果代码中含有group by聚合操作，那么select中的列，要么是来自max()、min()、sum()、avg()等聚合函数的结果，要么其他字段都出现在group by中
 

2、关键报错信息

1、SELECT list is not in GROUP BY clause and contains nonaggregated（SELECT列表不在groupby子句中，并且包含非聚合列表）

2、sql_mode=only_full_group_by（严格模式）
 

3、案例

如果执行以下sql，就出现上面所描述的错误信息

select name,age from student group by name

怎么解决？可以参照以下方法

方法1：将所有select的字段都加入到group by

select name,age from student group by name,age

方法2：所有未进行group by的字段都聚合函数

select name,max(age) as age from student group by name

方法3：去除未进行group by的字段

select name from student group by name

 
以上方式存在的弊端

1、调整后的sql需要保证调整前后sql执行结果的不变性，对于一些复杂的sql语句，调整起来会比较麻烦

2、为字段新增聚合函数会影sql执行效率

3、如果需要调整的sql语句较多，会比较耗时

4、调整后的sql有可能无法获取到想要的所有信息
 

4、解决

方法一：直接修改msql配置（重启失效）

打开数据库执行以下sql

SELECT @@GLOBAL.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，则将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';

注：使用以上方式，数据库重启后配置会还原，需要再次进行配置
 
方法二：修改msql配置文件（永久有效）

在找mysql的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';

注：sql_mode = sql语句：SELECT @@GLOBAL.sql_mode的查询结果 去除 ONLY_FULL_GROUP_BY后的字符串
 
方法三：使用any_value()函数

any_value()会选择被分到同一组的数据里第一条数据的指定列值作为返回数据

any_value()函数可以抑制ONLY_FULL_GROUP_BY值被拒绝

案例

select name,any_value(age) as age from student group by name

这篇好文章是转载于：学新通技术网