MySQL 是否忽略唯一约束上的空值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3712222/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Does MySQL ignore null values on unique constraints?
提问by Hugo Mota
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?
我有一个电子邮件列，我希望它是独一无二的。但我也希望它接受空值。我的数据库可以有 2 个空电子邮件吗？
回答by Mark Byers
Yes, MySQL allows multiple NULLs in a column with a unique constraint.
是的，MySQL 允许在具有唯一约束的列中存在多个 NULL。
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;
Result:
结果：
x
NULL
NULL
1
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.
并非所有数据库都如此。例如，SQL Server 2005 及更早版本只允许在具有唯一约束的列中使用单个 NULL 值。
回答by Matthew Flaschen
回答by bluegman991
I am unsure if the author originally was just asking whether or not this allows duplicate values or if there was an implied question here asking, "How to allow duplicate NULLvalues while using UNIQUE?" Or "How to only allow one UNIQUENULLvalue?"
我不确定作者最初是否只是在问这是否允许重复值，或者这里是否存在一个隐含的问题，“如何NULL在使用时允许重复值UNIQUE？” 或者“如何只允许一个UNIQUENULL值？”
The question has already been answered, yes you can have duplicate NULLvalues while using the UNIQUEindex.
问题已经得到解答，是的，您NULL在使用UNIQUE索引时可以有重复的值。
Since I stumbled upon this answer while searching for "how to allow one UNIQUENULLvalue." For anyone else who may stumble upon this question while doing the same, the rest of my answer is for you...
因为我在搜索“如何允许一个UNIQUENULL值”时偶然发现了这个答案。对于在做同样的事情时可能会偶然发现这个问题的其他人，我的其余答案是给你的......
In MySQL you can not have one UNIQUENULLvalue, however you can have one UNIQUEempty value by inserting with the value of an empty string.
在 MySQL 中，您不能只有一个UNIQUENULL值，但是您可以UNIQUE通过插入一个空字符串的值来获得一个空值。
Warning: Numeric and types other than string may default to 0 or another default value.
警告：数字和字符串以外的类型可能默认为 0 或其他默认值。
回答by nvogel
Avoid nullable unique constraints. You can always put the column in a new table, make it non-null and unique and then populate that table only when you have a value for it. This ensures that any key dependency on the column can be correctly enforced and avoids any problems that could be caused by nulls.
避免可为空的唯一约束。您始终可以将列放入新表中，使其非空且唯一，然后仅在您有值时才填充该表。这确保可以正确强制执行对列的任何键依赖，并避免可能由空值引起的任何问题。
