問題描述
我注意到，如果我有兩列的唯一復合鍵，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 復合鍵和空值的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！