Varchar 列:是否可以为 Null [英] Varchar columns: Nullable or not

问题描述

我们组织中的数据库开发标准规定 varchar 字段不应允许空值.它们应该有一个空字符串 ("") 的默认值.我知道这使查询和连接更容易，但是今天，我的一位同事问我为什么该标准仅适用于 varchar 类型而不适用于其他数据类型(int、datetime 等).我想知道其他人是否认为这是一个有效的、可辩护的标准，或者是否应将 varchar 视为与其他数据类型的字段相同?

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:

我相信空字符串和空值虽然在技术上不同，但在概念上是相同的.空的零长度字符串是不存在的字符串.它没有任何价值.但是，数值 0 与 NULL 不同.

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.

例如，如果名为 OutwardBalance 的字段的值为 0，则表示剩余 0.00 美元.但是，如果同一字段为 NULL，则表示该值未知.另一方面，值为"的名为 CustomerName 的字段与值为 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.

我在某处读到，空字符串与 NULL 的类比是空白 CD 与无 CD 的类比.然而，我认为这是一个错误的类比，因为一张空白 CD 仍然物理存在并且仍然具有没有写入任何有意义数据的物理数据空间.基本上，我相信一张空白 CD 相当于一串空格 (" ")，而不是一个空字符串.因此，我认为一串空格是一个与 NULL 分开的实际值，但一个空字符串在概念上相当于 NULL 的值缺失.

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.

如果我对可变长度字符串的看法有效，请告诉我，如果无效，请赐教.我已经阅读了一些关于这个主题的博客/论点，但仍然没有看到 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.

推荐答案

这几乎可以归结为 - 在您的应用程序中，对于特定的字符串，有空字符串和没有字符串有区别吗?

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.

如果您发现存在差异，则 null 具有不同的含义，应该被允许.

If you find that there is a difference, then the null has a distinct meaning and should be allowed.

根据我的经验，null 通常被建模为表示 unknown.

In my experience, null is normally modelled to mean unknown.

这是一个更具体的例子 - 人的中间名:

Here is a more concrete example - middle names of people:

• 如果您知道中间名，则填充该值
• 如果您知道此人没有中间名，则使用空字符串 ('')
• 如果你不知道一个人是否有中间名，null 可能更合适

• If you know the middle name, then the value is populated
• If you know that the person has no middle name, then use an empty string ('')
• If you don't know whether a person has a middle name, a null may be more appropriate

同样，如果您的应用程序对待没有中间名的人和中间名未知的人一样，那么对两者使用空字符串是有意义的(即使这确实意味着丢失一些信息).

Again, 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).

这篇关于Varchar 列:是否可以为 Null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！