mysql - 在MySQL中将几列标准化为一对多表

我想用分散在多列中的数据将表标准化为多对多表。

就我而言，我有一个person表，其中的hobby1，hobby2，hobby3，hobby4字符串列包含在hobby表中找到的名称。有些是空的，有些不是。

当前的数据库设计如下所示：



当前person_hobby为空。

您可以通过SQL查询为我提供帮助，以用与person_hobby相关的hobby列中找到的所有字符串填充hobby.name表的方法，以便以后删除它们吗？

谢谢

最佳答案

例如。：

DROP TABLE IF EXISTS person;

CREATE TABLE person
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,name VARCHAR(12) NOT NULL
,hobby1 VARCHAR(15) NULL
,hobby2 VARCHAR(15) NULL
,hobby3 VARCHAR(15) NULL
,hobby4 VARCHAR(15) NULL
);

DROP TABLE IF EXISTS person_hobby;

CREATE TABLE person_hobby
(person_id INT NOT NULL
,hobby_id INT NOT NULL
,PRIMARY KEY(person_id,hobby_id)
);

DROP TABLE IF EXISTS hobby;

CREATE TABLE hobby
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,name VARCHAR(15) NOT NULL UNIQUE
);

INSERT INTO person VALUES
(1,'John'  ,'Reading'   ,'Cycling','Swimming',NULL),
(2,'Paul'  ,'Travelling','TV','Bird watching','Cinema'),
(3,'George','Fishing'   ,'Swimming',NULL,NULL),
(4,'Ringo',NULL,NULL,NULL,NULL);

INSERT INTO hobby (name)
SELECT hobby1 FROM person WHERE hobby1 IS NOT NULL UNION
SELECT hobby2 FROM person WHERE hobby2 IS NOT NULL UNION
SELECT hobby3 FROM person WHERE hobby3 IS NOT NULL UNION
SELECT hobby4 FROM person WHERE hobby4 IS NOT NULL;

INSERT INTO person_hobby
SELECT p.id
     , h.id
  FROM
     (
SELECT id
     , hobby1 hobby
  FROM person
 UNION
SELECT id
     , hobby2
  FROM person
 UNION
SELECT id
     , hobby3
  FROM person
 UNION
SELECT id
     , hobby4
  FROM person
     ) p
  JOIN hobby h
    ON h.name = p.hobby;

Query OK, 9 rows affected (0.05 sec)
Records: 9  Duplicates: 0  Warnings: 0

SELECT * FROM person_hobby;
+-----------+----------+
| person_id | hobby_id |
+-----------+----------+
|         1 |        1 |
|         1 |        4 |
|         1 |        6 |
|         2 |        2 |
|         2 |        5 |
|         2 |        7 |
|         2 |        8 |
|         3 |        3 |
|         3 |        6 |
+-----------+----------+

现在，从人员表中删除列。