PostgreSQL 實現 MySQL INSERT IGNORE 語義的四種方案：RULE、觸發器、CTE、存儲過程

MySQL INSERT IGNORE 在主鍵/唯一鍵衝突時靜默忽略、不報錯、不中斷批量插入。PostgreSQL 無直接語法對應，但提供四種機制實現等價語義。本文以實測案例逐一演示：Rule 系統、觸發器函數、CTE 排除法、存儲過程異常捕獲。

測試環境準備

CREATE TABLE insert_ignore (
    id INTEGER PRIMARY KEY,
    log_time TIME WITHOUT TIME ZONE
);
INSERT INTO insert_ignore VALUES (1, '14:44:12');

方案一：Rule 重寫系統（最簡潔、聲明式）

CREATE RULE r_insert_ignore AS
ON INSERT TO insert_ignore
WHERE EXISTS (SELECT 1 FROM insert_ignore WHERE id = NEW.id)
DO INSTEAD NOTHING;

測試

INSERT INTO insert_ignore VALUES (1, CURRENT_TIME), (2, CURRENT_TIME);
-- INSERT 0 1  (只有 id=2 插入成功，id=1 靜默忽略)
SELECT * FROM insert_ignore;
-- id=1 原有行保留，id=2 新增

優點：純 SQL、無函數開銷、批量插入單條指令完成。
缺點：Rule 系統較隱蔽，遷移/備份易被忽略；PostgreSQL 官方文檔建議新項目優先用觸發器。

方案二：觸發器函數返回 NULL（標準、可控）

1. 創建函數

CREATE OR REPLACE FUNCTION sp_insert_ignore() RETURNS TRIGGER AS $$
BEGIN
    PERFORM 1 FROM insert_ignore WHERE id = NEW.id;
    IF FOUND THEN
        RETURN NULL;  -- 忽略本行插入
    END IF;
    RETURN NEW;       -- 繼續插入
END;
$$ LANGUAGE plpgsql;

2. 掛載觸發器

CREATE TRIGGER tr_ib_insert_ignore
BEFORE INSERT ON insert_ignore
FOR EACH ROW EXECUTE PROCEDURE sp_insert_ignore();

測試

INSERT INTO insert_ignore VALUES (3, CURRENT_TIME), (2, CURRENT_TIME);
-- INSERT 0 1  (id=3 成功，id=2 忽略)

優點：邏輯清晰、易調試、符合 SQL 標準觸發器模式。
缺點：逐行執行函數，極大批量插入有函數調用開銷。

方案三：CTE 排除法（無對象創建、單條 SQL 完成）

INSERT INTO insert_ignore
WITH ytt_test(f1, f2) AS (
    VALUES (6, CURRENT_TIME), (3, CURRENT_TIME)
)
SELECT a.* FROM ytt_test a
WHERE a.f1 NOT IN (SELECT id FROM insert_ignore);

測試

-- 結果：id=6 插入，id=3 忽略
SELECT * FROM insert_ignore;

優點：臨時一次性需求無需創建對象、純 SQL、可讀性強。
缺點：NOT IN 子查詢含 NULL 會全掃描，建議改 NOT EXISTS；極大數據集性能不如觸發器/Rule。

方案四：存儲過程捕獲異常（過程式、可返回錯誤信息）

CREATE OR REPLACE FUNCTION sp_insert_ignore(
    IN f_id INT, IN f_log_time TIME WITHOUT TIME ZONE
) RETURNS VOID AS $$
BEGIN
    INSERT INTO insert_ignore VALUES (f_id, f_log_time);
EXCEPTION WHEN unique_violation THEN
    RAISE NOTICE 'Duplicated Key Error on ID:%', f_id;
    RETURN;
END;
$$ LANGUAGE plpgsql;

測試

SELECT sp_insert_ignore(1, '14:22:35');  -- NOTICE: Duplicated Key Error on ID:1
SELECT sp_insert_ignore(8, '14:22:35');  -- 成功插入
SELECT * FROM insert_ignore;             -- id=8 新增

優點：可記錄/上報衝突詳情、業務邏輯可編程控制。
缺點：需逐行調用、不支持批量數組傳入（需自行拆分循環）。

方案對比與選型建議

PostgreSQL 15+ 新特性：INSERT ... ON CONFLICT DO NOTHING

PostgreSQL 9.5+ 已原生支持標準 SQL ON CONFLICT 子句，完美對應 MySQL INSERT IGNORE：

INSERT INTO insert_ignore (id, log_time)
VALUES (1, '14:44:12'), (2, '14:44:12')
ON CONFLICT (id) DO NOTHING;

強烈建議新項目直接使用原生語法，無需上述四種變通方案，性能最優、語義最清晰、標準兼容。

總結

PostgreSQL 歷版本提供多種變通實現 INSERT IGNORE，**9.5+ 請直接用 ON CONFLICT DO NOTHING**；舊版本按場景選：高頻批量用 Rule、常規業務用觸發器、一次性腳本用 CTE、需記錄衝突用存儲過程。