MySQL报ONLY_FULL_GROUP_BY错误的原因以及解决办法

在MySQL执行select查询时，可能会出现

which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

错误，其中关键字为only_full_group_by。

only_full_group_by是个什么东西？

1、ONLY_FULL_GROUP_BY 是 MySQL 中的一个 SQL 模式，用于控制 GROUP BY 语句的行为。当启用 ONLY_FULL_GROUP_BY 模式时，MySQL 将执行更严格的检查，要求在 GROUP BY 语句中的每个非聚合列都必须出现在 SELECT 查询结果中或使用聚合函数进行处理。

2、默认情况下，MySQL 允许在 GROUP BY 语句中省略非聚合列，并返回结果集中的任意值（通常是第一行的值）。这种行为在其他数据库系统中可能不同，因此为了提高代码的可移植性和准确性，可以启用 ONLY_FULL_GROUP_BY 模式。

举个例子，现有表及表数据如下：

id  |name|phone      |
----+----+-----------+
1000|小明  |13800000000|
1001|小明  |13800000001|
1002|小王  |13800000002|
1003|小赵  |13800000003|
1004|小钱  |13800000004|
1005|小李  |13800000005|

执行如下SQL：

select
    name,
    phone
from
    t_user
group by
    name

由于数据中存在2条小明，且select中会显示小明对应的电话phone，这个时候就存在一个问题，显示13800000000还是13800000001呢？我们直接看结果：

在开启ONLY_FULL_GROUP_BY模式下执行：

SQL 错误 [1055] [42000]: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.t_user.phone' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

在关闭ONLY_FULL_GROUP_BY模式下执行：

name|phone      |
----+-----------+
小明  |13800000000|
小李  |13800000005|
小王  |13800000002|
小赵  |13800000003|
小钱  |13800000004|

可以看出，在关闭ONLY_FULL_GROUP_BY模式下，默认显示第一条数据，也就是显示13800000000。

如何关闭ONLY_FULL_GROUP_BY模式

select @@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,删除，并通过SET进行设置：

SET @@sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

以上操作只会影响会话级别的配置，如果想要修改全局配置，则需要对应使用

@@global.sql_mode;

数据库如果重启之后，会发现还是会恢复原样，如果想要数据库重启之后也关闭该模式，则需要对应修改配置文件（百度修改方法）