mysql nval_mysql – 使用n：m-relation复制数据集

如果你足够幸运地运行当前的PostgreSQL 9.1,那么使用新的data-modifying CTEs只需一个命令即可获得优雅而快速的解决方案.

没有这样的运气,MySQL不支持Common Table Expressions (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

>使用window function 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的数字空间有漏洞,则相应的新行将被烧掉序列号.

>不要忘记将序列重置为新的最大值,否则您将在t中获得重复的键错误,从而从序列中绘制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;

有点similar question recently,我提供了一个类似的答案.对于没有CTE和窗口功能的8.3版本的替代品.