MySql 复合键和空值
我注意到，如果我有两列的唯一复合键，column_a 和 column_b，那么如果一列为空，我的 sql 将忽略此约束.
I have noticed that if I have a unique compound keys for two columns, column_a and column_b, then my sql ignores this constraint if one column is null.
例如
如果 column_a=1 和 column_b = null 我可以随意插入 column_a=1 和 column_b=null
if column_a=1 and column_b = null I can insert column_a=1 and column_b=null as much as I like
如果 column_a=1 和 column_b = 2 我只能插入一次这个值.
if column_a=1 and column_b = 2 I can only insert this value once.
除了将列更改为 Not Null 并设置默认值之外，还有其他方法可以应用此约束吗?
Is there a way to apply this constraint, other than maybe changing the columns to Not Null and setting default values?
推荐答案
http://dev.mysql.com/doc/refman/5.0/en/create-index.html
"UNIQUE 索引创建了一个约束，使得索引中的所有值都必须是不同的.如果您尝试添加具有与现有行匹配的键值的新行，则会发生错误.此约束不适用于 NULL 值BDB 存储引擎除外.对于其他引擎，对于可以包含 NULL 的列，UNIQUE 索引允许多个 NULL 值."
"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. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index allows multiple NULL values for columns that can contain NULL."
所以，不，您不能让 MySQL 将 NULL 视为唯一值.我想你有几个选择:你可以按照你在问题中的建议并存储一个特殊值"而不是空值，或者你可以对表使用 BDB 引擎.不过，我不认为这种细微的行为差异值得对存储引擎做出不同寻常的选择.
So, no, you can't get MySQL to treat NULL as a unique value. I guess you have a couple of choices: you could do what you suggested in your question and store a "special value" instead of null, or you could use the BDB engine for the table. I don't think this minor difference in behaviour warrants making an unusual choice of storage engine, though.
相关文章