mysql - 标准化具有冗余主键的MySQL表

我正在升级一个MySQL数据库的架构，该数据库存储一个Persons及其相关的Charities列表。问题Persons中的表包含个人数据和关联的慈善机构。它具有以下字段：

Person_Id（自动递增主键）
Citizen_Id（唯一的字母数字国民ID）
Person_Full_Name（不言自明）
Person_Email（不言自明）
Person_Assistant_Contact（此人的私人助理的手机）
Charity_Org_Id（慈善组织主表的外键）
Designation_Id（此人可以在慈善组织中持有的名称主表的外键）

如果需要，这是上述内容的DDL：

CREATE TABLE `Persons` (
    `Person_Id` SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `Citizen_Id` CHAR(10) NOT NULL UNIQUE COMMENT 'a unique alphanumeric national id',
    `Person_Full_Name` CHAR(100) NOT NULL,
    `Person_Email` CHAR(50) NULL,
    `Person_Assistant_Contact` CHAR(20) NULL COMMENT 'the cellphone of the persons personal assistant',
    `Charity_Org_Id` SMALLINT UNSIGNED NOT NULL COMMENT 'foreign key for a master table of charitable organizations',
    `Designation_Id` SMALLINT UNSIGNED NULL COMMENT 'foreign key for a master table of designations that the person can hold in the charitable organization',
    FOREIGN KEY (`Charity_Org_Id`) REFERENCES `Charity_Orgs` (`Charity_Org_Id`),
    FOREIGN KEY (`Designation_Id`) REFERENCES `Designations` (`Designation_Id`),
) ENGINE = INNODB;

由于有许多人属于多个慈善组织，因此表中有许多条目，其中名称和联系信息相同，但Charity_Org_Id和Designation_Id不同。 INSERT查询示例（请注意第一列和最后两列）：

INSERT INTO `Persons` VALUES
(2387,'OZN13445','Frederick Oznawa','[email protected]','+54-332887789',128,12),
(4533,'OZN13445','Frederick Oznawa','[email protected]','+54-332887789',520,2),
(4555,'OZN13445','Frederick Oznawa','[email protected]','+54-332887789',522,4);

显而易见的步骤是将联系人信息和关联的慈善机构分离到单独的表中。这就是我想要的：

INSERT INTO `Persons` VALUES
(2387,'OZN13445','Frederick Oznawa','[email protected]','+54-332887789');

INSERT INTO `Person_Charities` VALUES
(2387,128,12),
(2387,520,2),
(2387,522,4);

获取新的Persons表很简单。我想要一个纯SQL解决方案来获取“ Person_Charities”表（我想使用每人最低的Person_Id并丢弃其余的ID）。我可以使用电子表格软件手动执行此操作，但这容易出错，并且我正在查看数千行。

注意：我不想使用Citizen_Id作为备用主键

最佳答案

您可以使用以下查询查询要分隔的列的不同列表：

SELECT
    MIN(`Person_Id`) AS Person_Id
    `Citizen_Id`,
    `Person_Full_Name`,
    `Person_Email`,
    `Person_Assistant_Contact`
FROM
    Persons
GROUP BY
    `Citizen_Id`,
    `Person_Full_Name`,
    `Person_Email`,
    `Person_Assistant_Contact`assistant'

好的，我们有一个查询来从您的表中获取不同的人员列表。

创建新表

您可以使用CREATE TABLE ... SELECT语法创建表，也可以创建新表来存储数据并根据上述查询使用INSERT ... SELECT语法插入不同的人。我个人更喜欢手动构建一个新表。

CREATE TABLE `New_Persons` (
  `Person_Id` SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `Citizen_Id` CHAR(10) NOT NULL UNIQUE COMMENT 'a unique alphanumeric national id',
  `Person_Full_Name` CHAR(100) NOT NULL,
  `Person_Email` CHAR(50) NULL,
  `Person_Assistant_Contact` CHAR(20) NULL COMMENT 'the cellphone of the persons personal assistant'
);

