1. 什麼是UPSERT？
概要
「UPSERT」是指在資料庫中結合「INSERT」與「UPDATE」的功能。也就是說，如果資料不存在就插入新的資料；若已存在相同的資料，則更新該資料。透過這個功能，可以在保持資料一致性的同時，更有效率地進行操作。
在 MySQL 中，INSERT ... ON DUPLICATE KEY UPDATE 語法對應此功能。藉由這個語法，即使遇到資料重複的情況，也能避免重複鍵錯誤，並更新既有資料。
使用情境
- 客戶管理系統：當沒有新客戶資料時新增，有既有客戶資料變更時則更新。
- 商品庫存管理：新增新商品，同時更新現有商品的庫存數量。
MySQL 中 UPSERT 的優點
- 避免重複鍵錯誤
- SQL 查詢語句更簡潔
- 保持資料的一致性
2. MySQL 中 UPSERT 的基本用法
在 MySQL 中，UPSERT 的操作是透過 INSERT ... ON DUPLICATE KEY UPDATE 語法來實現。當發生重複鍵時，系統不會插入新資料，而是更新現有資料的部分或全部欄位。
基本語法
INSERT INTO 資料表名稱 (欄位1, 欄位2)
VALUES (值1, 值2)
ON DUPLICATE KEY UPDATE
欄位1 = 值1, 欄位2 = 值2;解說：
- 使用
INSERT INTO插入資料。 - 若欲插入的資料已存在於資料表中，則執行
ON DUPLICATE KEY UPDATE部分，更新既有資料。
範例：
INSERT INTO users (user_id, name)
VALUES (1, '田中太郎')
ON DUPLICATE KEY UPDATE
name = '田中太郎';以上範例中，若 user_id 為 1 的使用者已存在，則會將其 name 欄位更新為「田中太郎」。若不存在，則插入新資料。
3. UPSERT 的 SQL 語法細節與範例
更新多個欄位的情況
使用 UPSERT 時，也可以只更新部分欄位。在這種情況下，可以在 ON DUPLICATE KEY UPDATE 子句中指定特定欄位。
INSERT INTO products (product_id, name, price)
VALUES (100, '筆記型電腦', 50000)
ON DUPLICATE KEY UPDATE
price = VALUES(price);此範例中，若 product_id 為 100 的商品已存在，則只更新 price 欄位，其餘欄位（如 name）不會變更。
4. 與其他資料庫的差異
除了 MySQL 之外，其他資料庫也有類似操作。例如 PostgreSQL 與 SQLite 提供 INSERT ... ON CONFLICT 或 MERGE 語法來實現 UPSERT。
PostgreSQL 範例
INSERT INTO users (user_id, name)
VALUES (1, '田中太郎')
ON CONFLICT (user_id) DO UPDATE SET
name = '田中太郎';在 PostgreSQL 與 SQLite 中，會使用 ON CONFLICT 子句來控制重複鍵錯誤時的行為；而 MySQL 則使用 ON DUPLICATE KEY UPDATE。
MySQL 的特殊性
- MySQL 採用
INSERT ... ON DUPLICATE KEY UPDATE，語法與其他資料庫不同，移轉時需特別注意。
5. 進階 UPSERT 的用法
批次 UPSERT（多筆資料一次處理）
UPSERT 不僅可針對單筆資料，也能一次處理多筆資料，大幅提升資料庫操作效率。
INSERT INTO products (product_id, name, price)
VALUES
(100, '筆記型電腦', 50000),
(101, '智慧型手機', 30000)
ON DUPLICATE KEY UPDATE
price = VALUES(price);此範例中，一次插入多筆商品資料；若有重複的鍵，則僅更新該商品的價格。
利用儲存程序的 UPSERT
為了讓 UPSERT 更有效率，可以利用儲存程序（Stored Procedure）。這樣能在資料庫內建立可重複使用的程式碼，提升程式的可讀性與維護性。
6. UPSERT 的陷阱與注意事項
交易與死鎖
在使用 UPSERT 時，特別是處理大量資料時，可能會發生死鎖問題。若 MySQL 的交易隔離層級設定為 REPEATABLE READ，更容易引發間隙鎖（Gap Lock）。
避免間隙鎖的方法
- 將交易隔離層級改為
READ COMMITTED可降低死鎖發生機率。 - 必要時可將 UPSERT 操作拆分為多次查詢，以減少風險。
7. 總結
MySQL 的 UPSERT 功能能有效結合資料插入與更新，避免重複鍵錯誤。然而在實作時需注意死鎖風險與交易設定。若能正確使用，將能讓資料庫操作更簡單且高效。
