复制关系表数据 [英] Copying Relational Table Data

问题描述

我希望我不会在这个问题太长，我只是想确保我要求是完全清楚（我认为这是很混乱：）。

I hope I'm not being too long winded in this question, I just want to make sure that what I'm asking is completely clear (I think it's rather confusing :).

我有一个数据库，一堆表，所有的外键约束设置。关系有时是几个表深，也有一个子节点与多个父表关联的情况。我想插入一个我的顶级表行的副本，以及与它相关的所有子表数据（保持关系完整性）。也就是说，我的新顶层行获得自己的新主键（通过auto_increment），所有新的子行获得自己的主键（再次通过auto_increment），并且表的所有外键信息类似地与数据我复制（只有现在与新创建的主键）。所以现在我会有一个独立可变的关系数据的副本从原来的。

I've got a database with a bunch of tables with all my foreign key constraints set up. Relations are sometimes several tables deep, and there are also cases where a child is related to more than one parent table. I want to insert a copy of one of my "top level" table rows, and all of child table data that relates to it (keeping the relational integrity). That is to say my new top level row gets its own new primary key (via auto_increment), and all the new child rows get their own primary keys (again through auto_increment), and all the foreign key information of the tables relates analogously to the data I copied (only now with the newly created primary keys). So now I would have a copy of the relational data that was independently mutable from the original.

为了使我的例子更具体，我精心设置了一个类似，但更简单的例子。让我们定义下列表格：

To make my example more concrete, I have painstakingly set up an analogous, but simpler example. Lets define the following tables:

alt text http://www.freeimagehosting.net/uploads/ef22070a89.png

所有绿色ID字段都是auto_update主键，淡黄色的具有外键约束的索引列。假设数据库最初有以下数据：

All the green id fields are auto_update primary keys, and the yellowish ones are indexed columns with foreign key constraints. Lets say the database has the following data in it initially:

job_types
+----+----------+
| id | jobcode  |
+----+----------+
|  1 | DEADBEEF |
|  3 | FEEDFACE |
+----+----------+

managers
+----+---------------+-------------+
| id | name          | job_type_id |
+----+---------------+-------------+
|  1 | John          |           1 |
|  3 | Michael Scott |           3 |
+----+---------------+-------------+

departments
+----+------+------------+
| id | name | manager_id |
+----+------+------------+
|  1 | H32  |          1 |
|  2 | X11  |          3 |
+----+------+------------+

employees
+----+-------------+---------------+------------+-------------+
| id | name        | department_id | manager_id | job_type_id |
+----+-------------+---------------+------------+-------------+
|  1 | Billy Bob   |             1 |          1 |           1 |
|  2 | Sandra Lee  |             1 |          1 |           3 |
|  3 | Buddy Holly |             2 |          3 |           1 |
+----+-------------+---------------+------------+-------------+

想做的是做一个关系部门H32（id = 1）的副本。

Now say what I want to do is make a relational copy of department H32 (id=1).

我最终得到的应该是以下内容（显然，主键的实际值并不重要，参照完整性是）。

What I should end up with is something like the following (obviously actual values of primary keys is not important, referential integrity is).

job_types
+----+----------+
| id | jobcode  |
+----+----------+
|  1 | DEADBEEF |
|  3 | FEEDFACE |
|  4 | DEADBEEF |
|  5 | FEEDFACE |
+----+----------+

managers
+----+---------------+-------------+
| id | name          | job_type_id |
+----+---------------+-------------+
|  1 | John          |           1 |
|  3 | Michael Scott |           3 |
|  4 | John          |           4 |
+----+---------------+-------------+

departments
+----+------+------------+
| id | name | manager_id |
+----+------+------------+
|  1 | H32  |          1 |
|  2 | X11  |          3 |
|  3 | H32  |          4 |
+----+------+------------+

employees
+----+-------------+---------------+------------+-------------+
| id | name        | department_id | manager_id | job_type_id |
+----+-------------+---------------+------------+-------------+
|  1 | Billy Bob   |             1 |          1 |           1 |
|  2 | Sandra Lee  |             1 |          1 |           3 |
|  3 | Buddy Holly |             2 |          3 |           1 |
|  4 | Billy Bob   |             3 |          4 |           4 |
|  5 | Sandra Lee  |             3 |          4 |           5 |
+----+-------------+---------------+------------+-------------+

执行方式来实现这种类型的复制操作？对于值得我使用MySQL，使用InnoDB表引擎，在Grails的上下文中。我期待着听到一些有关如何以正确的方式这样做的好主意。

What's the most efficiently performing way to implement this type of copy operation? For what it's worth I'm working with MySQL, using the InnoDB table engine, in the context of Grails. I'm looking forward to hearing some good ideas on how you do something like this "the right way."

- Regards，Vic

-- Regards, Vic

我在PasteBin上发布了示例的MySQLDump初始化。

I've posted a MySQLDump of the example initialization on PasteBin.

EDIT
对于值得的，我发布了一个更简单/更宽泛的问题这里，我得到一般积极的回应，表明我不是只是做错了...

EDIT For what it's worth, I posted a much simpler / broader question here and I got generally positive responses, suggesting that I'm not "just doing it wrong"...

推荐答案

我已经使用 INSERT INTO ... SELECT 语法。 （C api也有一个MYSQL_OPTION_MULTI_STATEMENTS_ON，你可以用它运行多个语句，或者你可以使用一个过程）。

I have done a similar thing using INSERT INTO ... SELECT syntax. (The C api also has a MYSQL_OPTION_MULTI_STATEMENTS_ON which you can use to run multiple statements with. or you could use a procedure).

这是最有效的，因为您不必在客户端和服务器之间移动数据。复制的值是用于创建新实体的模板。我不明白你为什么这样做这个分词数据模型。

This is the most effective, since you do not have to move data between the client and the server. The value copied was a template to create a new entity. I don't understand why you are doing this with this particiluar data model.

这篇关于复制关系表数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！