.NET：数据库字段中允许 NULL 吗？
我的任务是重构 SQLServer DB...很多表和列“允许 NULL”，这是好的做法吗...
我似乎记得 CSLA.NET 的作者说这是非常糟糕的做法允许数据库中存在空值...
如果是这种情况，我的选择是什么？
从所有列中删除所有“ALLOW NULL”...并且在数字列中使用值 -1 例如？
我真的很感激任何人提供的任何意见。
我目前正在使用我的数据库中的模型（来自实体框架），并且“ALLOW NULLS”为空的数据库列...并且某些存储过程要求我有一个默认值...即 BOOLEAN 需要 FALSE 作为默认值...但它是空的..
好吧，我不想偏离我原来的问题，从我收集到的信息来看，允许空值是一件坏事...那么我该如何解决这个问题？
任何帮助真的很感激
I have the task of re-factoring an SQLServer DB.... A lot of the tables and columns "ALLOW NULLS", Is this good practice...
I seem to remember the authour of CSLA.NET saying it was really bad practice to allow nulls in a DB...
If this is the case, what are my alternatives?
Remove all "ALLOW NULLS" from all columns.... and in numeric columns use a value of -1 for example??
I would really appreciate any input anyone has.
I am currently using a Model (from entity framework) from my DB and the db columns that "ALLOW NULLS" are null ... and some of the stored procedures require that i have a default value... i.e. BOOLEAN require FALSE as default ... but it is null..
Well i don't want to stray from my original question, ALLOW NULLS are a bad thing from what i can gather .... so how do i fix this ?
Any help really appreciated
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
毫无疑问，应尽可能避免 NULL，因为它们可能会导致索引和查询语法出现一些问题。
由于 NULL 可能会带来问题，因此有人呼吁停止使用它们。然而，像大多数此类运动一样，它已经失控，以至于一些人狂热地坚持认为数据库中永远不应该使用 NULL。在我发现它有点过分热心之前，我在这个营地待了很多年。
答案介于两者之间。应尽可能避免 NULL，但存储它们确实有正当的业务原因。
很多时候你需要存储一个可选的数值，很多人会告诉你只存储一个零来表示“无值”，但这是存储一个真正意味着其他东西的神奇值的更糟糕的反模式。如果您不能对一个字段使用零怎么办，因为零也被认为是一个有意义的值，或者因为该值被用作除数的倍数，所以您需要使用其他一些神奇的值（-1？ ) 在这种情况下，对于该字段，现在您遇到了更糟糕的问题，因为所有可选数字字段现在的行为都不同。奥耶。
对于可空字段来说，日期是更具吸引力的候选者。人们在 .NET 中使用的标准“无值”是默认未分配的 DateTime 值，即 DateTime.MinValue，或更具体地说是 0001 年 1 月 1 日。但是，您无法将这个神奇值写入 SQL 数据库，因为默认的最小值为SQL Server DATETIME 字段是 1973 年 1 月 1 日。现在，您必须有一些东西来检查您是否在将这些值写入数据库和从数据库中读取时正确转换这些值，并且必须在所有检查的地方都进行防御性编码检查您的日期字段是否小于 SqlDateTime.MinValue，而不是仅检查它们是否等于 DateTime.MinValue。双奥耶。
我的偏好是按照真实的值进行处理，而不是构建大量人为的结构来隐藏该领域的真正含义和用法。如果某个字段很可能没有值，请使其可为空，并在您的应用程序对象中也使其可为空（如果您的语言支持这样的事情）。然后，每当你使用该字段时，你都需要考虑在它为 NULL 的情况下应该做什么，但这实际上是一件好事。一般来说，我反对让开发人员在不必要的代码复杂性上浪费脑力，但这是因为它分散了人们对正在解决的真正业务问题的注意力；然而，在这种情况下，缺乏价值是真正的业务问题的一部分，必须仔细考虑。如果您只是默认这些值，那么编写公式或算法的开发人员将不太可能考虑这些值丢失的边缘条件，甚至可能当时没有意识到这些值可能丢失。
There is no question that NULLs should be avoided when possible, because of the problems they can introduce several problems with indexing and query syntax.
Due to the problems that NULLs can introduce, there has been a push to stop using them. However, like most such movements, it has gotten out of control, to the point that some people fanatically insist that NULLs should never be used in the database. I was in this camp for a lot of years, before I found it to be a little overzealous.
The answer is somewhere in between. NULLs should be avoided whenever possible, but there are indeed valid business reasons for storing them.
Often times you need to store an optional numeric value, and many people will tell you to just store a zero to indicate "no value", but this is an even worse antipattern of storing an magical value that really means something else. What if then you can't use zero for one field, because zero is considered a meaningful value as well, or because this value is being used as the multiple of a divisor, so you need to use some other magical value (-1?) for that field in just this case, and now you have a worse problem, because all of your optional numeric fields are now behaving differently. Oye.
Dates are an even more compelling candidate for nullable fields. The standard "no value" that people use in .NET is the default unassigned DateTime value, which is DateTime.MinValue, or more specifically January 1, 0001. However, you cannot write this magic value into SQL database because the default minimum value for a SQL Server DATETIME field is January 1, 1973. You now have to have something that checks that you are translating those values properly as they are written to and read from the database, and you have to have defensive coding all over the place that checks for whether your date fields are less than SqlDateTime.MinValue, intead of just checking whether they are equal to DateTime.MinValue. Double Oye.
My preference is to deal with the values as they truly are, and not to build a lot of artifical constructs to hide the true meaning and usage of the field. If a field may very well not have a value, make it nullable, and make it nullable in your application objects as well (if you language supports such a thing). Then, anytime you are using that field, you are required to consider what should be done in the case where it is NULL, but that is actually a good thing. Generally I an opposed to making developers waste braincycles on unnecessary code complexity, but that is because it steals focus away from the true business problem being solved; however, in this case, the lack of a value IS part of the true business problem and must be thought through. If you are just defaulting these value, then the developer writing a formula or algorithm will be less likely to think through those edge conditions where the values are missing, and may not even realize at the time that it is a possibility that those values are missing.
有时 Null 是有效的列值，但我建议更好的做法是添加默认值并使列不为 Null。
如果您要考虑现有数据，则必须进行如下更新：
...如果您想对现有数据强加“非空”。
如果没有合理的默认值，那么 null 可以是一个完全有效的列值 - 但通常有一个不错的默认值可用。
Null is sometimes a valid column value, but I would suggest that a better practice would be to instead add default values and make the column not null.
If you have existing data to consider you would have to do an update like:
... if you want to impose 'not null' on existing data.
If there is no reasonable default value then null can be a perfectly valid column value - but there is quite often a decent default available.
使用空值是轻微的非规范化可以带来很大的性能优势的情况。在现实世界中，NULL 值是表示某个值尚未被使用的完全有效的方式。
记录了。这对于布尔 (BIT) 值和可选数值尤其重要。
纯粹主义者会让您创建一个带有左连接的新表来记录该值。现实主义者会改变现有的表并添加一个空值。
肯定存在与 null 相关的情况。出于完全相同的原因，将 Nullable 泛型添加到值类型的 .net 框架中。
Using a null value is a case where slight denormalisation can bring a big performance benefit. In the real world a NULL value is a perfectly valid way of saying that a value hasnt been
recorded. This is especially relevant for boolean (BIT) values and optional numeric values.
The purists will have you create a new table with a left join to record this value. The realist will alter the existing table and add a null value.
There is definitely cases where nulls are relevant. The Nullable generic was added to .net framework for value types for exactly the same reason.
NULL 是人类脸上的污点，应该从存在中根除。然而不幸的是，当前的系统和语言（尤其是 SQL）无法胜任对当前思维进行如此彻底改革的任务。事实上，SQL 是这场悲剧的主要肇事者。
因此，在现实世界中，我给出的建议是尽可能避免 NULL，但要接受现实，有时没有简单的选择。
NULLs are a blight on the face of humanity that should be eradicated from existence. Unfortunately, however, current systems and languages (especially SQL) aren't up to the task of accommodating such a radical overhaul of current thinking. In fact, SQL is a major perpetrator of this travesty.
In the real world, therefore, the advice I would give is to avoid NULLs as much as you can, but accept the reality that sometimes there is no easy alternative.