MySQL 是否忽略唯一约束上的空值?
我有一个电子邮件列，我希望它是独一无二的.但我也希望它接受空值.我的数据库可以有 2 个空电子邮件吗?
I have an email column that I want to be unique. But I also want it to accept null values. Can my database have 2 null emails that way?
推荐答案
是的，MySQL 允许在具有唯一约束的列中存在多个 NULL.
Yes, MySQL allows multiple NULLs in a column with a unique constraint.
CREATE TABLE table1 (x INT NULL UNIQUE);
INSERT table1 VALUES (1);
INSERT table1 VALUES (1); -- Duplicate entry '1' for key 'x'
INSERT table1 VALUES (NULL);
INSERT table1 VALUES (NULL);
SELECT * FROM table1;
结果:
x
NULL
NULL
1
这并非适用于所有数据库.例如，SQL Server 2005 及更早版本仅允许在具有唯一约束的列中使用单个 NULL 值.
This is not true for all databases. SQL Server 2005 and older, for example, only allows a single NULL value in a column that has a unique constraint.
相关文章