今天在使用 MySQL 的 GROUP BY 的时候报了个错，心想这个 SQL 怎么看都不应该报错才对啊。

以下内容只适用于 MySQL 5.7 及以上的版本

示例

为了方便举例子，这里先建两张简单的表

用户表：user(id, name, password)

房产表：house(id, username, address)

其中用户表用来存储用户信息，房产表存储用户的房产信息，那么显示每个人的信息以及拥有的房产信息：

1
2
3
4
5
SELECT DISTINCT u.*, h.*
FROM
	`user` AS u
	LEFT JOIN  house AS h ON u.`name` = h.username
	GROUP BY u.`name`

1055 - Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘learning.h.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

就是这样简单的SQL语句却报了错，经上网查询，得知：

MySQL 5.7,实现检测功能的依赖。如果启用了ONLY_FULL_GROUP_BY SQL模式(默认),MySQL拒绝选择列表查询,条件,或命令列表是指非聚合列既不是GROUP by子句中指定功能也不依赖他们。

高版本mysql（客户服务器版本是5.7.18）默认的sql_mode包含ONLY_FULL_GROUP_BY，这个属性保证了select到的列都在group by中出现。

但这是否意味着 MySQL5.7之后无法使用 GROUP BY？

显然是否定的，比如下面的SQL语句：

1
SELECT `name` FROM `user` GROUP BY `name`

查询的时候只能够查询 GROUP BY 的那列，但是显然这句SQL语句并没有什么实际的意义。但是下面这个为 name 这个列加上唯一索引就不一样了：

1
SELECT * FROM `user` GROUP BY `name`

确实不一样了，因为可以查询这个表的所有数据，但是似乎意义也并不大，因为 name 这个列有唯一索引，每一行都是一个组。

但是下面的SQL可能会稍微有点意义：

1
2
3
4
5
SELECT DISTINCT u.*, COUNT(*)
FROM
	`user` AS u
	INNER JOIN  house AS h ON u.`name` = h.username
GROUP BY u.`name`

该语句能够拥有一套或多套房产的用户的信息以及他们的房产数量。

但是我们能否同时分组并查询用户以及他们的房产信息呢？答案是否定的。

1
2
3
4
5
SELECT DISTINCT u.*, h.*
FROM
	`user` AS u
	INNER JOIN  house AS h ON u.`name` = h.username
	GROUP BY u.`name`

该SQL语句依旧报了与前面第一条SQL语句同样的错误。因此查询的内容应该只限于 GROUP BY 的列；或者当 GROUP BY 的列有唯一索引时，查询与该列在同一个表的列。

解决方案

通过上面的示例，可以得出一下结论：

  • MySQL 5.7 开始对 GROUP BY 语句进行了限制，不能随便 GROUP BY
  • 当 GROUP BY 的列有唯一索引时，能够查询与该列所在的同一表的所有列
  • 当 GROUP BY 的列没有唯一索引时，只能够查询该列的信息（好像并没有什么意义）
  • 查询字段中有聚合函数的字段不算做限制列，例如上面 COUNT()、SUM()、MAX()

MySQL这样的约束会导致很多我们看起来很正常的语句无法执行，可以通过下面的命令来暂时修改 sql_mode：

1
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

也可以在配置文件中手动配置 sql_mode

1
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

思考

为什么MySQL5.7 要开始限制 GROUP BY 的使用？

并非是 MySQL 要限制 GROUP BY 的使用，只是从 5.7 版本开始，MySQL 的 GROUP BY 才开始遵循 SQL/2008标准：

  • GROUP BY 子句中指定的每一个 group-by-term 都必须是列引用：也就是对在查询的 FROM 子句中引用的表中的列的引用。这些表达式称为分组列。
  • SELECT 列表、HAVING 子句或 ORDER BY 子句中的表达式如果不是集合函数，则必须是分组列，或者只能引用分组列。但是，如果支持可选的 SQL/2008 语言功能 T301 “功能依赖性”，则此类引用可以引用查询的 FROM 子句中由分组列的函数来确定的列。