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); -- okinsert into tb(a,b,c) values (null,null,null); -- still okinsert into tb(a,b,c) values (null,null,null); -- still okinsert into tb(a,b,c) values (1,null,null); -- okinsert into tb(a,b,c) values (1,2,null); -- okinsert into tb(a,b,c) values (1,2,3); -- okinsert into tb(a,b,c) values (1,null,null); -- SHOULD FAIL, BUT DOESN'Tinsert into tb(a,b,c) values (1,2,null); -- SHOULD FAIL, BUT DOESN'Tinsert 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.

参考文章