Varchar 列：可空或不可空
我们组织的数据库开发标准规定 varchar 字段不应允许空值。它们的默认值应该是空字符串 ("")。我知道这使得查询和连接变得更容易，但今天，我的一位同事问我为什么该标准只适用于 varchar 类型，而不适用于其他数据类型（int、datetime 等）。我想知道其他人是否认为这是一个有效的、可辩护的标准，或者是否应该将 varchar 视为与其他数据类型的字段相同？
我相信这个标准是有效的，原因如下：
我相信空字符串和空值虽然在技术上不同，但在概念上是相同的。空的零长度字符串是不存在的字符串。它没有任何价值。但是，数值 0 与 NULL 不同。
例如，如果名为 OutstandingBalance 的字段的值为 0，则表示剩余 0.00 美元。但是，如果同一字段为 NULL，则意味着该值未知。另一方面，值为“”的名为 CustomerName 的字段与值为 NULL 的情况基本相同，因为两者都表示该名称不存在。
我在某处读到，空字符串与 NULL 的类比就像一张空白 CD 与无 CD 的比较。然而，我认为这是一个错误的类比，因为空白 CD 在物理上仍然存在，并且仍然具有没有写入任何有意义的数据的物理数据空间。基本上，我相信空白 CD 相当于一串空格 (" ")，而不是空字符串。因此，我相信空格字符串是与 NULL 分开的实际值，但空字符串在概念上等同于 NULL 的值的缺失。
请让我知道我对可变长度字符串的信念是否有效，如果无效，请启发我。我已经阅读了一些关于这个主题的博客/论点，但仍然没有看到 NULL 和空字符串之间真正的概念差异。
The database development standards in our organization state the varchar fields should not allow null values. They should have a default value of an empty string (""). I know this makes querying and concatenation easier, but today, one of my coworkers questioned me about why that standard only existed for varchar types an not other datatypes (int, datetime, etc). I would like to know if others consider this to be a valid, defensible standard, or if varchar should be treated the same as fields of other data types?
I believe this standard is valid for the following reason:
I believe that an empty string and null values, though technically different, are conceptually the same. An empty, zero length string is a string that does not exist. It has no value. However, a numeric value of 0 is not the same as NULL.
For example, if a field called OutstandingBalance has a value of 0, it means there are $0.00 remaining. However, if the same field is NULL, that means the value is unknown. On the other hand, a field called CustomerName with a value of "" is basically the same as a value of NULL because both represent the non-existence of the name.
I read somewhere that an analogy for an empty string vs. NULL is that of a blank CD vs. no CD. However, I believe this to be a false analogy because a blank CD still phyically exists and still has physical data space that does not have any meaningful data written to it. Basically, I believe a blank CD is the equivalent of a string of blank spaces (" "), not an empty string. Therefore, I believe a string of blank spaces to be an actual value separate from NULL, but an empty string to be the absense of value conceptually equivalent to NULL.
Please let me know if my beliefs regarding variable length strings are valid, or please enlighten me if they are not. I have read several blogs / arguments regarding this subject, but still do not see a true conceptual difference between NULLs and empty strings.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
它几乎可以归结为这一点 - 在您的应用程序中，对于特定字符串，有空字符串和根本没有字符串之间有区别吗？
如果没有区别，那么你遵循的标准就可以了。
如果您发现存在差异，则
null具有不同的含义，应该被允许。根据我的经验，
null通常被建模为表示未知。这是一个更具体的示例 - 人的中间名：
null可能更合适同样，如果您的应用程序对没有中间名的人和中间名未知的人一视同仁，那么对两者使用空字符串是有意义的（即使这确实意味着丢失一些信息）。
It pretty much boils down to this - in your application, for a specific string, is there a difference between having an empty string to having no string at all?
If there is no distinction, then the standard your are following is fine.
If you find that there is a difference, then the
nullhas a distinct meaning and should be allowed.In my experience,
nullis normally modelled to meanunknown.Here is a more concrete example - middle names of people:
nullmay be more appropriateAgain, if your application treats people with no middle name and those where this is unknown identically, then using an empty string for both makes sense (even if it does mean losing some information).
不，null 是一个非常独特的值。例如 - 只是十多个中的一个 - null 可能意味着“我们根本没有值”，而空字符串意味着“我们有一个答案，但什么也没有”。这会很有用，例如，作为一个问题的答案 - 从来没有得到答案或答案什么也没有......
那里有一个很棒的白皮书，标题为“NULL 的 18 种含义”之类的东西 - 我不知道回想一下整数是什么！无论如何，那篇论文至少从 20 世纪 90 年代初就已经存在了，如果你能找到它，那就太棒了 - 我还没有进行过网络搜索。
空值的真正问题是它们可能“错误地”改变返回的行。例如，如果您说
select strcol1, datecol2, someint3 from fubar where ...
如果 strcol1 恰好为空，您将不会获得该行的值，因为存在隐含的“其中 strcol1 不为空” - 因此整个行可能会丢失。
并非所有 RDBMS 系统都如此，但对于某些 RDBMS 系统来说已经有一段时间了，因此如果您希望代码能够从一个 RDBMS 移动到另一个 RDBMS，则在处理 null 时应该非常小心。
另一点：Oracle - 或者至少是 Oracle 的某些版本 - 会默默地将空字符串转换为 null！这确实令人愤慨，但不知何故，他们已经“永远”在生产中了。当心！我的解决方案是使用其他字符串来表示“空字符串”，通常是单个空格字符。
No, null is a very distinctive value. For example - just one of over a dozen - null may mean "we don't have a value at all", while the empty string means "we have an answer and it is nothing." This would be useful, for example, as an answer to a question - never got an answer or the answer was nothing...
There's a GREAT whitepaper floating around out there entitled something like "the 18 meanings of NULL" - I don't recall just what the integer is! ANyway, that paper has been out there since at least the early 1990s and is truly fantastic if you can find it - I have not done a web search.
The real issue with nulls is that they can "errantly" alter what rows are returned. For example, if you say
select strcol1, datecol2, someint3 from fubar where ...
If strcol1 happens to be null, you will NOT get a value back for that row because there's an implied "where strcol1 is not null" - thus a whole row can be missing.
This is not true for all RDBMS systems, but has been true for some for quite a while so if you want your code to be able to be moved from one RDBMS to another, you should be VERY careful when handling nulls.
Another point: Oracle - or at least some versions of Oracle - will silently convert empty strings to null! This is truly outrageous, but somehow, they've had this in production "forever." Watch out! My solution is to use some other string to mean "the empty string", most often a single space character.