第三人称__- 的个人博客

目录

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

问题

根据需求使用 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代码的修改成本较大，具体使用哪种方式因项目而异。