會員匯入原本想新增一筆資料，結果卻改了舊會員的名稱。檢查SQL時不能只看新傳入的id，也要看資料表有哪些唯一鍵。INSERT … ON DUPLICATE KEY UPDATE在新增資料碰到PRIMARY KEY或UNIQUE衝突時，會轉去更新既有列。這個分支是資料庫的鍵約束觸發，並不是程式先查到某個欄位就自動選對會員。
新id不代表這筆一定新增
以下例子用訂閱者表，id是主鍵，email另有UNIQUE約束。資料表先存一位id為1、email為[email protected]、name為old的訂閱者。接著送入id為2的新資料，但email仍是同一個地址。id沒有重複，不代表整列沒有唯一鍵衝突：email的UNIQUE仍會攔住新增。
CREATE TABLE subscribers (
id INT PRIMARY KEY,
email VARCHAR(100) NOT NULL UNIQUE,
name VARCHAR(30) NOT NULL
);
INSERT INTO subscribers
(id, email, name)
VALUES (1, '[email protected]', 'old');先看表結構再讀upsert，會比只追應用程式傳入的id清楚。這裡email使用NOT NULL，避免把「沒有email」的NULL情況混進判斷；email比較仍受欄位型別與collation影響。這篇案例只用相同的小寫字串驗證衝突，沒有測試不同大小寫或所有郵件地址的相等規則，也不把資料庫字串相等當成完整郵件驗證。
用新列別名寫出更新來源
INSERT INTO subscribers
(id, email, name)
VALUES (2, '[email protected]', 'updated')
AS incoming
ON DUPLICATE KEY UPDATE
name = incoming.name;AS incoming替原本嘗試新增的列取別名。更新分支的incoming.name是這次送入的updated，不是資料表目前的old。例子只更新name，沒有指定id或email的更新，因此舊列仍保留id為1與原email。使用新列別名也讓更新來源一眼可辨；MySQL文件已將更新分支中的VALUES()寫法列為棄用，不宜再把舊寫法當成新範例。這裡討論的是MySQL，不保證其他資料庫採用相同語法。
實際讀回是哪一列被更新
本地隔離案例使用MySQL 8.4.9，在新資料庫執行以上SQL後讀回整表。結果只有id為1的列，name已變成updated，沒有產生id為2的列。接著送入id為3、email為[email protected]、name為new，因兩個唯一鍵都不衝突，這次才走新增分支。兩次操作的差異是鍵是否衝突，不是名稱內容或輸入順序。
SELECT id, email, name
FROM subscribers
ORDER BY id;
-- after same-email upsert
-- 1 [email protected] updated
INSERT INTO subscribers
(id, email, name)
VALUES (3, '[email protected]', 'new')
AS incoming
ON DUPLICATE KEY UPDATE
name = incoming.name;
-- after new-email upsert
-- 1 [email protected] updated
-- 3 [email protected] new案例建立的是自有隔離資料表，沒有接正式會員資料，也沒有執行HTTP匯入。它證明相同email觸發更新，以及不同鍵值可以新增；不能據此宣稱整套會員合併、權限或寄信流程已測過。應用程式若以傳入id為2判定「新增成功」，就會和資料庫實際結果不一致。回傳訊息與後續作業應根據真正保存的列處理。
多個唯一鍵要另外處理歧義
若同一張表有多個唯一索引，一次輸入可能與不同既有列衝突。例如傳入id命中甲列，email卻命中乙列，不能把upsert想成「固定按email找人」的可靠規則。MySQL官方文件提醒避免在有多個唯一索引的表任意使用這種寫法；更新分支若又造成另一個唯一鍵衝突，也可能失敗。這篇只驗證單一既有列的email衝突，沒有用案例替多鍵衝突保證更新對象。
如果業務規則明確要求以某個識別欄位找人，應先把資料模型與更新流程設計清楚。確認識別欄位是否真能唯一代表同一人，其他唯一約束是否可能指向另一列，再決定upsert是否適合。不要因為SQL省了一次程式端查詢，就省略會員識別的判斷。資料庫只維護你定義的約束，不知道兩份輸入在業務上應該合併還是拒絕。
更新欄位要有明確界線
範例只允許name從新列帶入，這讓讀回結果容易核對。實際匯入若把所有欄位都放進UPDATE清單，可能覆蓋既有資料的狀態、建立時間或其他不應由這次輸入改動的欄位。寫SQL前先列出此次操作負責哪些欄位，再逐項指定。這是更新內容的選擇，不代表資料庫已完成輸入驗證或使用者授權。
也不要把upsert和REPLACE當成同義詞。兩者對衝突的處理不同，這裡沒有使用REPLACE，更沒有測試刪除後新增可能牽涉的其他行為。如果需求是保留既有列並更新少數欄位，就先沿著ON DUPLICATE KEY UPDATE的實際語意檢查，不要為了「看起來都能寫入」而替換指令。
把同一筆匯入拆成可核對的問題
收到一筆匯入資料時，先問它代表哪個對象，再問哪些欄位允許更新，最後才選SQL。假設電子郵件地址是此次匯入採用的識別值，就要知道它是否已被另一位使用者使用，以及輸入的id是否只是匯入檔的暫時編號。若外部系統的id與本站主鍵不是同一套編號，直接拿它插入主鍵可能產生意料之外的碰撞。範例用手動整數是為了觀察分支，並沒有提供跨系統識別值轉換規則。
檢查既有資料時，可以先讀回相同email的列，再看輸入的id是否指向其他列。這種查詢有助於理解資料，但正式流程不能假設「先查後寫」中間永遠沒有其他連線改動。唯一約束仍由資料庫在寫入時判斷；需要跨多步驟維持業務條件時，交易、鎖定與錯誤處理要另外設計。本案例是一條連線的順序操作，沒有測試併發競爭或重試。
匯入完成後，讀回資料時應使用此次業務採用的識別條件，而不是盲目用輸入的新id查詢。範例第一次傳入id為2，資料庫中卻沒有這個id；若程式接著以id為2寄信或建立關聯，就可能找不到資料。真正要保存到其他表的識別值，必須來自已核實的既有或新增列。不要把範例裡的固定id為1硬寫進應用程式，它只是隔離資料的觀察結果。
測試資料也應讓錯誤容易暴露。保留舊名稱old，更新時用不同名稱updated，再新增另一個email，能直接分清兩次分支。若每次都送相同name，更新前後看起來沒有差異，容易把未更新、設成相同值與新增混在一起。案例中每次操作後都查整表，正式資料量大時則以限定條件讀回需要的欄位，避免把一次排查變成無範圍的全表檢視。
用讀回資料判斷流程是否符合預期
MySQL對每列的affected rows，通常以新增為1、修改既有列為2、既有列設成相同值為0；連線的CLIENT_FOUND_ROWS設定會影響相同值時的回報。應用程式不宜把「大於零」簡化成「新增了一個會員」，也不能只憑這個數字得知哪個唯一鍵碰撞。本案例主要核對讀回列與id，沒有把所有客戶端旗標逐一實跑。
排查時先看SHOW CREATE TABLE確認主鍵與唯一索引，再比較這次輸入的鍵值與既有列，最後看UPDATE清單到底改哪些欄位。測試至少包含已存在識別值與全新識別值兩種情況，並讀回id及業務欄位。若多鍵可能衝突，再加上各鍵指向不同列的專門測試。這樣才能判斷匯入工具實際在新增還是更新，而不是只看沒有SQL錯誤就認定資料正確。
評論0