onlyfullgroupby函数：用于MySQL中的分组聚合数据

在MySQL数据库中，聚合函数（Aggregate Function）是一类返回单个结果的函数，它们从一组值中计算出一个结果。常见的聚合函数包括COUNT、SUM、AVG、MAX、MIN等。当需要对表中的数据进行聚合统计时，可以利用聚合函数来实现。onlyfullgroupby函数是 MySQL 5.7 新增的函数，为解决在使用GROUP BY分组聚合操作时省略掉的非聚合列带来的影响而设计的。

一、onlyfullgroupby函数介绍

在 MySQL 中，当我们使用 GROUP BY 对数据进行分组聚合时，查询语句中的 SELECT 语句中的非聚合列必须要出现在 GROUP BY 语句中。只有这样，MySQL 才能对每个分组进行聚合操作，否则就会出现错误。但是，在实际查询过程中，可能需要查询的列中还包含了非聚合列，而且这个非聚合列在 GROUP BY 子句中并不需要出现，那么这时候就需要用到 onlyfullgroupby 函数。

onlyfullgroupby 函数是 MySQL 5.7 新增的函数，用于解决在 GROUP BY 子句中只列出一部分非聚合列而省略了另一部分非聚合列所可能引起的问题。这个函数可以为遗漏的非聚合列提供隐式的 GROUP BY 处理。只要查询 SELECT 列表中所有非聚合列也都是函数依赖于 GROUP BY 子句中的列（即：所有被查询的非聚合列都能够被 GROUP BY 子句中的列唯一确定），那么 MySQL 会给这些列自动添加 GROUP BY 子句。

如下是一个示例查询语句：

SELECT a, b, MAX(c)
FROM tbl
GROUP BY a;

显然，上述查询语句可能出错，因为b列没有被包含在GROUP BY子句中，并且在SELECT列表中是非聚合列，这种问题在MySQL 5.7之前，开发者也是需要手动解决，例如，通过使用b字段替换掉MAX（c）：

SELECT a, b, MAX(c)
FROM tbl
GROUP BY a, b;

然而，如果使用MySQL 5.7及以上版本，并且使用了onlyfullgroupby函数，则可以避免手动处理的麻烦：

SELECT a, b, MAX(c)
FROM tbl
GROUP BY a, onlyfullgroupby(b);

这里使用了 onlyfullgroupby 函数来规避未包含在 GROUP BY 子句中的列 b，让GROUP BY 子句隐含包含了这个列。

二、onlyfullgroupby函数的使用场景

onlyfullgroupby 函数的使用场景包括以下两种情况：

1. 查询语句包含聚合函数和非聚合列

当在查询语句中包含聚合函数和非聚合列时，通常需要用 GROUP BY 语句对查询结果进行分组，只有这样才能保证返回结果的正确性。如果查询语句中未包括所有非聚合列，结果集将会出现错误。此时可以使用 onlyfullgroupby 函数，来规避未包含在 GROUP BY 子句中的非聚合列。

2. 查询语句使用了窗口函数

当在查询语句中使用窗口函数时，例如 ROW_NUMBER()，通常情况下不需要使用 GROUP BY 子句进行分组。但是，如果窗口函数需要统计非聚合列的信息，那么 GROUP BY 子句就需要用到了。此时可以使用 onlyfullgroupby 函数，来自动补充未包含在 GROUP BY 子句中的非聚合列。

三、onlyfullgroupby函数使用举例

下面通过几个实际的例子来说明 onlyfullgroupby 函数的使用。

1. 查询分组数据中的最新一条数据

假设我们需要查询一个论坛网站上用户最新发表的一条帖子：

SELECT
    u.id,
    u.name,
    t.title,
    MAX(t.post_time) last_post_time
FROM
    user u,
    topic t
WHERE
    u.id = t.author_id
GROUP BY
    u.id;

上面的查询语句中，我们通过 GROUP BY 子句对用户进行分组，然后通过 MAX 函数来找到每个用户最新的发表时间。

如果不使用 onlyfullgroupby 函数，MySQL 将无法判断查询语句中的 title 列属于哪个帖子，因此会报错。因此，我们需要使用 onlyfullgroupby 函数来告诉 MySQL，title 列也是需要分组的。

SELECT
    u.id,
    u.name,
    t.title,
    MAX(t.post_time) last_post_time
FROM
    user u,
    topic t
WHERE
    u.id = t.author_id
GROUP BY
    u.id,
onlyfullgroupby(t.title);

2. 查询商品售出数量排名前 N 的分类

假设我们有一个订单表 order 表和一个商品表 item 表，现在需要查询出售出数量最多的前 N 个分类：

SELECT
    item.category,
    COUNT(order.id) order_count
FROM
    order,
    item
WHERE
    order.item_id = item.id
GROUP BY
    item.category
ORDER BY
    order_count DESC
LIMIT
    10;

上面的代码会报错，因为查询语句中只包含了 item.category 列，而未包含 order_count 列，如果不加处理会得到以下错误提示：

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause
and contains nonaggregated column 'test.order.id' which is not
functionally dependent on columns in GROUP BY clause; this is
incompatible with sql_mode=only_full_group_by

我们可以使用 onlyfullgroupby 函数，让 MySQL 自动将 order_count 列加入到 GROUP BY 子句中：

SELECT
    item.category,
    COUNT(order.id) order_count
FROM
    order,
    item
WHERE
    order.item_id = item.id
GROUP BY
    item.category,
    onlyfullgroupby(order_count)
ORDER BY
    order_count DESC
LIMIT
    10;

四、总结

onlyfullgroupby 函数可以解决在使用 GROUP BY 分组聚合操作时省略掉的非聚合列带来的影响。只要查询 SELECT 列表中所有非聚合列也都是函数依赖于 GROUP BY 子句中的列，那么 MySQL 会给这些列自动添加 GROUP BY 子句。在实际使用过程中，需要注意 onlyfullgroupby 函数的使用场景，以及避免出现 GROUP BY 子句的错误。通过本文的讲解，相信读者对 onlyfullgroupby 函数的用法已经有了一定的了解。

原创文章，作者：小蓝，如若转载，请注明出处：https://www.506064.com/n/271384.html