MySQL INSERT 語法深度解析：DELAYED、IGNORE、ON DUPLICATE KEY UPDATE 三大擴展實戰

INSERT 完整語法骨架

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
  [INTO] tbl_name [(col_name,...)]
  {VALUES ({expr | DEFAULT},...),(...),...
   | SET col_name={expr | DEFAULT}, ...
  }
  [ON DUPLICATE KEY UPDATE col_name=expr, ...]

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
  [INTO] tbl_name [(col_name,...)]
  SELECT ...
  [ON DUPLICATE KEY UPDATE col_name=expr, ...]

一、DELAYED：異步寫入、犧牲一致性換吞吐

運作機制

1. 客戶端發送 INSERT DELAYED，服務器將行放入隊列，**立即返回 OK**。
2. 後台線程批量寫入隊列數據；有 SELECT 讀取時暫停寫入，讀完繼續。
3. 隊列僅在記憶體，**崩潰/殺進程會丟失未刷盤數據**。

限制與禁忌

• 僅支援 VALUES 列表，**不支援 SELECT、ON DUPLICATE KEY UPDATE** (會被忽略)。
• 無法獲取 LAST_INSERT_ID() (自增值尚未生成)。
• 從庫複制中被忽略 (避免主從不一致)。
• MySQL 5.6+ 已棄用，8.0 移除，改用批量 INSERT + 應用層異步隊列 (Kafka/RabbitMQ/Redis Stream)。

二、IGNORE：靜默吞噬錯誤、保業務不中斷

行為

• 主鍵/唯一鍵衝突：**跳過該行，不報錯，繼續後續行**。
• 數據截断/類型轉換錯誤：**修正為最接近合法值 (截斷、轉 0/空串)，繼續** (非嚴格模式)。
• STRICT_TRANS_TABLES 模式下部分錯誤仍會報錯。

典型用法

INSERT IGNORE INTO users (id, email) VALUES (1, 'a@b.com'), (2, 'c@d.com');
-- id=1 已存在則跳過，id=2 不存在則插入
-- 無需先 SELECT 判斷，原子化「不存在則插入」

三、ON DUPLICATE KEY UPDATE：衝突轉更新、實現 UPSERT

觸發條件

表有 PRIMARY KEY 或 UNIQUE 索引，插入行導致鍵值衝突。

效果對比

-- 假設 a 為 UNIQUE，已有 a=1
INSERT INTO t (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
-- 等效
UPDATE t SET c=c+1 WHERE a=1;

影響行數語義

• 1：新插入 1 行。
• 2：更新 1 行 (刪 1 + 插 1 內部實現)。
• >2：多個唯一索引同時衝突，刪除多行再插 1 行。

VALUES() 函數引用插入值

INSERT INTO t (a,b,c) VALUES (1,2,3), (4,5,6)
  ON DUPLICATE KEY UPDATE c = VALUES(a) + VALUES(b);
-- 第一行衝突 → c=1+2=3
-- 第二行衝突 → c=4+5=9
-- 僅在 ON DUPLICATE KEY UPDATE 子句中有意義

多唯一索引陷阱

-- 假設 a、b 均為 UNIQUE
INSERT INTO t (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
-- 衝突條件：a=1 OR b=2
-- 若同時匹配多行，僅更新第一行 (LIMIT 1 隱含)
-- 建議：避免在多唯一鍵表使用，或確保業務邏輯單一衝突

四、三大擴展選型決策表

五、效能與並發提示

• 批量 VALUES (...),(...),(...) (500~5000 行/批) 遠優於循環單條 INSERT。
• ON DUPLICATE KEY UPDATE 會加排他鎖 (X-lock) 衝突行，高並發熱點鍵易死鎖/鎖等待；考慮應用層去重或分佈式鎖。
• IGNORE 仍會檢查唯一索引，大量衝突時 CPU 開銷大；預先去重更佳。