mysql 唯一索引为null_mysql 唯一索引与null.md

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.