如何深度复制一组数据，并将FK引用更改为点到所有副本？ [英] How Do I Deep Copy a Set of Data, and Change FK References to Point to All the Copies?

问题描述

假设我有表A和表B.表B引用表A.我想深度复制表A和表B中的一组行。我想要所有新的表B行引用新的表A行。

Suppose I have Table A and Table B. Table B references Table A. I want to deep copy a set of rows in Table A and Table B. I want all of the new Table B rows to reference the new Table A rows.

请注意，我不会将行复制到任何其他表。表A中的行将被复制到表A中，表B中的行将被复制到表B中。

Note that I'm not copying the rows into any other tables. The rows in table A will be copied into table A, and the rows in table B will be copied into table B.

如何确保外键引用获得重新调整为副本的一部分？

How can I ensure that the foreign key references get readjusted as part of the copy?

为了澄清，我试图找到一个通用的方法来做到这一点。我给出的示例涉及两个表，但在实践中，依赖图可能要复杂得多。即使是动态生成SQL来执行工作的通用方法也行。

To clarify, I'm trying to find a generic way to do this. The example I'm giving involves two tables, but in practice the dependency graph may be much more complicated. Even a generic way to dynamically generate SQL to do the work would be fine.

UPDATE：

问为什么这是必要的，所以我给一些背景。这可能太多了，但是这里：

People are asking why this is necessary, so I'll give some background. It may be way too much, but here goes:

我正在使用一个移动到客户端 - 服务器模型的旧桌面应用程序。但是，应用程序仍然使用一个基本的内部二进制文件格式来存储其表的数据。数据文件只是一个头，后面跟着一系列行，每一行只是二进制序列化字段值，其顺序由模式文本文件确定。唯一好的是它是非常快。这是可怕的在其他方面。我将应用程序移动到SQL Server，并尽量不降低性能。

I'm working with an old desktop application that's been moved to a client-server model. But, the application still uses a rudimentary in-house binary file format for storing data for its tables. A data file is just a header followed by a series of rows, each of which is just the binary serialized field values, the order of which is determined by a schema text file. The only thing good about it is that it's very fast. It's terrible in every other respect. I'm moving the application to SQL Server and trying not to degrade the performance too badly.

这是一种调度应用程序;数据对任何人都不重要，并且没有审计跟踪等。这不是一个超大量的数据，如果数据库变得太大，我们不一定需要保存非常旧的数据。

This is a kind of scheduling application; the data's not critical to anybody, and there's no audit tracking, etc. necessary. It's not a supermassive amount of data, and we don't necessarily need to keep very old data around if the database grows too large.

他们习惯的一个特性是能够复制整个计划，以创建他们可以迷惑的假设场景。任何用户都可以根据自己的需要多次执行此操作。在旧数据库中，每个计划的数据文件存储在自己的数据文件夹中，以名称标识。因此，复制日程表与复制数据文件夹和重命名一样简单。

One feature that they are accustomed to is the ability to duplicate entire schedules in order to create "what-if" scenarios that they can muck with. Any user can do this as many times as they want, as often as they want. In the old database, the data files for each schedule are stored in their own data folder, identified by name. So, copying a schedule was as simple as copying the data folder and renaming it.

我必须能够有效地做同样的事情SQL Server或迁移将无法正常工作。也许你以为我只能复制实际改变的数据，以避免冗余;但真实地听起来太复杂，不可行。

I must be able to do effectively the same thing with SQL Server or the migration will not work. Maybe you're thinking that I can just only copy the data that actually gets changed in order to avoid redundancy; but that honestly sounds too complicated to be feasible.

要将另一把扳手放在组合中，可以有一个层次结构的日程表数据文件夹。因此，数据文件夹可以包含数据文件夹，其可以包含数据文件夹。复制可以发生在任何级别。

To throw another wrench into the mix, there can be a hierarchy of schedule data folders. So, a data folder may contain a data folder, which may contain a data folder. And the copying can occur at any level.

在SQL Server中，我实现一个嵌套集层次结构来模仿这一点。我有一个DATA_SET表如下：

In SQL Server, I'm implementing a nested set hierarchy to mimic this. I have a DATA_SET table like this:

CREATE TABLE dbo.DATA_SET
(
    DATA_SET_ID UNIQUEIDENTIFIER PRIMARY KEY,
    NAME NVARCHAR(128) NOT NULL,
    LFT INT NOT NULL,
    RGT INT NOT NULL
)

所以，有一个数据集的树结构。每个数据集表示调度，并且可以包含子数据集。每个表中的每一行都有一个DATA_SET_ID FK引用，指示它属于哪个数据集。每当我复制数据集时，我将该数据集和其他所有数据集的表中的所有行复制到同一个表中，但引用了新的数据集。

