database 处理用户输入时 NULL 与 Empty

NULL vs Empty when dealing with user input

提问by Darryl Hein

Yes, another NULL vs empty string question.

是的，另一个 NULL 与空字符串问题。

I agree with the idea that NULL means not set, while empty string means "a value that is empty". Here's my problem: If the default value for a column is NULL, how do I allow the user to enter that NULL.

我同意 NULL 表示未设置，而空字符串表示“空值”的想法。这是我的问题：如果列的默认值为 NULL，我如何允许用户输入该 NULL。

Let's say a new user is created on a system. There is a first and last name field; last name is required while first name is not. When creating the user, the person will see 2 text inputs, one for first and one for last. The person chooses to only enter the last name. The first name is technically not set. During the insert I check the length of each field, setting all fields that are empty to NULL.

假设在系统上创建了一个新用户。有一个名字和姓氏字段；姓氏是必需的，而名字不是。创建用户时，此人将看到 2 个文本输入，一个用于第一个，一个用于最后一个。此人选择仅输入姓氏。技术上未设置名字。在插入期间，我检查每个字段的长度，将所有空字段设置为 NULL。

When looking at the database, I see that the first name is not set. The question that immediately comes to mind is that maybe they never saw the first name field (ie, because of an error). But this is not the case; they left if blank.

查看数据库时，我看到未设置名字。立即想到的问题是，也许他们从未见过名字字段（即，由于错误）。但这种情况并非如此; 如果空白，他们就离开了。

So, my question is, how do you decide when a field should be set to NULL or an empty string when receiving user input? How do you know that the user wants the field to be not set without detecting focus or if they deleted a value...or...or...?

所以，我的问题是，在接收用户输入时，您如何决定何时将字段设置为 NULL 或空字符串？你怎么知道用户希望在没有检测到焦点的情况下不设置该字段，或者他们是否删除了一个值......或......或......？

Related Question: Should I use NULL or an empty string to represent no data in table column?

相关问题：我应该使用 NULL 还是空字符串来表示表列中没有数据？

采纳答案by Kibbee

I almost never use NULL when referring to actual data. When used for foreign keys, I would say that NULL is valid, but it is almost never valid for user entered data. The one exception that would probably come up quite regularly is for dates that don't exist, such as an employee database with a "termination_date" field. In that case, all current employees should have a value of NULL in that field. As for getting them to actually enter a null value, for the values that truly require a null value, I would put a checkbox next to the input field so that the user can check it on and off to see the corresponding value to null (or in a more user friendly manner, none). When enabling the checkbox to set the field to null, the corresponding text box should be disabled, and if a null value is already associated, it should start out as disabled, and only become enabled once the user unchecks the null checkbox.

在引用实际数据时，我几乎从不使用 NULL。当用于外键时，我会说 NULL 是有效的，但对于用户输入的数据几乎从不有效。一个可能经常出现的例外是不存在的日期，例如带有“termination_date”字段的员工数据库。在这种情况下，所有当前员工在该字段中的值都应为 NULL。至于让他们实际输入空值，对于真正需要空值的值，我会在输入字段旁边放一个复选框，以便用户可以打开和关闭它以查看对应的值为空（或以更用户友好的方式，没有）。当启用复选框将字段设置为空时，应禁用相应的文本框，如果已经关联了空值，

回答by tvanfosson

If the user provides an empty string, I always treat it as null from the database perspective. In addition, I will typically trim my string inputs to remove leading/trailing spaces and then check for empty. It's a small win in the database with varchar() types and it also reduces the cases for search since I only need to check for name is nullinstead of name is null or name = ''You could also go the other way, converting null to ''. Either way, choose a way and be consistent.

如果用户提供一个空字符串，我总是从数据库的角度将其视为空字符串。此外，我通常会修剪我的字符串输入以删除前导/尾随空格，然后检查是否为空。这在具有 varchar() 类型的数据库中是一个小胜利，它也减少了搜索的情况，因为我只需要检查name is null而不是name is null or name = ''你也可以走另一条路，将 null 转换为 ''。无论哪种方式，选择一种方式并保持一致。

回答by dkretz

I'll break the pattern, and say that I would always use NULL for zero-length strings, for the following reasons.

我将打破这种模式，并说我将始终对零长度字符串使用 NULL，原因如下。

1. If you start fine-slicing the implications of blanks, then you must ensure somehow that every other developer reads and writes it the same way.

2. How do you alphabetize it?

3. Can you unambiguously determine when a user omitted entering a value, compared with intentionally leaving it blank?

4. How would you unambiguously query for the difference? Can a query screen user indicate NULL vs. blank using standard input form syntax?

5. In practice, I've never been prohibited from reading and writing data using default, unsurprising behavior using this rule. If I've needed to know the difference, I've used a boolean field (which is easier to map to unambiguous UI devices). In one case, I used a trigger to enforce True => null value, but never saw it invoked because the BR layer filtered out the condition effectively.

