mySQL 中的外键和 NULL
我可以在值表（值）中有一列作为knownValues表的外键引用，并在需要时将其设置为NULL，如示例所示：
表：值
product type value freevalue
0 1 NULL 100
1 2 NULL 25
3 3 1 NULL
表：类型
id name prefix
0 length cm
1 weight kg
2 fruit NULL
表：knownValues
id Type name
0 2 banana
注意：表中的类型值 & knownValues 当然会被引用到 types 表中。
Can I have a column in my values table (value) referenced as a foreign key to knownValues table, and let it be NULL whenever needed, like in the example:
Table: values
product type value freevalue
0 1 NULL 100
1 2 NULL 25
3 3 1 NULL
Table: types
id name prefix
0 length cm
1 weight kg
2 fruit NULL
Table: knownValues
id Type name
0 2 banana
Note: The types in the table values & knownValues are of course referenced into the types table.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
外键中的 NULL 是完全可以接受的。处理外键中的 NULL 很棘手，但这并不意味着您将此类列更改为 NOT NULL 并在引用表中插入虚拟（“N/A”、“未知”、“无值”等）记录。
在外键中使用 NULL 通常需要使用 LEFT/RIGHT JOIN 而不是 INNER JOIN。
NULLs in foreign keys are perfectly acceptable. Dealing with NULLs in foreign keys is tricky but that does not mean that you change such columns to NOT NULL and insert dummy ("N/A", "Unknown", "No Value" etc) records in your reference tables.
Using NULLs in foreign keys often requires you to use LEFT/RIGHT JOIN instead of INNER JOIN.
虽然您可以使外键列可为空，但我建议通常最好设计没有可为空外键的表。空值总是会导致某些歧义和不正确的结果，但如果所讨论的列预计受到某些约束，那么这将是一个双重问题。
Although you can make foreign key columns nullable I would suggest that it's generally better to design tables without nullable foreign keys. Nulls invariably lead to certain ambiguities and incorrect results but that's doubly a problem if the columns in question are expected to be subject to some constraint.
这是一个1到0到多的关系。我已经在 SQL Server 中使用过很多次了。我相信使用 MySQL 也可以做到这一点。
由于与数据聚合相关的问题，我更喜欢避免数据库中出现 NULL，因此，根据我的设计，我在查找表中放置了 UNKNOWN 行。
This is a 1 to zero-to-many relationship. I have used this many times with SQL Server. I believe it is possible to do this with MySQL as well.
I prefer to avoid NULLs in my databases because of issues related to data aggregation so, depending on my design, I put an UNKNOWN row in the lookup table.
是的，外键约束列中很可能有 NULL。我刚刚尝试过。请记住，如果您不使用 InnoDB 存储引擎，那么您的外键约束无论如何都会被忽略。
Yes it is quite possible to have a NULL in your foreign-key-constrained column. I just tried it. Bear in mind that if you are not using the InnoDB storage engine, your foreign key constraints will be ignored anyway.
当然，外键有可能有NULL值，但为此你不必担心这一点，我希望你可以使用InnoDB作为数据库引擎来管理Key约束。对于这种情况，我建议使用 Left Join 或 Right Join 从数据库获取行，并且可以使用 Group By 来避免重复。请不要使用内连接。
Of course, there is a possibilities ti have a NULL values in foreign key, but for that you don't get worry about this, I hope you may used InnoDB as database engine to manage the Key constraints. For this case i suggest to use Left Join or Right Join to get rows from DB and Group By can be used for avoid duplication. Please do not use Inner Join.