Mysql

可以在 MySQL 的 BULK INSERT 中跳過重複鍵(即 MERGE)嗎?

  • January 10, 2022

我有一條INSERT...SELECT語句,我想從 TABLE1 到 TABLE2 執行幾次,因為 TABLE1 中的數據會隨著時間而改變。

我不希望 select 過去的數據會隨著時間的推移而改變,select 只會返回更多的新數據,以及之前查詢的相同舊數據。

但是由於主鍵的唯一鍵約束,我無法再次插入舊數據。

我想要實現的是一種INSERT ...ON DUPLICATE KEY UPDATE...但對於INSERT...SELECT. 跳過重複的鍵就足夠了,但我不知道該怎麼做

MySQL中沒有MERGE聲明。您可以按如下方式輕鬆完成此操作(所有 DDL 和 DML 都顯示在文章底部以及此處的小提琴中):

我創建並填充了兩個表foo_1,並foo_2使用相同的數據和相同PRIMARY KEY的欄位 ( foo_x, foo_y);

然後我在 table 中添加了一條記錄foo_1

然後我執行了查詢:

INSERT INTO foo_2 
SELECT * FROM foo_1 WHERE (foo_x, foo_y) -- foo_x, foo_y are the PK!
NOT IN (SELECT foo_x, foo_y FROM foo_2);

然後SELECT * FROM foo_2,表中的新記錄foo_1已被複製到表中foo_2。據我所知,這是一種簡單而有效的方法來做你想做的事。使用單個欄位執行此PRIMARY KEY操作更加容易!

=============== DDL和DML ============

foo_1

CREATE TABLE foo_1
(
 foo_x INTEGER NOT NULL,
 foo_y INTEGER NOT NULL,
 foo_2 INTEGER NOT NULL,
 foo_3 VARCHAR (25) NOT NULL,
 PRIMARY KEY (foo_x, foo_y)
);
INSERT INTO foo_1 
VALUES 
(1, 1, 2, 'foo_3_val_1'),
(1, 2, 7, 'foo_3_val_2'),
(1, 3, 8, 'foo_3_val_3'),
(1, 4, 9, 'foo_3_val_4');

和表foo_2

CREATE TABLE foo_2
(
 foo_x INTEGER NOT NULL,
 foo_y INTEGER NOT NULL,
 foo_2 INTEGER NOT NULL,
 foo_3 VARCHAR (25) NOT NULL,
 PRIMARY KEY (foo_x, foo_y)
);
INSERT INTO foo_2
VALUES 
(1, 1, 2, 'foo_3_val_1'),
(1, 2, 7, 'foo_3_val_2'),
(1, 3, 8, 'foo_3_val_3'),
(1, 4, 9, 'foo_3_val_4');

將新記錄插入表中foo_1

INSERT INTO foo_1 VALUES (1, 5, 24, 'foo_3_val_5');

進而:

INSERT INTO foo_2 
SELECT * FROM foo_1 WHERE (foo_x, foo_y)
NOT IN (SELECT foo_x, foo_y FROM foo_2);

然後檢查該表現在是否foo_2包含來自 table 的單個新記錄foo_1

SELECT * FROM foo_2;

結果:

foo_x   foo_y   foo_2   foo_3
1       1           2   foo_3_val_1
1       2           7   foo_3_val_2
1       3           8   foo_3_val_3
1       4           9   foo_3_val_4
1       5          24   foo_3_val_5
INSERT IGNORE INTO t2
   SELECT ... FROM t1 ...;

聽起來像你想要的。筆記:

  • 它將“忽略”與UNIQUE鍵(包括PRIMARY KEY)匹配的任何 t1 行,僅插入其餘行。
  • 如果 t2 有一個AUTO_INCREMENT列(並且它沒有被用於 ‘IGNORE’),這個命令很可能會“燒掉”id。(IODKU 也會燒掉 id。)這是因為該操作有效地發現有多少行來自SELECT並分配那麼多 id ,然後才意識到有些不會被使用。
  • 由於上述兩個注意事項,最好有一個“自然PK”,而不是auto_inc。
  • IODKU 就像INSERT IGNORE加上更改非唯一列、設置modified_date、計算更改次數等的能力。
  • 如果你必須有一個 auto_inc,但“燒錄 id”是一個問題,你可能需要去BIGINT. 或者你可以使用這裡討論的兩步技術:http: //mysql.rjweb.org/doc.php/staging_table#normalization——我喜歡“批量標準化”;它專為大量攝取數據而設計。

引用自:https://dba.stackexchange.com/questions/260420