Mysql
可以在 MySQL 的 BULK INSERT 中跳過重複鍵(即 MERGE)嗎?
我有一條
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——我喜歡“批量標準化”;它專為大量攝取數據而設計。