mysql - 将多个列规范化为 MySQL 中的一个多对多表

Question

我想将数据分布在多列中的表规范化为多对多表。

在我的例子中，我有一个 person表 hobby1 , hobby2 , hobby3 , hobby4包含在 hobby 中找到的名称的字符串列 table 。有些是空的有些不是。

目前的数据库设计是这样的:

当前 person_hobby是空的。

你能帮我做填充 person_hobby 的 SQL 查询吗？包含在 hobby 中找到的所有字符串的表格与 hobby.name 相关的列这样我以后就可以摆脱它们了？

谢谢

Answer 1

例如:

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 |
+-----------+----------+

现在从 person 表中删除列。