So, there's a tree structure of data sets. Each data set represents a schedule, and may contain child data sets. Every row in every table has a DATA_SET_ID FK reference, indicating which data set it belongs to. Whenever I copy a data set, I copy all the rows in the table for that data set, and every other data set, into the same table, but referencing new data sets.

这里有一个简单的具体例子：

So, here's a simple concrete example:

CREATE TABLE FOO
(
    FOO_ID BIGINT PRIMARY KEY,
    DATA_SET_ID BIGINT FOREIGN KEY REFERENCES DATA_SET(DATA_SET_ID) NOT NULL
)


CREATE TABLE BAR
(
    BAR_ID BIGINT PRIMARY KEY,
    DATA_SET_ID BIGINT FOREIGN KEY REFERENCES DATA_SET(DATA_SET_ID) NOT NULL,
    FOO_ID UNIQUEIDENTIFIER PRIMARY KEY
)

INSERT INTO FOO
SELECT 1, 1 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 1 UNION ALL

INSERT INTO BAR
SELECT 1, 1, 1
SELECT 2, 1, 2
SELECT 3, 1, 3

所以，假设我将数据集1复制到一个新的数据集ID 2。复制后，表格将如下所示：

So, let's say I copy data set 1 into a new data set of ID 2. After I copy, the tables will look like this:

FOO
FOO_ID, DATA_SET_ID
1    1
2    1
3    1
4    2
5    2
6    2

BAR
BAR_ID, DATA_SET_ID, FOO_ID
1    1    1
2    1    2
3    1    3
4    2    4
5    2    5
6    2    6

如您所见，新的BAR行引用了新的FOO行。这不是我询问的DATA_SET_ID的重新布线。

As you can see, the new BAR rows are referencing the new FOO rows. It's not the rewiring of the DATA_SET_ID's that I'm asking about. I'm asking about rewiring the foreign keys in general.

所以，这确实是太多的信息，但是你去了。

So, that was surely too much information, but there you go.

我肯定有很多关于性能的关注大量复制这样的数据的想法。表不会是巨大的。我不期望在任何表中超过1000条记录，并且大多数表将比这小得多。

I'm sure there are a lot of concerns about performance with the idea of bulk copying the data like this. The tables are not going to be huge. I'm not expecting more than 1000 records in any table, and most of the tables will be much much smaller than that. Old data sets can be deleted outright with no repercussions.

感谢，
Tedderz

Thanks, Tedderz

推荐答案

这里是一个有三个表的例子，可能让你开始。

Here is an example with three tables that can probably get you started.

DB架构

CREATE TABLE users
    (user_id int auto_increment PRIMARY KEY, 
     user_name varchar(32));
CREATE TABLE agenda
    (agenda_id int auto_increment PRIMARY KEY, 
     `user_id` int, `agenda_name` varchar(7));
CREATE TABLE events
    (event_id int auto_increment PRIMARY KEY, 
     `agenda_id` int, 
     `event_name` varchar(8));

一个SP用于克隆拥有他的议程和活动记录的用户

An SP to clone a user with his agenda and events records

DELIMITER $$
CREATE PROCEDURE clone_user(IN uid INT)
BEGIN
    DECLARE last_user_id INT DEFAULT 0;

    INSERT INTO users (user_name)
    SELECT user_name
      FROM users
     WHERE user_id = uid;

    SET last_user_id = LAST_INSERT_ID();

    INSERT INTO agenda (user_id, agenda_name)
    SELECT last_user_id, agenda_name
      FROM agenda
     WHERE user_id = uid;

    INSERT INTO events (agenda_id, event_name)
    SELECT a3.agenda_id_new, e.event_name
      FROM events e JOIN
    (SELECT a1.agenda_id agenda_id_old, 
           a2.agenda_id agenda_id_new
      FROM
    (SELECT agenda_id, @n := @n + 1 n 
       FROM agenda, (SELECT @n := 0) n 
      WHERE user_id = uid 
      ORDER BY agenda_id) a1 JOIN
    (SELECT agenda_id, @m := @m + 1 m 
       FROM agenda, (SELECT @m := 0) m 
      WHERE user_id = last_user_id 
      ORDER BY agenda_id) a2 ON a1.n = a2.m) a3 
         ON e.agenda_id = a3.agenda_id_old;
END$$
DELIMITER ;

克隆用户

CALL clone_user(3);

这是 SQLFiddle 演示。

Here is SQLFiddle demo.

这篇关于如何深度复制一组数据，并将FK引用更改为点到所有副本？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！