解決MySQL複合主鍵下ON DUPLICATE KEY UPDATE語句失效問題

標籤：mysql

先描述一下這個問題的起因，假設有一張表，裡面儲存了交易訂單，每張訂單有唯一的ID，有最後更新時間，還有資料，詳情如下：

+-------+----------+------+-----+---------------------+-------+| Field | Type     | Null | Key | Default             | Extra |+-------+----------+------+-----+---------------------+-------+| UID   | int(11)  | NO   | PRI | 0                   |       || Time  | datetime | NO   |     | 0000-00-00 00:00:00 |       || Data  | int(11)  | YES  |     | NULL                |       |+-------+----------+------+-----+---------------------+-------+

針對這張表會做追加及更新的操作，具體來說就是如果訂單不存在就INSERT一條新的，如果已存在就UPDATE。由於入庫前無法得知相應記錄是否已存在，通常的做法無法以下幾種：

1、先SELECT一下，再決定INSERT還是UPDATE；

2、直接UPDATE，如果受影響行數是0，再INSERT；

3、直接INSERT，如果發生主鍵衝突，再UPDATE；

這幾種方法都有缺陷，對MySQL來說其實最好的是直接利用INSERT...ON DUPLICATE KEY UPDATE...語句，具體到上面的test表，執行語句如下 ：

INSERT INTO test VALUES (1, ‘2016-1-1‘, 10) ON DUPLICATE KEY UPDATE Time=‘2016-1-1‘,Data=10;

可以很好的插入或更新資料，一條語句就搞定，至此一直工作得很好。

後來因為查詢方式變更，要求將UID和Time兩個欄位做聯合主鍵，此時表結構如下：

+-------+----------+------+-----+---------------------+-------+| Field | Type     | Null | Key | Default             | Extra |+-------+----------+------+-----+---------------------+-------+| UID   | int(11)  | NO   | PRI | 0                   |       || Time  | datetime | NO   | PRI | 0000-00-00 00:00:00 |       || Data  | int(11)  | YES  |     | NULL                |       |+-------+----------+------+-----+---------------------+-------+

但是問題來了：一但Time欄位被更新，即使是相同的UID，也被資料庫認為是不同的主鍵，因此不會產生主鍵衝突，上面的語句就失效了，資料庫裡出現了很多UID相同的資料。

開始尋找解決辦法，其實也簡單，按MySQL文檔裡的說明，ON DUPLICATE KEY UPDATE語句判斷是否衝突是依靠主鍵或唯一索引，因此為UID建立唯一索引就可以了。先建索引：

CREATE UNIQUE INDEX IDX_UID ON test(UID);

再測試一下插入：

INSERT INTO test VALUES (1, ‘2016-1-1‘, 10) ON DUPLICATE KEY UPDATE Time=‘2016-1-1‘,Data=10;INSERT INTO test VALUES (1, ‘2016-2-1‘, 20) ON DUPLICATE KEY UPDATE Time=‘2016-2-1‘,Data=20;

檢查資料庫，可以看到不會有多條資料產生，唯一的一條資料是Data欄位被更新成20的，成功。

本文出自 “兔子窩” 部落格，請務必保留此出處http://boytnt.blog.51cto.com/966121/1736690

解決MySQL複合主鍵下ON DUPLICATE KEY UPDATE語句失效問題