mysql 的 sql_mode.only_full_group_by属性解析

mysql8.0官网：处理 group by

1. 初始条件

现在有这样一张表，其中有两条 name = 裤子 的数据

2. 现象

执行以下sql，想要得到按name分组的数据：GROUP BY name

SELECT name,author,SUM(price) FROM  `t_book`
GROUP BY name

在执行 sql 时 出现了 1055 异常

SELECT name,author,SUM(price) FROM  `t_book`
GROUP BY name
> 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.t_book.author' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
> 时间: 0.03s

按照错误提示，执行以下语句：GROUP BY name ,author，查询才不会报错

SELECT name,author,SUM(price) FROM  `t_book`
GROUP BY name ,author

查询结果：

         但是，这种查询已经脱离了我们原来的逻辑，我们是想要GROUP BY name，结果应只出现两条数据。而不是GROUP BY name ,author后的三条结果，那应该怎么做呢？

 

3. 解决

 

①：关闭sql_mode 的 only_full_group_by模式

         only_full_group_by 要求 group by 后边必须写满 select 后边的非函数列，所以我们只需要关闭only_full_group_by即可，关闭方式又分为 临时关闭 和 永久关闭

• 临时关闭：临时关闭又分为Session级 和 GLOBAL级
  • Session：仅关闭当前会话设置，mysql重启后失效
  • GLOBAL：关闭全局设置，mysql重启后失效
• 永久关闭：永久关闭only_full_group_by模式，这种方法需要在mysql的配置文件里修改，然后重启。
  1. 找到配置文件/etc/my.cnf(或则关联文件夹找到mysql-server.cnf)
  2. 在上述文件内的[mysqld]后追加sql_mode=‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION’
  3. 保存配置文件后，重启Mysql即可。

接下来演示一下关闭与打开Session级的sql_mode.only_full_group_by属性带来的影响！

查看 sql_mode属性

• 查看Session级：SELECT @@sql_mode;
• 查看 GLOBAL级：select @@GLOBAL.sql_mode;

查看Session级的 sql_mode属性，结果是可以看到带有ONLY_FULL_GROUP_BY属性的，所以我们GROUP BY name会报 1055 异常

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

接下来关闭Session级的 sql_mode属性

// 关闭 session级
SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

// 关闭 GLOBAL级
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

再次select @@sql_mode;查询发现 sql_mode的值已经删除了ONLY_FULL_GROUP_BY属性

STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

然后再次执行如下sql

SELECT name,author,SUM(price) FROM  `t_book`
GROUP BY name 

执行结果：是按照我们的想法，返回了两条记录

注意：查询结果中，select后边的author属性由于没有使用聚合函数，所以只会返回原数据表中的第一条数据，使用时请注意！原数据如下

         

②：使用 ANY_VALUE() 抑制 ONLY_FULL_GROUP_BY 的影响

         如果不想关闭mysql的ONLY_FULL_GROUP_BY全局设置，仅仅想让当前sql忽略其影响，则可以使用ANY_VALUE(cloum) 忽略ONLY_FULL_GROUP_BY的影响，详见官方文档！

mysql8.0官网：ANY_VALUE(cloum) 可以抑制 ONLY_FULL_GROUP_BY 的影响

上面已经把ONLY_FULL_GROUP_BY属性关闭了，接下来打开它：

 // 打开`ONLY_FULL_GROUP_BY`属性
 SET SESSION sql_mode = sys.list_add(@@session.sql_mode, 'ONLY_FULL_GROUP_BY');

然后再去执行一下上面的sql，就会报1055异常了

SELECT name,author,SUM(price) FROM  `t_book`
GROUP BY name 

使用 ANY_VALUE（）忽略ONLY_FULL_GROUP_BY的影响：

SELECT name,ANY_VALUE(author),SUM(price) FROM  `t_book`
GROUP BY name

结果如下：

正常出结果！