MySQL中關於唯一性的限制與NULL詳解

這裡記錄的是很久之前的一個bug 了，主要給大家介紹了關於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中文網其他相關文章！