关于MySQL的ONLY_FULL_GROUP_BY

  • Z时代
  • 2024-01-10
  • 分类：综合

编程

今天同事问了一个奇怪的MySQL group by问题。研究了一会发现饶有趣味，记录之~
表结构简化如下：

create table user
(
	id int not null primary key,
	remark varchar(20) null,
	constraint user_with_icp_remark_uindex unique (remark)
);

对上表执行如下 SELECT 操作：

SELECT * FROM user GROUP BY remark;

会报错：SELECT list is not in GROUP BY clause and contains nonaggregated column "user.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 以后默认设置了 ONLY_FULL_GROUP_BY，即默认设置下只允许 SELECT 的列：1）在GROUP BY中出现过；或 2）是聚合值。id 不满足上面的条件，出现异常很合理。但是对上表执行如下 SELECT 操作：

SELECT * FROM user GROUP BY id;

却可以正常执行 ！！？？

第一反应当然是去看MySQL官方文档，其解释为：Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.

即除了上面的 1） 和 2）限制外还多了一条： 3） SELECT 的列在功能性上能够被 group by 的列唯一确定。更多信息可以看这里和这里（注意5.7.5之前的版本上可是没有 3 的）。

这可以解释 GROUP BY id 可行，因为id为主键，每个id都可以确定唯一的remark值（不会有一个id对应多个remark）。

但是却无法解释 GROUP BY remark为何会报错，因为remark建了唯一索引，所以remark也可以确定唯一的id（不会有一个remark对应多个id）。

开始怀疑是MySQL的bug，果然找到了对应的issue，编号为79291。就是说如果UK的列没有设置为非空就会出现这个bug，而不管实际是否有NULL值。

遂修改user表结构，

ALTER TABLE user MODIFY remark varchar(20) NOT NULL;

再执行 

SELECT * FROM user GROUP BY remark;

也OK了。

说点题外话，这个bug是2015年提的，直到2018年才在MySQL 8.0.11中才被修复。

时间间隔了三年多，还是那两个哥们在那一问一答，瞬间让我觉得真是岁月静好。。。

以上是 关于MySQL的ONLY_FULL_GROUP_BY 的全部内容， 来源链接： utcz.com/z/511009.html

回到顶部