mysql - 在MySQL中，如何将一个现有表拆分为许多新的相关表？

我有一张和这张相似的桌子。去foo的酒吧数量实际上只有两个

table foo_old
 ID           int
 name         string
 bar_name_1   string
 bar_code_1   int
 bar_name_2   string
 bar_code_2   int

既然这让我哭了，我想把它分成两张这样的桌子

table foo_new
 ID           int
 name         string
 ID_bar_1     int
 ID_bar_2     int

table bar_new
 ID           int
 name         string
 code         int

我的问题是，如何编写脚本来创建匹配的记录
在foo_u new中，对于foo_u old中的每个现有记录，包括新关联的bar_u new记录的新id？

最佳答案

首先创建foo_new和bar_new表：

CREATE TABLE foo_new (
   id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   name varchar(100),
   id_bar_1 int,
   id_bar_2 int
);

CREATE TABLE bar_new (
   id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   name varchar(100),
   code int,
   UNIQUE (code)
);

然后用bar_new中所有不同的条填充foo_old表：

INSERT INTO bar_new (name, code)
SELECT DISTINCT bar_name_1 name, bar_code_1 code FROM foo_old
UNION
SELECT DISTINCT bar_name_2 name, bar_code_2 code FROM foo_old;

然后将foo_old与bar_new连接以填写foo_new：

INSERT INTO `foo_new` (name, id_bar_1, id_bar_2)
SELECT  f.name, b1.id, b2.id
FROM    foo_old f
JOIN    bar_new b1 ON (b1.code = f.bar_code_1)
JOIN    bar_new b2 ON (b2.code = f.bar_code_2);

测试用例：

CREATE TABLE foo_old (
   id int NOT NULL PRIMARY KEY,
   name varchar(100),
   bar_name_1 varchar(100),
   bar_code_1 int,
   bar_name_2 varchar(100),
   bar_code_2 int
);

INSERT INTO foo_old VALUES (1, 'foo1', 'bar1', 1, 'bar2', 2);
INSERT INTO foo_old VALUES (2, 'foo2', 'bar6', 6, 'bar5', 5);
INSERT INTO foo_old VALUES (3, 'foo3', 'bar4', 4, 'bar3', 3);
INSERT INTO foo_old VALUES (4, 'foo4', 'bar2', 2, 'bar7', 7);
INSERT INTO foo_old VALUES (5, 'foo5', 'bar6', 6, 'bar5', 5);
INSERT INTO foo_old VALUES (6, 'foo6', 'bar4', 4, 'bar1', 1);
INSERT INTO foo_old VALUES (7, 'foo7', 'bar7', 7, 'bar4', 4);
INSERT INTO foo_old VALUES (8, 'foo8', 'bar3', 3, 'bar8', 8);

这就是上述操作之后foo_new和bar_new的外观：

SELECT * FROM foo_new ORDER BY name;
+----+------+----------+----------+
| id | name | id_bar_1 | id_bar_2 |
+----+------+----------+----------+
|  3 | foo1 |        1 |        4 |
|  6 | foo2 |        2 |        7 |
|  5 | foo3 |        3 |        6 |
|  4 | foo4 |        4 |        5 |
|  7 | foo5 |        2 |        7 |
|  1 | foo6 |        3 |        1 |
|  2 | foo7 |        5 |        3 |
|  8 | foo8 |        6 |        8 |
+----+------+----------+----------+
8 rows in set (0.00 sec)


SELECT * FROM bar_new ORDER BY name;
+----+--------+------+
| id | name   | code |
+----+--------+------+
|  1 | bar1   |    1 |
|  4 | bar2   |    2 |
|  6 | bar3   |    3 |
|  3 | bar4   |    4 |
|  7 | bar5   |    5 |
|  2 | bar6   |    6 |
|  5 | bar7   |    7 |
|  8 | bar8   |    8 |
+----+--------+------+
8 rows in set (0.00 sec)

关于mysql - 在MySQL中，如何将一个现有表拆分为许多新的相关表？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/3889678/