MySql 复合键和空值 [英] MySql compound keys and null values

问题描述

我注意到，如果我有两列的唯一复合键，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.

这篇关于MySql 复合键和空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！