SQL 为什么 Oracle 9i 将空字符串视为 NULL？

Why does Oracle 9i treat an empty string as NULL?

提问by Chris R

I know that it doesconsider '?' as NULL, but that doesn't do much to tell me whythis is the case. As I understand the SQL specifications, '?' is not the same as NULL-- one is a valid datum, and the other is indicating the absence of that same information.

我知道它确实考虑了“？” as NULL，但这并不能告诉我为什么会这样。据我了解 SQL 规范，'?' 与NULL-- 一个是有效数据不同，另一个表示没有相同的信息。

Feel free to speculate, but please indicate if that's the case. If there's anyone from Oracle who can comment on it, that'd be fantastic!

随意推测，但请指出是否是这种情况。如果有来自 Oracle 的任何人可以对此发表评论，那就太棒了！

采纳答案by Justin Cave

I believe the answer is that Oracle is very, very old.

我相信答案是 Oracle 非常非常古老。

Back in the olden days before there was a SQL standard, Oracle made the design decision that empty strings in VARCHAR/VARCHAR2columns were NULLand that there was only one sense of NULL (there are relational theorists that would differentiate between data that has never been prompted for, data where the answer exists but is not known by the user, data where there is no answer, etc. all of which constitute some sense of NULL).

早在 SQL 标准出现之前，Oracle 就做出了这样的设计决定，即VARCHAR/VARCHAR2列中的空字符串是空字符串，NULL并且只有一种 NULL 含义（有些关系理论家会区分从未被提示输入的数据，答案存在但用户不知道的数据，没有答案的数据等，所有这些都构成了某种感觉NULL）。

By the time that the SQL standard came around and agreed that NULLand the empty string were distinct entities, there were already Oracle users that had code that assumed the two were equivalent. So Oracle was basically left with the options of breaking existing code, violating the SQL standard, or introducing some sort of initialization parameter that would change the functionality of potentially large number of queries. Violating the SQL standard (IMHO) was the least disruptive of these three options.

当 SQL 标准出现并同意NULL空字符串是不同的实体时，已经有 Oracle 用户的代码假定这两者是等效的。因此，Oracle 基本上只能选择破坏现有代码、违反 SQL 标准或引入某种初始化参数来改变潜在的大量查询的功能。违反 SQL 标准 (恕我直言) 是这三个选项中破坏性最小的。

