MySQL 5.7.X 以上 sql_mode=only_full_group_by 问题

目录

• 问题
• 原因
• 解决方法
• 总结

问题

根据需求使用 Group By 对数据去重查询，提示以下错误

[SQL] select id,name_en,name_tc from student group by name_en,name_tc;
[Err] 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'school.student.ID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

原因

出现这个问题的主要原因是由于 MySQL 在 5.7.X 开始，默认开启了 only_full_group_by 模式。开启这个模式后，如果一个字段没有在 Select 语句和 Group By 子句中同时出现，或者是聚合函数的值的话，那么这条 SQL 查询会被 MySQL 认为非法的，会报错误，导致在老版本中能够执行的查询语句在新版本中无法执行。

解决方法

1. 使用 any_value(column)
   在 MySQL 中有一个函数：any_value(column) ，该函数允许非分组字段的出现，与关闭 only_full_group_by 模式有相同效果。

select any_value(id),name_en,name_tc from student group by name_en,name_tc;

2. 修改 sql_mode
   使用 SQL 语句修改 sql_mode，不过这种修改是一次性的，当 MySQL 服务重启后，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_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

3. 修改 MySQL 配置文件

• Windows 修改安装目录下的 my.ini 文件
• Mac 或者 Linux 修改 my.cnf 文件，一般在 /etc 目录下
  在 [mysqld] 下添加以下语句，然后重启 MySQL 服务，可以做到真正的一劳永逸。

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 8.0 以上的版本中，由于 NO_AUTO_CREATE_USER 已经删除，使用以下配置

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

总结

实际上，only_full_group_by 模式开启会比较好，原因有二：

1. 开启才符合 SQL 标准。
2. 在 MySQL 中有 any_value(column) 函数。

不过考虑到已有项目的 SQL 代码的修改成本较大，具体使用哪种方式因项目而异。