如何深度复制一组数据，并更改FK引用以指向所有副本？

Question

假设我有表A和表B。表B引用了表A。我想深入复制表A和表B中的一组行。我希望所有新的表B行都引用新的表A行。

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

如何确保将外键引用作为副本的一部分进行重新调整？

为了澄清，我试图找到一种通用的方式来做到这一点。我给出的示例涉及两个表，但是在实践中，依赖关系图可能要复杂得多。甚至是一种动态生成SQL的通用方法也可以。

更新：

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

我正在使用一个旧的桌面应用程序，该应用程序已移至客户端-
服务器模型。但是，该应用程序仍使用基本的内部二进制文件格式来存储其表的数据。数据文件只是一个标题，后跟一系列行，每行都是二进制序列化的字段值，其顺序由模式文本文件确定。唯一的好处是它非常快。在其他各个方面都很糟糕。我正在将应用程序移至SQL
Server，并尝试不降低性能。

这是一种调度应用程序；数据对任何人都不重要，也没有必要进行审核跟踪等。它不是大量的数据，如果数据库太大，我们不必保留非常旧的数据。

他们习惯于使用的一项功能是能够复制整个计划，以创建他们可以采用的“假设分析”方案。任何用户都可以根据需要多次执行此操作。在旧数据库中，每个日程表的数据文件都存储在它们自己的数据文件夹中，该文件夹由名称标识。因此，复制日程表就像复制数据文件夹并重命名一样简单。

我 必须 能够使用SQL
Server有效地执行相同的操作，否则迁移将无法进行。也许您在想，为了避免冗余，我只能复制实际上已更改的数据。但这听起来实在太复杂了以至于不可行。

要将其他扳手投入使用，可以有一个计划数据文件夹层次结构。因此，数据文件夹可能包含一个数据文件夹，该数据文件夹可能包含一个数据文件夹。并且复制可以发生在任何级别。

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

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引用，指示它属于哪个数据集。每当我复制数据集时，我都会将表中该数据集的所有行以及所有其他数据集复制到同一张表中，但要引用新的数据集。

因此，这是一个简单的具体示例：

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的新数据集中。复制后，表将如下所示：

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的重新布线。我想问的是一般重新布线外键。

所以，那肯定是太多的信息，但是您去了。

我敢肯定，像这样批量复制数据的想法会引起很多关于性能的担忧。桌子不会很​​大。我不希望任何表中有超过1000条记录，并且大多数表都比这小得多。可以直接删除旧数据集，而不会造成任何影响。

谢谢，泰德兹

Answer 1

这是一个包含三个表的示例，可能会帮助您入门。

数据库架构

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来克隆用户的日程和事件记录

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 演示。