I have a table containing a video_id field which is set to unique accept NULL values. However it will not accept two null values. Table type InnoDB or MYISAM will not work.
我有一个包含video_id字段的表,该字段设置为唯一的接受NULL值。但是它不会接受两个空值。表类型InnoDB或MYISAM将无法正常工作。
This produces a duplicate key error on video _id (Null value):
这会在视频_id(Null值)上产生重复键错误:
INSERT INTO dances (name, video_id, level) VALUES ('abc', '', 'beg');
If I read the Mysql rules correctly it should do so. Can anyone suggest where to go next?
如果我正确阅读了Mysql规则,它应该这样做。谁能建议下一步去哪儿?
3 个解决方案
#1
2
Yes, MySQL allows multiple NULLs in a column with a unique constraint. See this Stack Overflow question.
是的,MySQL允许具有唯一约束的列中的多个NULL。请参阅此Stack Overflow问题。
An empty string '' is not the same as NULL.
空字符串''与NULL不同。
Use this instead:
改为使用它:
INSERT INTO dances (name, video_id, level) VALUES ('abc', NULL, 'beg');
#3
0
And why do you need multiple rows of your table to have a null video_id?
为什么你需要多行的表来获得null video_id?
From a DB design perspective, it sounds like the object you are modelling with the table is too "big" and needs to be broken up into a main table and detail tables.
从数据库设计的角度来看,它听起来像您使用表建模的对象太“大”,需要分解为主表和详细信息表。
Your options are (A) remove the UNIQUE constraint, or (B) create a separate table that contains object_id, video_id pairs (where object_id is whatever the primary key is on your main table).
您的选项是(A)删除UNIQUE约束,或(B)创建一个包含object_id,video_id对的单独表(其中object_id是主表上的主键)。
#1
2
Yes, MySQL allows multiple NULLs in a column with a unique constraint. See this Stack Overflow question.
是的,MySQL允许具有唯一约束的列中的多个NULL。请参阅此Stack Overflow问题。
An empty string '' is not the same as NULL.
空字符串''与NULL不同。
Use this instead:
改为使用它:
INSERT INTO dances (name, video_id, level) VALUES ('abc', NULL, 'beg');
#2
#3
0
And why do you need multiple rows of your table to have a null video_id?
为什么你需要多行的表来获得null video_id?
From a DB design perspective, it sounds like the object you are modelling with the table is too "big" and needs to be broken up into a main table and detail tables.
从数据库设计的角度来看,它听起来像您使用表建模的对象太“大”,需要分解为主表和详细信息表。
Your options are (A) remove the UNIQUE constraint, or (B) create a separate table that contains object_id, video_id pairs (where object_id is whatever the primary key is on your main table).
您的选项是(A)删除UNIQUE约束,或(B)创建一个包含object_id,video_id对的单独表(其中object_id是主表上的主键)。