MySql 复合键和空值
我注意到，如果我有两列（column_a 和 column_b）的唯一复合键，那么如果其中一列为空，我的 sql 将忽略此约束。
例如，
如果column_a=1 且column_b = null 我可以随意插入column_a=1 和column_b=null
如果column_a=1 且column_b = 2 我只能插入该值一次。
除了将列更改为“非空”并设置默认值之外，还有其他方法可以应用此约束吗？
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.
E.g.
if column_a=1 and column_b = null I can insert column_a=1 and column_b=null as much as I like
if column_a=1 and column_b = 2 I can only insert this value once.
Is there a way to apply this constraint, other than maybe changing the columns to Not Null and setting default values?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
http://dev.mysql.com/doc/refman/5.0/en/create- index.html
“UNIQUE 索引创建一个约束，使得索引中的所有值都必须不同。如果您尝试添加具有与现有行匹配的键值的新行，则会发生错误。此约束不适用于除 BDB 存储引擎之外的 NULL 值，对于其他引擎，UNIQUE 索引允许可以包含 NULL 的列有多个 NULL 值。”
所以，不，你不能让 MySQL 将 NULL 视为唯一值。我想您有几个选择：您可以按照您在问题中建议的操作并存储“特殊值”而不是 null，或者您可以对表使用 BDB 引擎。不过，我认为这种行为上的微小差异并不值得做出不寻常的存储引擎选择。
http://dev.mysql.com/doc/refman/5.0/en/create-index.html
"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."
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.
我通过在
COALESCE(column_b, 0)同一张表上创建一个虚拟（存储）列来解决这个问题。然后，我根据该列（和第二列）创建了唯一的复合索引。效果很好。当然，这在 2010 年可能是不可能的:)
I worked around this issue by creating a virtual (stored) column on the same table that was
COALESCE(column_b, 0). I then made my unique composite index based upon that column (and the second column) instead. Works very well.Of course this was probably not possible back in 2010 :)