MySQL 使用 GROUP BY 的 Error 1055

1. 问题原因

在 MySQL 8.0.17 版本执行以下 SQL 语句，报出错误 Error 1055

select Host,user,COUNT(*)
FROM user
GROUP BY Select_priv;

错误信息：

Error 1055 - Expression #1 of SELECT list is not in GROUP BY clause 
and contains nonaggregated column 'mysql.user.Host' which
is not functionally dependent on columns in GROUP BY
clause; this is incompatible with sql_mode=only_full_group_by

从错误信息看，主要与 sql_mode = only_full_group_by有关。这是 Mysql 5.7 以上版本的 group by 新特性，在5.7版本之后 only_full_group_by 模式是默认开启的

• ONLY_FUll_GROUP_BY 模式：
  对于 GROUP BY 聚合操作，如果在 GROUP BY 中的字段没有在 SELECT 中出现，那这个 SQL 语句就是不合法的，其实就是说查出来的字段必须被包含在聚合函数(count、avg等) 或者 GROUP BY 之后的字段集合里面

2. 解决方法

1. 首先在终端中连接进入 mysql，使用以下命令来查看全局的 sql_mode，可以看到如下截图

   SELECT @@GLOBAL.sql_mode;
   

   

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_ENGINE_SUBSTITUTION';
   

3. 重新查看 sql_mode 设置，确认修改成功

   SELECT @@GLOBAL.sql_mode;
   

   

4. mac 下重启mysql 服务

   mysql.server restart
   

3. 总结

其实严格来说，MySQL 之所以默认开启 ONLY_FUll_GROUP_BY模式也是为了减少错误。因为当 SELECT 查找的字段不在 GROUP BY 聚合字段中时，该字段查出来的数据不是确定的，当索引改变或者优化器选择不同的优化策略时可能会返回不同的结果。在该例子中，修改数据库 sql_mode 后，语句正常执行查询结果如下。从全表数据的截图可以看出，user 字段其实有4个不同值，但是使用 GROUP BY 的查询结果中只有前2个，这就意味着之后有条件变化该字段可能还会查到其他值，从而导致故障

select Host,Select_priv,user,COUNT(*)
FROM user
GROUP BY Select_priv,Host;