mysql - 为什么我在 Left Join 上得到 NULL 值？

Question

我正在将数据库迁移为新数据库，以将一对多关系更改为多对多关系(并改进列命名方案)。 [编辑:我创建了一个 SQLFiddle为此。]

oldDB                           newDB
=========================       =======================================
individuals                     people
 - individual_id                 - id
 - individual_name_first         - first_name
 - individual_name_last          - last_name
 - individual_name_other         - additional_identifier
 - individual_position           - role
 - individual_group_code         - (replaced with people-groups table)
(There are duplicate rows
 in this table for individ's
 who are in more than one
 group.)

groups                          groups
 - (no id in oldDB)              - id
 - group_code                    - short_name
 - group_name                    - full_name

                                people_groups
                                 - id
                                 - person_id
                                 - group_id
                                 - start_date
                                 - end_date

具体来说，我在创建 people 之间的链接表时遇到问题。和groups .

我已经创建了 people和groups表:

CREATE TABLE IF NOT EXISTS people (
  id int(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  first_name varchar(50) NOT NULL,
  last_name varchar(50) NOT NULL,
  additional_identifier varchar(50) DEFAULT NULL COMMENT 'In case of duplicate first and last names',
  role varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE newDB.people ADD UNIQUE `name` (last_name, first_name, additional_identifier);

INSERT INTO newDB.people
  (id, first_name, last_name, role)
SELECT
  individual_id, individual_name_last, individual_name_first, individual_position, COUNT(*)
FROM
  oldDB.individuals
GROUP BY
  individual_name_last, individual_name_first;

CREATE TABLE IF NOT EXISTS newDB.groups(
    id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL UNIQUE,
    short_name VARCHAR(255) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO newDB.groups
  (full_name, short_name)
SELECT
  group_name, group_code
FROM
  oldDB.groups;

接下来我会CREATE newDB.people-groups表，但首先我要确保我可以 SELECT正确的值(value)观:

SELECT
  newDB.groups.id 'group id',
  newDB.people.id 'person id',
  individual_group_code 'group short name',
  individual_name_last 'last name',
  individual_name_first 'first name'
FROM
  oldDB.individuals
LEFT JOIN
  newDB.groups ON(
    newDB.groups.short_name = oldDB.individuals.individual_group_code
   )
LEFT JOIN newDB.people ON (
  newDB.people.last_name = oldDB.individuals.individual_name_last 
    AND
  newDB.people.first_name = oldDB.individuals.individual_name_first
)
GROUP BY
  individual_name_last ASC,
  individual_name_first ASC,
  individual_group_code

第一个LEFT JOIN仅用于显示组短名称以方便验证。 第二个LEFT JOIN很重要:它应该允许 'person id'输出从 newDB.people.id 拉取。相反，我只是在该列中为所有输出行获取 NULL。 其他所有内容都显示正确。我错过了什么？

编辑:

期望的输出

这就是我希望得到的。 (我通过将 newDB.people.id 'person id' 替换为 oldDB.individuals.individual_id 'person id' 来生成它。为了举例说明问题，925 号和 1232 是两个不同组中的同一个人。新数据库将其简化为 925 号。)

实际输出

这是我得到的:

编辑2:

Here是一个确实可以工作的SQLFiddle。为什么它在我的 phpmyadmin 中不起作用？

Answer 1

您正在对选择列表中的 5 列非聚合列执行分组依据(3 列)。另外，这并不重要，列输出中没有聚合。

MySQL 将其视为distinct(对于 3 列)，并返回在 MRU 缓存中遇到的第一行，如果没有缓存，则返回在聚集索引或物理索引中遇到的第一行排序以满足 2 个非分组依据列。

换句话来说，这是一个用户错误。出了问题。我建议使用GROUP BY来清理你的意图。

有些相关，请阅读我最近的回答 Here与 ONLY_FULL_GROUP_BY 相关。请参阅该链接的底部是 MySQL Handling of GROUP BY在我看来，这是对 MySQL 允许的实际问题和非标准的掩盖，这使得违反该标准的数据变得意外且难以解释。

那么 MySQL 开发团队做了什么？他们默认实现了该标准(从版本 5.7 开始)，以禁止您刚刚执行的查询类型。

编辑1

您的查询，没有 GROUP BY，但有 order by newGroups.id,people.id，在版本 5.7.14 服务器上: