如何将mysql表拆分_在MySQL中,如何将一个已有的表拆分为多个新的相关表?

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)

);

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;

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);

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)