1. 如果您开始细化空白的含义，那么您必须以某种方式确保所有其他开发人员以相同的方式读取和写入它。

2. 你是如何按字母顺序排列的？

3. 与故意将值留空相比，您能否明确确定用户何时省略输入值？

4. 您将如何明确查询差异？查询屏幕用户能否使用标准输入表单语法指示 NULL 与空白？

5. 在实践中，我从来没有被禁止使用这条规则使用默认的、毫不奇怪的行为来读取和写入数据。如果我需要知道差异，我使用了一个布尔字段（它更容易映射到明确的 UI 设备）。在一种情况下，我使用触发器强制 True => null 值，但从未看到它被调用，因为 BR 层有效地过滤了条件。

回答by Charlie Martin

What you need to do is figure out what behavioryou want. There is no one fixed algebra of how name strings are interpreted.

你需要做的是弄清楚你想要什么行为。名称字符串的解释方式没有一个固定的代数。

Think about the state machine here: you have fields which have several states: it slounds like you're thinking about a state of "unitialized", another of "purposefully empty" and a third with some set value. ANYTHING you do that makes that assignment and is consistent with the rest of your program will be find; it sounds like the easy mapping is

想想这里的状态机：你有几个状态的字段：它听起来像是你在考虑一个“统一化”的状态，另一个“有目的地空”的状态，还有一个带有一些设定值的状态。您所做的任何使该分配并与您程序的其余部分一致的事情都会被发现；听起来简单的映射是

NULL → uninitialized
"" → purposefully unset
a name → initialized.

NULL → 未初始化的
"" → 故意取消设置
名称 → 已初始化。

回答by Pieter B

While your example is mostly for strings, I like to say that I use null for numerical and boolean fields. An accountbalance of 0 is very different to me as one that is null. Same for booleans, if people take a multiple choice test with true and false answers, it is very important to know whether someone answered true or false or didn't answer at all. Not using null in these cases would require me to have an extra table or a different setup to see whether someone answered a question. You could use for instance -1 for not filled in 0 for false and 1 for true, but then you're using a numerical field for something that's essentially a boolean.

虽然您的示例主要用于字符串，但我想说我对数字和布尔字段使用 null。accountbalance 为 0 对我来说非常不同，因为它是 null。对于布尔值也是如此，如果人们用正确和错误的答案进行多项选择测试，那么了解某人的答案是对还是错或根本没有回答是非常重要的。在这些情况下不使用 null 需要我有一个额外的表或不同的设置来查看是否有人回答了问题。例如，您可以使用 -1 表示未填充 0 表示假，1 表示真，但随后您将数字字段用于本质上是布尔值的内容。

回答by Michael Haren

I try to keep things simple. In this case, I'd make the first-name column not-nullable and allow blanks. Otherwise, you'll have three cases to deal with anywhere you refer to this field:

我尽量保持简单。在这种情况下，我会让名字列不可为空并允许空白。否则，您将在引用此字段的任何地方处理三种情况：

1. Blank first name
2. Null first name
3. Non-blank first name

1. 空白名字
2. 空名字
3. 非空名字

If you go with 'blank is null' or 'null is blank' then you're down to two cases. Two cases are better than three.

如果您选择“空白为空”或“空为空”，那么您将分为两种情况。两个案例总比三个好。

To further answer your question: the user entering data probably doesn't (and shouldn't) know anything about what a "null" is and how it compares to an "empty". This issue should be resolved cleanly and consistently in the system--not the UI.

进一步回答您的问题：输入数据的用户可能不（也不应该）知道什么是“空”以及它与“空”的比较。这个问题应该在系统中干净且一致地解决，而不是在 UI 中。

回答by Max

I've never, ever had a use for a NULL value in production code. An empty string is a fine sentinel value for a blank name field, phone number, or annual income for any application. That said, I'm sure you could find some use for it, but I just think it's overused. If I wereto use a NULL value, however, I imagine I'd use it anywhere I want to represent an empty value.

我从来没有在生产代码中使用过 NULL 值。对于任何应用程序的空白姓名字段、电话号码或年收入，空字符串是一个很好的标记值。也就是说，我相信你可以找到一些用处，但我只是认为它被过度使用了。如果我是使用NULL值，但是，我想我会使用它的任何地方我要代表空值。

回答by Titanium Creative

I have always used NULLfor uninitialized values, emptyfor purposely empty values and 0for off indicators.

我一直用于NULL未初始化的值、empty有意为空的值和0关闭指标。

By doing this all the time, it is there even if I am not using it, but I don't have to do anything different if I need that distinction.

通过一直这样做，即使我不使用它，它也存在，但如果我需要这种区别，我不必做任何不同的事情。

I am usually testing for empty(), but sometimes I check for isset()which evaluates falseon NULL. This is useful for reminders to answer certain questions. If it is empty, falseor 0then the question is answered.

我通常测试的empty()，但有时我检查isset()，其评价false上NULL。这对于提醒回答某些问题很有用。如果是empty，false还是0那么问题的答案。