string Oracle 中的 null 与空字符串

null vs empty string in Oracle

提问by Tiny

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

可能的重复：
为什么 Oracle 9i 将空字符串视为 NULL？

I have a table in Oracle 10g named TEMP_TABLEwith only two columns - idand descriptionjust for the sake of demonstration.

我在Oracle中的表10G命名的TEMP_TABLE，只有两列-id和description只为示范的缘故。

The column idis a sequence generated primary key of type NUMBER(35, 0) not nulland the column DESCRIPTIONis a type of VARCHAR2(4000) not null.

列id是序列生成的类型的主键，NUMBER(35, 0) not null列DESCRIPTION是VARCHAR2(4000) not null.

The basic table structure in this case would look something like the following.

这种情况下的基本表结构类似于以下内容。

+--------------+-----------+---------------+
|Name          | Null?     | Type          |
+--------------+-----------+---------------+
|ID            | NOT NULL  | NUMBER(35)    |
|DESCRIPTION   | NOT NULL  | VARCHAR2(4000)|
+--------------+-----------+---------------+

After creating this table, I'm trying to insert the following INSERTcommands alternatively.

创建此表后，我尝试INSERT交替插入以下命令。

INSERT INTO temp_table (id, description) VALUES (1, null); ->unsuccessful
INSERT INTO temp_table (id, description) VALUES (2, '');   ->unsuccessful

Both of them are unsuccessful as obvious because the not nullconstraint is enforced on the DESCRIPTIONcolumn.

很明显，它们都没有成功，因为not null约束是在DESCRIPTION列上强制执行的。

In both of the cases, Oracle complains

在这两种情况下，Oracle 都抱怨

ORA-01400: cannot insert NULL into ("WAGAFASHIONDB"."TEMP_TABLE"."DESCRIPTION")

An empty string is treated as a NULLvalue in Oracle.

空字符串NULL在 Oracle 中被视为一个值。

---

---

If I dropped the not nullconstraint on the DESCRIPTIONcolumn then the basic table structure would look like the following

如果我删除列not null上的约束，DESCRIPTION那么基本表结构将如下所示

+--------------+-----------+---------------+
|Name          | Null?     | Type          |
+--------------+-----------+---------------+
|ID            | NOT NULL  | NUMBER(35)    |
|DESCRIPTION   |           | VARCHAR2(4000)|
+--------------+-----------+---------------+

and both of the INSERTcommands as specified would be successful. They would create two rows one with a nullvalue and another with an empty string ''in the DESCRIPTIONcolumn of the TEMP_TABLE.

并且INSERT指定的两个命令都会成功。他们将创建两行一用一null值，另一个为空字符串''中DESCRIPTION的列TEMP_TABLE。

Now, if I issue the following SELECTcommand,

现在，如果我发出以下SELECT命令，

SELECT * FROM temp_table WHERE description IS NULL;

then it fetches both the rows in which one has a nullvalue and the other has an empty string ''in the DESCRIPTIONcolumn.

然后它获取列中有一个null值的行和另一个有空字符串''的DESCRIPTION行。

The following SELECTstatement however retrieves no rows from the TEMP_TABLE

SELECT但是，以下语句未从TEMP_TABLE

SELECT * FROM temp_table WHERE description='';

It doesn't even retrieve the row which has an empty string in the DESCRIPTIONcolumn.

它甚至不检索列中具有空字符串的DESCRIPTION行。

---

---

Presumably, it appears that Oracle treats a nullvalue and an empty string ''differently here which however doesn't appear to be the case with the INSERTstatement in which both a nullvalue and an empty string ''are prevented from being inserted into a column with a not nullconstraint. Why is it so?

据推测，Oracle 似乎在此处对null值和空字符串的处理方式''不同，但INSERT在阻止将null值和空字符串''插入具有not null约束的列中的语句中，情况似乎并非如此。为什么会这样？

回答by Vaibhav Desai

This is because Oracle internally changes empty string to NULL values. Oracle simply won't let insert an empty string.

这是因为 Oracle 在内部将空字符串更改为 NULL 值。Oracle 根本不会让插入空字符串。

On the other hand, SQL Server would let you do what you are trying to achieve.

另一方面，SQL Server 会让你做你想要实现的事情。

There are 2 workarounds here:

这里有两种解决方法：

1. Use another column that states whether the 'description' field is valid or not
2. Use some dummy value for the 'description' field where you want it to store empty string. (i.e. set the field to be 'stackoverflowrocks' assuming your real data will never encounter such a description value)

1. 使用另一列说明“描述”字段是否有效
2. 为您希望它存储空字符串的“描述”字段使用一些虚拟值。（即将字段设置为 'stackoverflowrocks' 假设您的真实数据永远不会遇到这样的描述值）

Both are, of course, stupid workarounds :)

当然，两者都是愚蠢的解决方法:)

回答by DazzaL

In oracle an empty varchar2 and null are treated the same, and your observations show that.

在 oracle 中，空 varchar2 和 null 的处理方式相同，您的观察表明了这一点。

when you write:

当你写：

select * from table where a = '';

its the same as writing

它和写作一样

select * from table where a = null;

and not a is null

并不是 a is null

which will never equate to true, so never return a row. same on the insert, a NOT NULL means you cant insert a null or an empty string (which is treated as a null)

这永远不会等于 true，所以永远不要返回一行。在插入时相同，NOT NULL 表示您不能插入空值或空字符串（被视为空值）