MySQL INSERT 進階語法實戰：DELAYED、IGNORE、ON DUPLICATE KEY UPDATE 三大擴展用法與選型指南

MySQL 在標準 SQL 基礎上擴展了三大 INSERT 修飾符，解決高併發寫入、冪等導入、業務 upsert 等典型場景。本文深度解析語法、行為差異、適用邊界與組合陷阱，給出選型決策表。

一、 語法總覽

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_list)]
    VALUES (val_list), ... 
    [ON DUPLICATE KEY UPDATE col=expr, ...]

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    SET col=val, ...
    [ON DUPLICATE KEY UPDATE ...]

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_list)]
    SELECT ... 
    [ON DUPLICATE KEY UPDATE ...]

二、 三大修飾符深度解析

1. DELAYED —— 異步寫入，犧牲一致性換吞吐

行為流程

1. 客戶端發送 INSERT DELAYED → 服務端立即返回 OK (不等寫盤)。
2. 行放入內存隊列 (每表一個隊列)。
3. 專用線程批量寫盤，寫入期間有讀請求 → 隊列掛起讓路。
4. 隊列為空 → 線程休眠。

關鍵限制

• 僅支持 MyISAM / MEMORY / ARCHIVE，InnoDB 忽略 DELAYED (退為普通 INSERT)。
• 不支持 SELECT / ON DUPLICATE KEY UPDATE 組合。
• 無法獲取 LAST_INSERT_ID() (自增值在寫盤時才生成)。
• 數據僅在內存：kill -9 / 崩潰 / 重啟 → 隊列數據全丟。
• 從庫不執行：複製時忽略 DELAYED，避免主從數據不一致。

適用場景

日誌寫入、埋點上報、非核心業務、極高寫入峰值、可容忍少量丟失。

2. IGNORE —— 靜默吞噬錯誤，實現冪等導入

行為

• 主鍵/唯一鍵衝突 → 忽略該行，不報錯，繼續後續行。
• 數據截斷/類型轉換錯誤 (非嚴格模式) → 警告 + 修正值寫入。
• 返回值：affected_rows = 實際插入行數 (忽略的不計)。

典型用法

-- 大文件冪等導入，主鍵衝突自動跳過
INSERT IGNORE INTO facts (id, metric, val) VALUES ...;

-- 批量建立關聯，避免重複報錯
INSERT IGNORE INTO user_role (user_id, role_id) SELECT ...;

3. ON DUPLICATE KEY UPDATE (ODKU) —— Upsert 核心武器

觸發條件

插入行導致 主鍵 或 任一唯一索引 衝突 → 執行 UPDATE 子句。

返回值語義

• 1 = 新插入行
• 2 = 更新現有行
• 0 = IGNORE 生效時的衝突行 (罕見組合)

VALUES() 函數引用插入值

INSERT INTO stats (date, type, cnt) VALUES ('2024-01-15', 'pv', 1)
ON DUPLICATE KEY UPDATE cnt = cnt + VALUES(cnt);

多行批量 upsert 時，VALUES(col) 取對應行的插入值。

多唯一索引陷阱

-- 表有 UK(a) 和 UK(b)
INSERT ... ON DUPLICATE KEY UPDATE ...
-- 若 a 衝突更新，b 也衝突但不同行 → 僅更新第一個匹配行 (非確定性)
-- 建議：單表最多一個唯一鍵參與 ODKU，或業務保證不同時衝突。

三、 組合與互斥規則

四、 選型決策表

五、 生產最佳實踐

1. 批量多值 INSERT： INSERT INTO t (a,b) VALUES (1,2),(3,4),...; 單條 500-1000 行，配合 max_allowed_packet 調大。
2. ODKU 覆蓋索引： 更新列 + 唯一鍵列組成覆蓋索引，避免回表。
3. 避免觸發器副作用： ODKU 會觸發 BEFORE/AFTER UPDATE 觸發器，注意遞歸/性能。
4. 監控 affected_rows： 1=新增、2=更新，用於業務指標統計。

六、 總結

DELAYED = 異步火力掩護 (MyISAM 專屬)；IGNORE = 靜默冪等盾；ODKU = 原子 Upsert 劍。 InnoDB 時代 DELAYED 已邊緣化，核心武器是 批量多值 INSERT + ODKU。理解三者互斥規則與返回值語義，能在高併發寫入、數據同步、計數器業務中寫出正確、高效、可觀測的 SQL。