1. 什么是Group by语句?
在开始讨论Group by的问题前,我们先来看一下什么是Group by语句。在Mysql中,Group by语句用于对查询结果进行分组,相同的结果会被归为一组,然后对每组的结果进行聚合操作,如统计每组的数量、求和等。通常,Group by语句会和聚合函数如count、sum一起使用。下面是一个示例代码:
SELECT category, COUNT(*)
FROM products
GROUP BY category;
以上代码将对products表的结果根据category列进行分组,然后在每组中统计该组的数量。这样,我们就可以很容易地了解每个category中有多少个产品了。
2. Group by语句错误使用的情况
2.1 未聚合的列也出现在select语句中
在使用Group by语句时,我们必须明确列出每个出现在select语句中的列,这些列要么是聚合函数,要么是被分组的列。如果一个列既没有被聚合也没有被分组,那么就会出现问题。下面是一个错误示例:
SELECT category, name, COUNT(*)
FROM products
GROUP BY category;
以上代码中,name列既没有被聚合也没有被分组,这样就会导致结果不可预测。事实上,在不同的Mysql版本中,这种错误使用Group by语句的情况返回的结果都可能不同。在最新的Mysql版本中,这样的代码将会无法执行,并会提示错误信息:Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'products.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by。这大大避免了不正确使用Group by语句的问题。
2.2 SELECT子句中使用聚合函数而不使用GROUP BY
在使用Group by语句时,我们通常会使用聚合函数如count、sum等来对每组的结果进行计算。如果我们忘记在Group by语句中列出所有出现在select语句中的列,就会导致问题。以下是一个错误示例:
SELECT category, SUM(price)
FROM products;
以上代码中,我们使用了sum函数对price列进行计算,但是没有在Group by语句中列出category列,这样就会导致结果不可预测。在Mysql中,如果SELECT子句中出现了聚合函数,却没有使用Group by语句,就会将整个查询结果作为一组进行计算。在这个例子中,查询结果只会有一组,因为没有使用Group by语句,所以会将所有的price值相加,而category列则会被忽略。
2.3 SELECT子句中出现多个聚合函数
在使用Group by语句时,我们通常只会使用一个聚合函数,如果不小心在一个查询中使用了多个聚合函数,就会出现错误。以下是一个错误示例:
SELECT category, SUM(price), COUNT(*)
FROM products
GROUP BY category;
以上代码中,我们同时使用了sum函数和count函数进行计算,但是这两个函数的含义不同,使用GROUP BY时,只能对一列进行分组聚合,不能同时对多列进行聚合操作,否则会导致错误结果或者执行失败。
3. 兼容性问题
在旧版本的Mysql中,Group by语句允许未聚合的列也出现在select语句中。这样做可能会导致意想不到的结果,因为它并不强制执行聚合。在此之后的Mysql版本中,默认情况下会将ONLY_FULL_GROUP_BY模式设置为开启状态,这样就会强制要求所有未聚合的列也必须出现在Group by语句中。这是一种更安全的方法,可以避免不正确的结果。虽然在Mysql中开启ONLY_FULL_GROUP_BY会提高代码的安全性,但是在某些情况下,会导致代码无法正常执行。如果你的代码已经在之前的版本中使用了错误的Group by语句,那么将其迁移到新版Mysql时,可能会因为ONLY_FULL_GROUP_BY模式而失败。为了避免这种情况,可以使用sql_mode参数,将其设置为'no_engine_substitution,traditional',这样可以关闭ONLY_FULL_GROUP_BY模式,从而允许之前错误的Group by语句。
4. 总结
正确使用Group by语句可以帮助我们更好地理解数据,对于我们的业务提供更有用的信息。不正确使用Group by语句则可能会导致结果不可预测,因此我们必须保证每个出现在select语句中的列要么是被聚合的,要么是被列出的分组的列。Mysql的迭代版本中,ONLY_FULL_GROUP_BY模式已经成为默认设置,它可以强制要求所有出现在select语句中的列均出现在Group by语句中,这样就可以保证查询结果的可预测性。