null对空字符串在Oracle [duplicate](null vs empty string in Oracle [duplicate])

 
 
 可能重复： 
 为什么Oracle 9i将空字符串视为NULL？ 
 
 
 我在Oracle 10g中有一个名为TEMP_TABLE的表，只有两列 - id和description只是为了演示。 
 
 列id是序列生成的主键，类型为NUMBER(35, 0) not null并且DESCRIPTION是VARCHAR2(4000) not null 。 
 
 在这种情况下，基本的表格结构将如下所示。 
 
+--------------+-----------+---------------+
|Name          | Null?     | Type          |
+--------------+-----------+---------------+
|ID            | NOT NULL  | NUMBER(35)    |
|DESCRIPTION   | NOT NULL  | VARCHAR2(4000)|
+--------------+-----------+---------------+
 
 创建此表后，我试图插入以下INSERT命令。 
 
INSERT INTO temp_table (id, description) VALUES (1, null); ->unsuccessful
INSERT INTO temp_table (id, description) VALUES (2, '');   ->unsuccessful
 
 它们都不成功，因为在DESCRIPTION列上强制执行not null约束。 
 
 在这两种情况下，Oracle抱怨 
 
ORA-01400: cannot insert NULL into ("WAGAFASHIONDB"."TEMP_TABLE"."DESCRIPTION")
 
 在Oracle中将空字符串视为NULL值。 
 
 
 如果我在DESCRIPTION列上删除了not null约束，那么基本的表结构将如下所示 
 
+--------------+-----------+---------------+
|Name          | Null?     | Type          |
+--------------+-----------+---------------+
|ID            | NOT NULL  | NUMBER(35)    |
|DESCRIPTION   |           | VARCHAR2(4000)|
+--------------+-----------+---------------+
 
 并且指定的两个INSERT命令都将成功。 它们将在TEMP_TABLE的DESCRIPTION列中创建两行一个， null值为null ，另一行为空字符串。 
 
 现在，如果我发出以下SELECT命令， 
 
SELECT * FROM temp_table WHERE description IS NULL;
 
 那么它将获取两个行，其中一个具有null值，另一个在DESCRIPTION列中有一个空字符串。 
 
 然而，以下SELECT语句从TEMP_TABLE检索不到任何行 
 
SELECT * FROM temp_table WHERE description='';
 
 它甚至没有检索在DESCRIPTION列中有一个空字符串的行。 
 
 
 大概看来，Oracle在这里看到一个null值和一个空字符串，但是在INSERT语句似乎并不是这样，其中一个null值和一个空字符串''被阻止插入具有not null约束的列。 为什么会这样？ 

 
 
Possible Duplicate:
 Why does Oracle 9i treat an empty string as NULL? 
 
 
I have a table in Oracle 10g named TEMP_TABLE with only two columns - id and description just for the sake of demonstration.
 
The column id is a sequence generated primary key of type NUMBER(35, 0) not null and the column DESCRIPTION is a type of 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 INSERT commands alternatively.
 
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 null constraint is enforced on the DESCRIPTION column.
 
In both of the cases, Oracle complains
 
ORA-01400: cannot insert NULL into ("WAGAFASHIONDB"."TEMP_TABLE"."DESCRIPTION")
 
An empty string is treated as a NULL value in Oracle.
 
 
If I dropped the not null constraint on the DESCRIPTION column then the basic table structure would look like the following
 
+--------------+-----------+---------------+
|Name          | Null?     | Type          |
+--------------+-----------+---------------+
|ID            | NOT NULL  | NUMBER(35)    |
|DESCRIPTION   |           | VARCHAR2(4000)|
+--------------+-----------+---------------+
 
and both of the INSERT commands as specified would be successful. They would create two rows one with a null value and another with an empty string '' in the DESCRIPTION column of the TEMP_TABLE.
 
Now, if I issue the following SELECT command,
 
SELECT * FROM temp_table WHERE description IS NULL;
 
then it fetches both the rows in which one has a null value and the other has an empty string '' in the DESCRIPTION column.
 
The following SELECT statement however retrieves no rows from the TEMP_TABLE
 
SELECT * FROM temp_table WHERE description='';
 
It doesn't even retrieve the row which has an empty string in the DESCRIPTION column.
 
 
Presumably, it appears that Oracle treats a null value and an empty string '' differently here which however doesn't appear to be the case with the INSERT statement in which both a null value and an empty string '' are prevented from being inserted into a column with a not null constraint. Why is it so?

原文：https://stackoverflow.com/questions/13278773