Oracle has left open the possibility that the VARCHARdata type would change in a future release to adhere to the SQL standard (which is why everyone uses VARCHAR2in Oracle since that data type's behavior is guaranteed to remain the same going forward).

Oracle 保留了VARCHAR数据类型在未来版本中更改以符合 SQL 标准的可能性（这就是每个人都VARCHAR2在 Oracle 中使用的原因，因为该数据类型的行为保证在未来保持不变）。

回答by Brian

Tom KyteVP of Oracle:

甲骨文副总裁Tom Kyte：

A ZERO length varchar is treated as NULL.

'' is not treated as NULL.

'' when assigned to a char(1) becomes ' ' (char types are blank padded strings).

'' when assigned to a varchar2(1) becomes '' which is a zero length string and a zero length string is NULL in Oracle (it is no long '')

零长度 varchar 被视为 NULL。

'' 不被视为 NULL。

'' 分配给 char(1) 时变为 ' '（char 类型是空白填充字符串）。

'' 当分配给 varchar2(1) 时变为 ''，这是一个零长度字符串，零长度字符串在 Oracle 中为 NULL（它不是长 ''）

回答by user67897

I suspect this makes a lot more sense if you think of Oracle the way earlier developers probably did -- as a glorified backend for a data entry system. Every field in the database corresponded to a field in a form that a data entry operator saw on his screen. If the operator didn't type anything into a field, whether that's "birthdate" or "address" then the data for that field is "unknown". There's no way for an operator to indicate that someone's address is really an empty string, and that doesn't really make much sense anyways.

我怀疑，如果您以早期开发人员可能所做的方式考虑 Oracle——作为数据输入系统的美化后端，这会更有意义。数据库中的每个字段都对应于数据输入操作员在其屏幕上看到的表单中的一个字段。如果操作员没有在字段中输入任何内容，无论是“生日”还是“地址”，则该字段的数据为“未知”。操作员没有办法表明某人的地址实际上是一个空字符串，无论如何这也没有多大意义。

回答by Walter Mitty

Oracle documentation alerts developers to this problem, going back at least as far as version 7.

Oracle 文档提醒开发人员注意这个问题，至少可以追溯到版本 7。

Oracle chose to represent NULLS by the "impossible value" technique. For example, a NULL in a numeric location will be stored as "minus zero", an impossible value. Any minus zeroes that result from computations will be converted to positive zero before being stored.

Oracle 选择通过“不可能的值”技术来表示 NULLS。例如，数字位置中的 NULL 将存储为“负零”，这是一个不可能的值。在存储之前，计算产生的任何负零都将转换为正零。

Oracle also chose, erroneously, to consider the VARCHAR string of length zero (the empty string) to be an impossible value, and a suitable choice for representing NULL. It turns out that the empty string is far from an impossible value. It's even the identity under the operation of string concatenation!

Oracle 还错误地选择将长度为零的 VARCHAR 字符串（空字符串）视为不可能的值，并且是表示 NULL 的合适选择。事实证明，空字符串远非不可能的值。甚至是字符串拼接运算下的身份！

Oracle documentation warns database designers and developers that some future version of Oracle might break this association between the empty string and NULL, and break any code that depends on that association.

Oracle 文档警告数据库设计人员和开发人员，某些未来版本的 Oracle 可能会破坏空字符串和 NULL 之间的这种关联，并破坏依赖于该关联的任何代码。

There are techniques to flag NULLS other than impossible values, but Oracle didn't use them.

除了不可能的值之外，还有其他标记 NULL 的技术，但 Oracle 没有使用它们。

(I'm using the word "location" above to mean the intersection of a row and a column.)

（我使用上面的“位置”一词来表示行和列的交叉点。）

回答by Alex Kreutznaer

Empty string is the same as NULL simply because its the "lesser evil" when compared to the situation when the two (empty string and null) are not the same.

空字符串与 NULL 相同，只是因为与两者（空字符串和 null）不相同的情况相比，它是“较小的邪恶”。

In languages where NULL and empty String are not the same, one has to always check both conditions.

在 NULL 和空字符串不相同的语言中，必须始终检查这两个条件。

回答by Sorter

According to official 11g docs

根据官方 11g 文档

Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.

Oracle 数据库当前将长度为零的字符值视为空值。但是，这在未来版本中可能不再适用，Oracle 建议您不要将空字符串视为空字符串。

Possible reasons

可能的原因

1. val IS NOT NULLis more readable than val != ''
2. No need to check both conditions val != '' and val IS NOT NULL

1. val IS NOT NULL比 val != ''
2. 无需检查这两个条件 val != '' and val IS NOT NULL

回答by zloctb

Example from book

书中的例子

   set serveroutput on;   
    DECLARE
    empty_varchar2 VARCHAR2(10) := '';
    empty_char CHAR(10) := '';
    BEGIN
    IF empty_varchar2 IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('empty_varchar2 is NULL');
    END IF;


    IF '' IS NULL THEN
    DBMS_OUTPUT.PUT_LINE(''''' is NULL');
    END IF;

    IF empty_char IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('empty_char is NULL');
    ELSIF empty_char IS NOT NULL THEN
    DBMS_OUTPUT.PUT_LINE('empty_char is NOT NULL');
    END IF;

    END;

回答by erikkallen

Because not treating it as NULL isn't particularly helpful, either.

因为不将其视为 NULL 也不是特别有用。

If you make a mistake in this area on Oracle, you usually notice right away. In SQL server, however, it will appear to work, and the problem only appears when someone enters an empty string instead of NULL (perhaps from a .net client library, where null is different from "", but you usually treat them the same).

如果您在 Oracle 上在这方面犯了错误，您通常会立即注意到。但是，在 SQL Server 中，它似乎可以工作，并且仅当有人输入空字符串而不是 NULL 时才会出现问题（可能来自 .net 客户端库，其中 null 与“”不同，但您通常将它们视为相同）。

I'm not saying Oracle is right, but it seems to me that both ways are approximately equally bad.

我并不是说 Oracle 是对的，但在我看来，这两种方式大致同样糟糕。

回答by Michael T. Gunderson

First of all, null and null string were not always treated as the same by Oracle. A null string is, by definition, a string containing no characters. This is not at all the same as a null. NULL is, by definition, the absence of data.

首先，Oracle 并不总是将 null 和 null string 视为相同。根据定义，空字符串是不包含字符的字符串。这与空值完全不同。根据定义，NULL 是没有数据。

Five or six years or so ago, null string was treated differently from null by Oracle. While, like null, null string was equal to everything and different from everything (which I think is fine for null, but totally WRONG for null string), at least length(null string) would return 0, as it should since null string is a string of zero length.

五六年前，Oracle 对 null 字符串的处理方式与 null 不同。虽然，像 null 一样，空字符串等于一切，但不同于一切（我认为这对 null 来说很好，但对于空字符串完全错误），至少长度（空字符串）会返回 0，因为它应该返回 0，因为空字符串是零长度的字符串。

Currently in Oracle, length(null) returns null which I guess is O.K., but length(null string) also returns null which is totally WRONG.

目前在 Oracle 中，length(null) 返回 null，我猜是可以的，但 length(null string) 也返回 null，这是完全错误的。

I do not understand why they decided to start treating these 2 distinct "values" the same. They mean different things and the programmer should have the capability of acting on each in different ways. The fact that they have changed their methodology tells me that they really don't have a clue as to how these values should be treated.

我不明白为什么他们决定开始将这两个不同的“价值观”一视同仁。它们意味着不同的东西，程序员应该有能力以不同的方式对每个东西采取行动。他们改变了方法论的事实告诉我，他们真的不知道应该如何对待这些价值观。

回答by Cade Roux

Indeed, I have had nothing but difficulties in dealing with Oracle, including invalid datetime values (cannot be printed, converted or anything, just looked at with the DUMP() function) which are allowedto be inserted into the database, apparently through some buggy version of the client as a binary column! So much for protecting database integrity!

事实上，我在处理 Oracle 时遇到了一些困难，包括无效的日期时间值（无法打印、转换或任何东西，只能用 DUMP() 函数查看），这些值被允许插入到数据库中，显然是通过一些错误客户端版本为二进制列！保护数据库完整性就这么多！

Oracle handling of NULLs links:

Oracle 对 NULL 链接的处理：

http://digitalbush.com/2007/10/27/oracle-9i-null-behavior/

http://digitalbush.com/2007/10/27/oracle-9i-null-behavior/

http://jeffkemponoracle.com/2006/02/empty-string-andor-null.html

http://jeffkemponoracle.com/2006/02/empty-string-andor-null.html