MySQL中的唯一性限制與NULL實例詳解
- 小云云原創
- 2018-01-13 14:10:521708瀏覽
本文主要為大家介紹了關於MySQL中唯一性約束與NULL的相關資料，文中介紹的非常詳細，對大家具有一定的參考學習價值，需要的朋友們下面來一起看看吧。希望能幫助大家。
前言
之前做的一個需求，簡化描述下就是接受其他群組的 MQ 的訊息，然後在資料庫裡插入一筆記錄。為了防止他們重複發送訊息，插入多個重複記錄，所以在表格中的幾個欄位上加了一個唯一性索引。
CREATE UNIQUE INDEX IDX_UN_LOAN_PLAN_APP ON testTable (A, B, C);
這時 A,B,C 三列都是不允許 NULL 值的，唯一性限制也是 work 的。
後來由於需求的變化，修改了先前的唯一性約束，又多加了一列。 (至於為什麼加就不贅述了)。
ALTER TABLE testTable DROP INDEX IDX_UN_LOAN_PLAN_APP, ADD UNIQUE KEY `IDX_UN_LOAN_PLAN_APP` (A, B, C, D);
新加的 D 是型別是 datetime, 允許為 NULL，預設值為 NULL。之所以預設值為 NULL，是考慮到不是所有記錄都有這個時間的， 如果強行設定一個 Magic Value (比如'1970-01-01 08:00:00‘)當做預設值，看起來很奇怪。
藍後。 。 。就出問題了。加了 D 之後，唯一性約束基本上就失效了。
Insert into testTable (A,B,C,D) VALUES (1,2,3,NULL); --- OK Insert into testTable (A,B,C,D) VALUES (1,2,3,NULL); --- OK Insert into testTable (A,B,C,D) VALUES (1,2,3,NULL); --- OK
上面的三條 SQL 都是可以執行成功的，資料庫中會有多筆一樣的記錄。可依照我們先前的構想，在執行後兩條 SQL 時 應該拋出 ‘Duplicate key' 的異常的。
後來查了一下，才發現其實MySQL 官方文件上已經明確說了這一點， 唯一性索引是允許多個NULL 值的存在的：
A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index allows multiple NULL values for columns that can contain NULL.
從下表中也可以看出來不管是採用什麼類型的儲存引擎，在建立unique key 的時候都是允許多個NULL 存在的。 。 。 。
細想想，其實也蠻合理，畢竟在 MySQL 中認為 NULL 代表著「未知」。 在 SQL 中，任何值與 NULL 的比較回傳值都是 NULL 而不是 TRUE, 就算 NULL 與 NULL 的比較也是回傳 NULL。
所以只能 fix 了。 。 。解決方法也蠻簡單粗暴的，直接把線上資料刷了一遍，將「1970-01-01 08:00:00」作為預設值，然後把那列改為不允許為 NULL 的了，咳。
MySQL 官網上也有蠻多人討論過這個問題，一部分人認為這是 MySQL 的 bug， 另一部分則認為是一個 feature，附上連結。
MySQL Bugs: #8173: unique index allows duplicates with null values
相關推薦：
以上是MySQL中的唯一性限制與NULL實例詳解的詳細內容。更多資訊請關注PHP中文網其他相關文章！