复制具有n:m关系的数据集

Question

我希望使用单个SQL语句

insert into T (...) select ... from T where ...

复制大量数据集。我的问题是，从表格 T 到其他表格存在 N:M 关系，这些也必须被复制。如果我不知道哪个原始数据集属于哪个复制数据集，该怎么办？让我举个例子来说明。

之前数据库的内容：

表格 T：

ID  | COL1 | COL2    
-----------------
1   | A    | B
2   | C    | D

N:M-表从表T引用了表U（表U未显示）：

T   | U              
---------
1   | 100
1   | 101
2   | 100
2   | 102

我的复制操作，其中[???]是我不知道的部分：

insert into T (COL1, COL2) select COL1, COL2 from T
insert into NM (T, U) select [???]

之后的数据库内容：

表格 T：

ID  | COL1 | COL2
-----------------
1   | A    | B
2   | C    | D
3   | A    | B
4   | C    | D

N:M表：

T   | U
---------
1   | 100
1   | 101
2   | 100
2   | 102
3   | 100
3   | 101
4   | 100
4   | 102

注意：

• 我有成千上万个数据集（不仅仅是两个）
• 我想使用“insert ... select”来获得更好的性能

- user1027167

---

我不理解问题，尤其是最后一句话。只需创建您想要的任何 select - 根据需要加入多个表，这将产生一个结果表，并将其插入即可。就这样！ - Tomas

@Tomas：我想从一个表格复制到同一个表格。 - user1027167

这不应该成为问题 - 我曾经有一个非常类似的问题，这应该不会是什么问题。因此，请按照您通常的方式准备select子查询，然后尝试将其放置在插入语句下面，无需担心任何问题。 - Tomas

我添加了一个小例子，在你的“非常相似的问题”中，你进行了更新。我想要进行插入操作，但不知道插入的ID。 - user1027167

id列是一个带有自增的主键。 - user1027167

Answer 1

如果你很幸运地使用当前的PostgreSQL 9.1，那么有一个优雅且快速的解决方案，只需使用新的数据修改CTE即可完成。

MySQL没有支持公共表达式(CTE)，更不用说是数据修改CTE了。

假设(col1,col2)最初是唯一的：

查询1

• 在这种情况下，您可以轻松地从表中选择任意片段。
• t.id没有浪费序列号。

WITH s AS (
    SELECT id, col1, col2
    FROM   t
--  WHERE  some condition
    )
    ,i AS (
    INSERT INTO t (col1, col2)
    SELECT col1, col2   -- I gather from comments that id is a serial column
    FROM   s
    RETURNING id, col1, col2
    )
INSERT INTO tu (t, u)
SELECT i.id, tu.u
FROM   tu
JOIN   s ON tu.t = s.id
JOIN   i USING (col1, col2);

如果 (col1, col2) 不是唯一的，我看到另外两种方法：

查询2

• 使用 窗口函数row_number() 使非唯一行变为唯一。
• 像上面的查询一样，在 t.id 空间中插入没有空缺的行。

WITH s AS (
    SELECT id, col1, col2
         , row_number() OVER (PARTITION BY col1, col2) AS rn
    FROM   t
--  WHERE some condition
    )
    ,i AS (
    INSERT INTO t (col1, col2)
    SELECT col1, col2
    FROM   s
    RETURNING id, col1, col2
    )
    ,r AS (
    SELECT *
         , row_number() OVER (PARTITION BY col1, col2) AS rn
    FROM   i
    )
INSERT INTO tu (t, u)
SELECT r.id, tu.u
FROM   r
JOIN   s USING (col1, col2, rn)    -- match exactly one id per row
JOIN   tu ON tu.t = s.id;

查询 3

• 这基于 @ypercube 已经提供的相同思路，但是全部在一个查询中完成。
• 如果当前 t.id 的数字空间中存在空洞，则新行将相应地烧录序列号。
• 不要忘记在新的最大值之后重置您的序列，否则对于从序列中获取默认值的id的新插入操作，您将获得重复键错误。我将此作为最终步骤整合到命令中。这是最快和最安全的方法。

WITH s AS (
    SELECT max(id) AS max_id
    FROM   t
    )
    ,i AS (
    INSERT INTO t (id, col1, col2)
    SELECT id + s.max_id, col1, col2
    FROM   t, s
    )
    ,j AS (
    INSERT INTO tu (t, u)
    SELECT tu.t + s.max_id, tu.u
    FROM   tu, s
    )
SELECT setval('t_id_seq', s.max_id + s.max_id)
FROM   s;

手册中关于setval()的详细信息。

测试设置

用于快速测试。

CREATE TEMP TABLE t (id serial primary key, col1 text, col2 text);
INSERT INTO t (col1, col2) VALUES 
 ('A', 'B')
,('C', 'D');

CREATE TEMP TABLE tu (t int, u int);
INSERT INTO tu VALUES
 (1, 100)
,(1, 101)
,(2, 100)
,(2, 102);

SELECT * FROM t;
SELECT * FROM tu;

最近有一个类似的问题，我提供了一个相似的答案。此外还有针对8.3版本没有公共表达式和窗口函数的替代方案。

- Erwin Brandstetter

Answer 2

步骤1. 锁定（两个）表或确保只有此脚本在运行。禁用FK检查。

步骤2. 按照以下顺序使用这两个INSERT语句：

INSERT INTO NM 
    (T, U) 
  SELECT 
      T + maxID, U
  FROM 
      NM
    CROSS JOIN
      ( SELECT MAX(ID) AS maxID 
        FROM T
      ) AS m

INSERT INTO T 
    (ID, COL1, COL2) 
  SELECT 
      ID+maxID, COL1, COL2 
  FROM 
      T
    CROSS JOIN
      ( SELECT MAX(ID) AS maxID 
        FROM T
      ) AS m

步骤三。重新启用外键。

- ypercubeᵀᴹ

---

别忘了在完成操作后重置你的序列列。 - Erwin Brandstetter