在MySQL中使用三个表填充多对多人-颜色表

我正在尝试在MySQL中规范化一个表，将一个具有许多相似列的表转换为具有两个列的多对多关系。我有以下表格：

人员：

+----+------+
| id | name |
+----+------+
| 1  | John |
| 2  | Anna |
| 3  | Leon |
+----+------+

Person_Temp：

+------+--------+--------+--------+--------+
| name | color1 | color2 | color3 | color4 |
+------+--------+--------+--------+--------+
| John |  red   |  blue  |  green |        |
| Anna |  green | yellow |        |        |
| Leon |  blue  |  red   |        |        |
+------+--------+--------+--------+--------+

颜色：

+----+--------+
| id |  name  |
+----+--------+
| 1  | red    |
| 2  | blue   |
| 3  | green  |
| 4  | yellow |
+----+--------+

我想在填充多对多关系表后删除PERSON_TEMP：

人色：

+-----------+----------+
| person_id | color_id |
+-----------+----------+
|     1     |    1     |
|     1     |    2     |
|     1     |    3     |
|     2     |    3     |
|     2     |    4     |
|     3     |    2     |
|     3     |    1     |
+-----------+----------+

但是，我还没有找到我的查询的任何解决方案。我与人员ID的唯一关系是Person_Temp中的名称。我知道姓名在人称中是唯一的，因此使用它们进行查询不成问题。

我尝试使用此SQL，但它不起作用，因为Person_Temp没有id列。

INSERT INTO `person_color`
SELECT p.id, c.id
FROM (
  SELECT id, color1 color
  FROM person_temp
  UNION
    SELECT id, color2 FROM person_temp
  UNION
    SELECT id, color3 FROM person_temp
  UNION
    SELECT id, color4 FROM person_temp
  UNION
    SELECT id, color5 FROM person_temp
) p
  JOIN color c
    ON c.name = p.color;

---

解决方案

您的原始查询简单明了。除了从person_temp中选择name而不是id，您只需将name与id中的id连接在一起。您不需要单独对person_temp执行所有这些联接。

INSERT INTO person_color
SELECT p.id AS person_id, c.id AS color_id
FROM (
     SELECT name, color1 AS color FROM person_temp
  UNION
    SELECT name, color2 FROM person_temp
  UNION
    SELECT name, color3 FROM person_temp
  UNION
    SELECT name, color4 FROM person_temp
  UNION
    SELECT name, color5 FROM person_temp
) nc
JOIN color c
    ON c.name = nc.color
JOIN person p
    ON p.name = nc.name;

表行没有顺序，因此在插入中ORDER BY p.id, c.id是没有意义的。输出行有顺序。

无论是在提出关系设计的意义上，还是在更高范式的意义上，您都没有实现规范化。您只是在改进设计。