場景重現(xiàn)

開發(fā)同學為了防止插入多條重復(fù)記錄，在表中這3個列上加了個唯一性索引： uk_col1_col2_col3 (col1,col2,col3) 。 然而下面這3條數(shù)據(jù)竟然插入成功了，且沒有拋出 ‘Duplicate key’ 的異常：

mysql> insert into uktest(col1,col2,col3) values(1,2,null);
Query OK, 1 row affected (0.01 sec)

mysql> insert into uktest(col1,col2,col3) values(1,2,null);
Query OK, 1 row affected (0.00 sec)

mysql> insert into uktest(col1,col2,col3) values(1,2,null);
Query OK, 1 row affected (0.00 sec)

mysql> select col1,col2,col3  from uktest;
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    1 |    2 | NULL |
|    1 |    2 | NULL |
|    1 |    2 | NULL |
+------+------+------+
3 rows in set (0.00 sec)

上面的三條 SQL 都執(zhí)行成功了，數(shù)據(jù)庫中插入了多條一樣的記錄?？砂凑瘴覀兊臉?gòu)想，在執(zhí)行后兩條 SQL 時 應(yīng)該拋出 ‘Duplicate key’ 的異常的。有點詭異~

還有這種操作

查看表結(jié)構(gòu)如下：

mysql> show create table uktest;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                                                        |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| uktest | CREATE TABLE `uktest` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '無意義的ID',
  `col1` int(11) NOT NULL DEFAULT '0',
  `col2` int(11) NOT NULL DEFAULT '0',
  `col3` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_col1_col2_col3` (`col1`,`col2`,`col3`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8     |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

col1、col2 都是not null 的，插入時有明確的值。col3 列是 datetime 類型，默認值為 NULL 。

懷疑和 NULL 有關(guān)，查查圣經(jīng) →_→ 【官方文檔】：

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。

何以解憂？

將 col3 列改為 NOT NULL ，且有默認值。時間默認值可以設(shè)置為一個有意義的值，比如'1979-01-01 08:00:01' 或 '2000-01-01 00:00:01'。

這也是為啥軍規(guī)中明確規(guī)定：索引字段請務(wù)必設(shè)置為 not null 的原因之一啦，除了提高索引效率節(jié)省空間外，也是為了避免“唯一索引失效了”這類誤會啦~

真相了

[參考]：唯一性約束與null