将数据插入我们全新的表格中

INSERT INTO New_Persons (
  Person_Id,
  Citizen_Id,
  Person_Full_Name,
  Person_Email,
  Person_Assistant_Contact
)
SELECT
    MIN(Person_Id) AS Person_Id,
    Citizen_Id,
    Person_Full_Name,
    Person_Email,
    Person_Assistant_Contact
FROM
    Persons
GROUP BY
    Citizen_Id,
    Person_Full_Name,
    Person_Email,
    Person_Assistant_Contact;

好的，我们有一张桌子，上面有不同的人。您可以根据需要添加其他约束和索引。

如何继续？

我们在原始New_Persons表中有一个包含不同人员的表（即：Persons），并且有一个表（具有（几乎）相似的人）。下一步是减少旧的Persons表以仅存储所需的信息。

选项包括：

添加新列以存储New_Persons表中的ID，然后使用Multiple-table update查询更新该列，然后删除不必要的列。
创建一个新表来存储计算出的信息，然后删除旧表并重命名新表。

如果您有足够的存储空间，建议使用第二个选项。您可以验证插入的数据是否正确，并且不需要完全重新组织旧表。最重要的是，丢失任何数据的机会要少得多（由于您不触摸现有表，因此在出现问题时可以重新启动该过程）。

创建新表

CREATE TABLE `Person_Charities` (
  `Person_Id` SMALLINT UNSIGNED,
  `Charity_Org_Id` SMALLINT UNSIGNED NOT NULL COMMENT 'foreign key for a master table of charitable organizations',
  `Designation_Id` SMALLINT UNSIGNED NULL COMMENT 'foreign key for a master table of designations that the person can hold in the charitable organization',
  PRIMARY KEY PK_PersonCharities (Person_Id, Charity_Org_Id)
);

再一次，您可以调整键，索引和您想要的任何内容。

将数据插入我们的新表

因此，我们有一个空的新表，以及两个有一些数据的旧表，我们知道它们之间的关系。现在，我们的目标是创建一个查询，以从旧的Persons表中检索新的Person_id和剩余的列。

这听起来像使用Person_Id以外的公共列在给定表上进行简单联接

INSERT INTO Person_Charities
(
  Person_Id,
  Charity_Org_Id,
  Designation_Id
)
SELECT
  NP.Person_Id,
  P.Charity_Org_Id,
  P.Designation_Id
FROM
  Persons P
  INNER JOIN New_Persons NP
    ON P.Citizen_Id = NP.Citizen_Id
      AND P.Person_Full_Name = NP.Person_Full_Name
      AND P.Person_Email = NP.Person_Email
      AND P.Person_Assistant_Contact = NP.Person_Assistant_Contact;

快完成了

好的，我们有了带有所需数据的新表，只是它们的名称令人困惑：)

重命名表，我们完成了：

RENAME TABLE
  Persons TO Old_Persons,
  New_Persons TO Persons

旁注

在原始shema中，Citizen_Id是唯一的，但是您要插入重复项。模式或插入的数据错误。
在删除旧表之前，您应该检查新表中的数据并确认没有任何问题。（并且请记住，旧表的名称现在是Old_Persons！）
在开始修改架构之前，请始终备份数据库并测试备份文件（您认识一个叫Murphy的人）
如果New_Persons表中的任何列可为空，则应修改最后一个联接。您必须处理NULL“值”
如果需要更多规范化，请考虑创建一个新数据库并将您的数据从旧数据库迁移到新数据库。

个人笔记

我更喜欢创建具有所需结构和约束的全新表，并用INSERT INTO ... SELECT语句填充它们。填充新表（并验证数据）后，重命名旧表，然后重命名新表。最后一步是将索引添加到新表中。一切正常后，可以将旧表存档。

我希望这有帮助。快乐的编码:)

哦，还有SQL Fiddle demo

关于mysql - 标准化具有冗余主键的MySQL表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/24243834/