MySQL IODKU 進階實戰：批次 Upsert、VALUES() 引用、條件更新、LAST_INSERT_ID 技巧

INSERT ... ON DUPLICATE KEY UPDATE (IODKU) 是 MySQL 原子性 Upsert 核心。本文深入講解批次寫入、VALUES() 函數引用插入值、條件式更新、主鍵獲取等進階用法。

核心機制回顧

• 表必須有 PRIMARY KEY 或 UNIQUE INDEX
• 衝突判斷基於所有唯一鍵 (含主鍵)
• 衝突時執行 UPDATE 分支，不衝突執行 INSERT
• 單條語句原子性，避免競態條件

批次 Upsert 與 VALUES() 函數

多行插入，衝突時累加各自插入值

INSERT INTO stats (date, type, count) VALUES
('2024-01-01', 'view', 10),
('2024-01-01', 'click', 5),
('2024-01-02', 'view', 8)
ON DUPLICATE KEY UPDATE 
    count = count + VALUES(count);  -- 關鍵：引用對應行的插入值

效果：('2024-01-01', 'view') 衝突時，count += 10；('2024-01-01', 'click') 衝突時，count += 5。

錯誤寫法：直接用字面值

-- 錯誤：所有衝突行都加 10
ON DUPLICATE KEY UPDATE count = count + 10;

條件式更新

-- 只更新較大值
INSERT INTO scores (user_id, game_id, score) VALUES (1, 1, 9500)
ON DUPLICATE KEY UPDATE 
    score = IF(VALUES(score) > score, VALUES(score), score);

-- 只更新較新時間
INSERT INTO logs (id, content, updated_at) VALUES (1, 'new', NOW())
ON DUPLICATE KEY UPDATE 
    content = VALUES(content),
    updated_at = IF(VALUES(updated_at) > updated_at, VALUES(updated_at), updated_at);

多欄位更新與表達式

INSERT INTO products (sku, name, price, stock, updated_at) 
VALUES ('SKU001', '產品A', 99.9, 100, NOW())
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    price = VALUES(price),
    stock = stock + VALUES(stock),  -- 累加庫存
    updated_at = VALUES(updated_at);

獲取插入/衝突行的自增 ID

-- 表：id AUTO_INCREMENT PK
INSERT INTO users (name, email) VALUES ('John', 'john@example.com')
ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id);

SELECT LAST_INSERT_ID();  -- 正確返回新插入 ID 或現有 ID

原理：LAST_INSERT_ID(expr) 設定下一次 LAST_INSERT_ID() 返回值。

完整語法變體

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

-- 2. SET 形式 (MySQL 特有)
INSERT [LOW_PRIORITY|DELAYED|HIGH_PRIORITY] [IGNORE]
INTO tbl_name SET col1=val1, col2=val2,... 
[ON DUPLICATE KEY UPDATE col=expr,...]

-- 3. SELECT 形式 (跨表 Upsert)
INSERT [LOW_PRIORITY|HIGH_PRIORITY] [IGNORE]
INTO tbl_name [(col_list)] SELECT ... FROM ... 
[ON DUPLICATE KEY UPDATE col=expr,...]

SELECT 形式實戰：異構表同步

-- 從暫存表同步到主表
INSERT INTO main_table (id, name, status, updated_at)
SELECT id, name, status, NOW() FROM staging_table
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    status = VALUES(status),
    updated_at = VALUES(updated_at);

觸發器執行順序 (重要！)

BEFORE INSERT → (INSERT 或 UPDATE) → AFTER INSERT / AFTER UPDATE

衝突時：BEFORE INSERT → BEFORE UPDATE → 更新 → AFTER UPDATE (不觸發 AFTER INSERT)

效能與陷阱總結

項目	說明
AUTO_INCREMENT 消耗	每次嘗試插入 (含衝突) 都消耗自增值，高併發可能耗盡 BIGINT
死鎖風險	多併發 IODKU 同一唯一鍵易死鎖，建議應用層排隊或用 SELECT ... FOR UPDATE
複製安全	Row-based replication 安全；Statement-based 需 deterministic
IGNORE 差異	INSERT IGNORE 衝突靜默忽略；IODKU 衝突更新
REPLACE 差異	REPLACE = DELETE + INSERT，觸發器/外鍵/自增 ID 行為不同

最佳實踐清單

• 批次大小 500~2000 行，平衡封包大小與交易開銷
• 必須有唯一鍵，業務鍵優於技術鍵
• 使用 VALUES(col) 正確引用插入值
• 搭配 LAST_INSERT_ID() 獲取 ID
• 監控 Innodb_deadlocks 指標
• 測試環境驗證觸發器行為