MySQL中SELECT非聚合非GROUP BY列未报错的原因解析

问题背景 #

给定LeetCode的SQL场景：

• Users表：id为主键，存储用户ID和name
• Rides表：id为主键，存储骑行记录ID、user_id关联用户、distance骑行距离

需求是统计每个用户的总骑行距离，无骑行记录的用户距离记为0，结果按travelled_distance降序、name升序排序。用户写出的SQL如下：

select
    u.name,
    coalesce(sum(r.distance), 0) as travelled_distance
from users u
left join rides r on u.id = r.user_id
group by u.id
order by travelled_distance desc, u.name asc; 

用户的疑问：按SQL标准，SELECT中的列要么是聚合函数，要么必须出现在GROUP BY子句中，但这段SQL里u.name既不是聚合函数也不在GROUP BY里，却在部分MySQL版本中执行成功，想明确原因（注：用GROUP BY u.id是因为Users表可能存在重复用户名）。

原因分析 #

核心触发点：MySQL的ONLY_FULL_GROUP_BY SQL模式 #

MySQL有一个名为ONLY_FULL_GROUP_BY的SQL模式，它直接决定了MySQL是否严格遵循SQL标准的GROUP BY规则：

• 当关闭该模式时，MySQL允许SELECT子句中出现既不在GROUP BY里、也不是聚合函数的列，此时MySQL会从每个分组中任意选取该列的一个值返回。
• 当开启该模式时，MySQL会严格执行SQL标准，这种写法会直接报错，提示u.name不在GROUP BY子句中。

为什么这段SQL的结果是正确的？ #

因为Users.id是主键，主键具有唯一性，每个id对应的name是唯一确定的——即使存在重复用户名，每个用户的id不同，分组后每个分组里只会有一个name值。所以即使MySQL从分组中“任意选取”name，实际取到的就是该用户唯一的name，结果不会出错。

兼容性建议 #

虽然这段SQL在部分MySQL环境下能运行，但不符合SQL标准，在其他数据库（比如PostgreSQL、SQL Server）或者开启了ONLY_FULL_GROUP_BY的MySQL环境中会报错。为了保证代码的兼容性，推荐两种修改方式：

1. 将u.name加入GROUP BY子句：

select
    u.name,
    coalesce(sum(r.distance), 0) as travelled_distance
from users u
left join rides r on u.id = r.user_id
group by u.id, u.name
order by travelled_distance desc, u.name asc; 

2. 用聚合函数包裹u.name（因为每个分组的name唯一，聚合函数结果和原值一致）：

select
    max(u.name) as name,
    coalesce(sum(r.distance), 0) as travelled_distance
from users u
left join rides r on u.id = r.user_id
group by u.id
order by travelled_distance desc, name asc; 

内容的提问来源于stack exchange，提问作者ylyu1