您现在的位置：程式碼之家 >> 編程語言 > >> 更多程式語言 >> 程式設計疑難解答

SQL 批量插入防重複：聯合主鍵約束 + INSERT IGNORE / MERGE 多方案詳解

編輯：程式設計疑難解答

場景還原

表 A、B、C，需將 A、B 某些列組合插入 C。
C 表：logid (PK, FK到A), msgid (PK, FK到B), col3 (默認值)。
(logid, msgid) 為聯合主鍵。
批量插入時自動跳過已存在的組合。

方案一：聯合主鍵 + INSERT IGNORE (MySQL / MariaDB)

-- 表結構已定義聯合主鍵
INSERT IGNORE INTO C (logid, msgid)
SELECT A.logid, B.msgid
FROM A, B
WHERE ...; -- 連接條件

-- 重複鍵行直接忽略，不報錯，不中斷，返回受影響行數(不含忽略行)

方案二：ON DUPLICATE KEY UPDATE (MySQL) — 無實際更新

INSERT INTO C (logid, msgid)
SELECT A.logid, B.msgid FROM A, B
ON DUPLICATE KEY UPDATE logid = logid; -- 虛擬更新，觸發跳過邏輯

方案三：MERGE (SQL Server / Oracle / PostgreSQL 15+ / 標準 SQL)

-- SQL Server
MERGE INTO C AS target
USING (SELECT A.logid, B.msgid FROM A, B) AS source
ON (target.logid = source.logid AND target.msgid = source.msgid)
WHEN NOT MATCHED THEN
    INSERT (logid, msgid) VALUES (source.logid, source.msgid);

方案四：NOT EXISTS 子查詢 (通用、所有數據庫)

INSERT INTO C (logid, msgid)
SELECT A.logid, B.msgid
FROM A, B
WHERE NOT EXISTS (
    SELECT 1 FROM C
    WHERE C.logid = A.logid AND C.msgid = B.msgid
);

方案五：LEFT JOIN + IS NULL (通用、高性能)

INSERT INTO C (logid, msgid)
SELECT A.logid, B.msgid
FROM A
JOIN B ON ... -- 你的關聯條件
LEFT JOIN C ON C.logid = A.logid AND C.msgid = B.msgid
WHERE C.logid IS NULL;

性能與選型建議

方案	適用庫	性能	備註
INSERT IGNORE	MySQL	⭐⭐⭐⭐⭐	最簡潔，靠引擎級去重
MERGE	SQL Server/Oracle/PG	⭐⭐⭐⭐	標準語法，功能最強(可更新)
NOT EXISTS	所有	⭐⭐⭐	優化器通常轉為 Anti-Join
LEFT JOIN	所有	⭐⭐⭐⭐	大數據量常優於 NOT EXISTS

關鍵前提：正確定義聯合主鍵

CREATE TABLE C (
    logid BIGINT NOT NULL,
    msgid BIGINT NOT NULL,
    col3 VARCHAR(20) DEFAULT 'default',
    PRIMARY KEY (logid, msgid) -- 核心：聯合主鍵
);

無主鍵/唯一約束，任何去重方案都無法在數據庫層面保證原子性、並發安全。

總結

批量插入防重複，**首選數據庫層面約束 (聯合主鍵) + 方言專用語法 (INSERT IGNORE / MERGE)**。MySQL 用 INSERT IGNORE 最爽；SQL Server/Oracle 用 MERGE 最標準；跨庫兼容用 LEFT JOIN ... IS NULL。切記：應用層去重不可靠，必須靠數據庫約束。