mysql - 是否有一个查询只从一个表中选择列，如果仅按该表的主键分组，会给出不正确的结果？

Question

例如，一个 bookpub 数据库包含以下表(伪代码):

book (key: isbn)
bookauthor (key:author_id, isbn)
author (key: author_id)

以下查询返回每位作者的图书数量:

select lastname, firstname, count(isbn)
from author
join bookauthor using (author_id)
group by lastname, firstname;

但是，以下查询在 MySQL 中也产生了相同的结果，没有任何提示:

select lastname, firstname, count(isbn)
from author
join bookauthor using (author_id)
group by author_id;

那么，为什么不应该使用 author_id 而不是 lastname, firstname？

我可能要补充一点，正式的 SQL 规范包含以下内容:

All non-aggregate groups in a SELECT expression list or HAVING expression list must be included in the GROUP BY clause.

有人可以解释一下吗？什么是“非聚合组”？为什么不直接说“专栏”呢？此外，什么是“表达式列表”？在这种情况下，表达式的计算结果是否始终为一列？

Answer 1

没有任何 SQL 实现是 100% 符合 ANSI 定义的。有些东西丢失了，有些东西被添加了，有些东西只是不同了。

在 MySQL 的情况下，选择不执行您提到的限制:

• SELECT 表达式列表或 HAVING 表达式列表中的所有非聚合组都必须包含在 GROUP BY 子句中。

这允许使用您已经注意到的 GROUP BY primary_key 语法，而不是笨拙的 (实际上成本略高) GROUP BY property1, property2, property3等。它干净优雅。

然而，也有缺点；由于 MySQL，误用和误解在 Web 开发人员中很普遍，而且它的灵 active 允许 bug 悄悄溜走，但未被发现。我建议在大多数情况下避免使用它，因为性能提升微乎其微，而且出现错误的可能性可能很大。

漏过的错误示例可能是:

SELECT
    person.name,
    address.city
FROM
    person
INNER JOIN
    address
        ON  address.person_id = person.id
GROUP BY
    person.id

MySQL 几乎总是 允许该代码执行。即使地址表可以有每个人的多个条目(我住过不止一个地址)。

代码可能需要如下所示，但 MySQL 永远不会强制执行:

SELECT
    person.name,
    address.move_in_date,
    address.city
FROM
    person
INNER JOIN
    address
        ON  address.person_id = person.id
GROUP BY
    person.id,
    address.id

涉及的联接越多，GROUP BY 需要包含多个主键或其他字段的可能性就越大。

您得到的行为是，当代码不明确时，MySQL 会任意选择要返回的值。它显然是不确定的。下面的代码可以给出一个地址的城市和另一个地址的城市人口:-/

SELECT
    person.name,
    address.move_in_date,
    address.city,
    city.population
FROM
    person
INNER JOIN
    address
        ON  address.person_id = person.id
INNER JOIN
    city
        ON  address.city_id = city.id
GROUP BY
    person.id

然后人们会尝试使用如下“技巧”来滥用它...

SELECT
    person.name,
    address.move_in_date,
    address.city,
    city.population
FROM
    person
INNER JOIN
    address
        ON  address.person_id = person.id
INNER JOIN
    city
        ON  address.city_id = city.id
GROUP BY
    person.id
ORDER BY
    person.id,
    city.population DESC

这恰好导致 MySQL 引擎选择人口最多的城市。对于找到每个人居住过的人口最多的城市有用吗？好吧，它实际上并不能保证有效。它仍然是任意的；如果正在写入表，或者数据库处于分布式环境中，或者 MySQL 代码更改等，则行为可能会发生变化。

但人们还是会这样做。因为“好吧，到目前为止它一直对我有用!”...