mysql 的唯一索引要求所有参与的列都不能够为 null 值，如果唯一索引中的任何一个元素含有 null 值，则唯一约束将不起作用。

示例代码

create table tb (

a int,

b int,

c int,

unique index (a,b,c)

);

insert into tb(a,b,c) values (null,null,null); -- ok

insert into tb(a,b,c) values (null,null,null); -- still ok

insert into tb(a,b,c) values (null,null,null); -- still ok

insert into tb(a,b,c) values (1,null,null); -- ok

insert into tb(a,b,c) values (1,2,null); -- ok

insert into tb(a,b,c) values (1,2,3); -- ok

insert into tb(a,b,c) values (1,null,null); -- SHOULD FAIL, BUT DOESN'T

insert into tb(a,b,c) values (1,2,null); -- SHOULD FAIL, BUT DOESN'T

insert into tb(a,b,c) values (1,2,3); -- fails correctly

解决方案

给参与唯一索引的字段设置缺省值，如果是数值可以统一设置为 -1，如果是字符串可以设置为 ""

官网的描述

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 permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.

我们学习知识和原理，是否理解到位，重要的就是能不能将其运用到实际工作中，做到举一反三，融会贯通。

碰巧看到墨天轮上的一篇小文，标题是"Oracle的唯一索引为什么允许多个null行数据的存在？"，虽然知识点小，但从问题，再到作者的解释，就给我们做出了一个表率。

问题描述如下，

一同事提出Oracle中，一个表上有唯一索引，该列上就应该加上非空约束，他应该是在比对数据时遇到了问题，按照唯一索引列去比较，却没有比较出列值为null的数据，想当然的觉得应该让研发改规范，要把唯一索引列上加上非空约束。

我一听这个，这跟我以往使用的经验不符啊，Oracle是允许唯一索引列上，多行都是null值的。这个是我做java代码开发时就已知的事实，但当时就觉得数据库就是这样设计的，没去想为什么是这样。我当时就回复了那个同事一下，说数据库的唯一索引就是这样设计的，允许多个null存在，多个null不算重复。

实验如下所示，

SQL> create table test_unique_index(c1 number, c2 varchar2(1), c3 varchar2(1));
Table created.


SQL> create unique index idx_test_unique_index_01 on test_unique_index(c1, c2, c3);
Index created.


SQL> insert into test_unique_index values(1, 'a', null);
1 row created.


SQL> insert into test_unique_index values(1, 'a', null);
insert into test_unique_index values(1, 'a', null)
*
ERROR at line 1:
ORA-00001: unique constraint (BISAL.IDX_TEST_UNIQUE_INDEX_01) violated

可以看到，(1, 'a', null)记录不允许插入两次，这是不是就说多个null是重复的？他的同事继续说，

他说你看复合唯一索引时，有一个列为null出现了两次，Oracle却认为违反了唯一索引，这不就是认为多个null列也算重复吗，Oracle这样处理的是不是有点反常？

官方手册对唯一索引的说明，明确指出Oracle不会对所有列都是null的行进行索引存储，换句话说，如果不是说所有列均为null，就可以索引存储，

https://docs.oracle.com/cd/E11882_01/server.112/e40540/indexiot.htm#CNCPT1895

Unique and Nonunique Indexes

Indexes can be unique or nonunique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column or columns. For example, no two employees can have the same employee ID. Thus, in a unique index, one rowid exists for each data value. The data in the leaf blocks is sorted only by key.

Nonunique indexes permit duplicates values in the indexed column or columns. For example, the first_name column of the employees table may contain multiple Mike values. For a nonunique index, the rowid is included in the key in sorted order, so nonunique indexes are sorted by the index key and rowid (ascending).

Oracle Database does not index table rows in which all key columns are null, except for bitmap indexes or when the cluster key column value is null.

作者的回复，

什么是唯一索引？

唯一索引是说的唯一索引列的数据唯一还是索引段中存储的索引唯一，这样一想瞬间就明白了，索引唯一当然指的是索引段中存储的索引唯一。

Oracle为什么允许单列唯一索引中数据可以多行为null？那是因为null的数据不会存入索引。

Oracle为什么处理复合唯一索引时，有一个列为null出现了两次，Oracle却认为违反了唯一索引约束？那是因为复合索引列，只要有一列不为空就会存入索引中。

我们尝试dump上述索引数据块，可以看到，确实索引叶子节点数据块存储了(1, 'a', null)的数据，

...
row#XXX[XXXX] flag: -------, lock: 2, len=14, data:(6):  03 00 2b d7 00 00
col 0; len 1; (1):  61
col 1; len 2; (2):  c1 02
col 2; NULL
...

近期更新的文章：

《NFT，只可远观不可亵玩焉》

《MySQL数据库设计开发规范》

《SQL Server检索SQL和用户信息的需求》

《MySQL隐式转换案例一则》

《Linux几种数据传输工具比较》

文章分类和索引：

《公众号900篇文章分类和索引》