ORACLE中与NULL有关的操作
1.更新数据时注意NULL操作
(1)更新时,因为NULL导致更新后数据丢失.
创建表test_1和表test_2,两张表的测试数据如下:
SQL> select * from test_1;
ID1 ID2
----- -----
1 16
2 17
21
SQL> SELECT * FROM TEST_2;
ID1 ID2
----- -----
1 26
2 27
19
接下来利用表TEST_2去更新表TEST_1,关联字段为ID1,用TEST_2表ID2字段的值去更新TEST_1表ID2字段的值.
SQL> update test_1 a set id2=(select id2 from test_2 b where a.id1=b.id1);
3 rows updated
SQL> SELECT * FROM TEST_1;
ID1 ID2
----- -----
1 26
2 27
显然TEST_1表记录被做了更新,但却丢失了一条数据.
SQL> rollback;
Rollback complete
再利用表TEST_1和表TEST_2进行关联操作如下:
SQL> SELECT A.* FROM TEST_1 A,
2 TEST_2 B
3 WHERE A.ID1=B.ID1;
ID1 ID2
----- -----
1 16
2 17
从上面关联之后的结果来看,很显然所得出的结果跟我们想要的结果不一样,关联之后所得的结果数据中没有ID1为NULL的数据记录.
那么为什么以上操作会造成数据的丢失呢.要回答这个问题,首先得回答下面这个问题.
在ORACLE数据库中NULL=NULL?
对于这个问题有人回答是相等的,也有人回答是不等的,那么到底是相等的还是不相等的呢?下面在ORACLE数据库环境中做下实验.
SQL> select * from dual;
DUMMY
-----
X
SQL> select * from dual where 1=1;
DUMMY
-----
X
SQL> select * from dual where 1=2;
DUMMY
-----
通过上述SQL操作知道,可以用DUAL表来证明一下NULL等不等于NULL.
SQL> select * from dual where null=null;
DUMMY
-----
结果显示,NULL等于NULL是不对的.
SQL> select * from dual where null<>null;
DUMMY
-----
结果显示,NULL不等于NULL是不对的.
从上面SQL可以看出,在ORACLE中null=null和null<>null都是不对的.再看下面SQL
SQL> select * from dual where null is null;
DUMMY
-----
X
结果显示,NULL是可能等于NULL的.
由此可以看出,在ORACLE数据库中NULL表示的是一个未知的东西.这跟SQL Server和Sybase等其他版本的数据库是不一样的,他们认为NULL=NULL是正确的.这就可以解释为什么在做更新及关联操作时,结果表中数据丢失的原因了.
(2)更新时,因为NULL导致更新后数据出错.
SQL> select * from test_1;
ID1 ID2
----- -----
1 16
2 17
21
3 18
SQL> select * from test_2;
ID1 ID2
----- -----
1 26
2 27
4
利用表TEST_2去更新表TEST_1,关联字段为ID1,用TEST_2表ID2字段的值去更新TEST_1表ID2字段的值.
SQL> update test_1 a set id2=(select id2 from test_2 b where a.id1=b.id1);
4 rows updated
SQL> select * from test_1;
ID1 ID2
----- -----
1 26
2 27
3
从上面结果可以看出,更新后数据不仅丢失了ID1为空的数据,而且使得ID1=3这条数据发生了改变.可以看出上述更新操作是不合理的,在进行更新操作时并没有考虑NULL的情况.
SQL> rollback;
Rollback complete
正确的更新操作如下:
SQL> update test_1 a set id2=(select id2 from test_2 b where a.id1=b.id1)
2 where exists (select 1 from test_2 b where a.id1=b.id1);
2 rows updated
SQL> SELECT * FROM TEST_1;
ID1 ID2
----- -----
1 26
2 27
21
3 18
SQL> COMMIT;
Commit complete
SQL> SELECT * FROM TEST_1;
ID1 ID2
----- -----
1 26
2 27
21
3 18
2.索引与NULL
在ORACLE数据库中含有NULL的字段是不能走索引的,这是很多数据库开发者都知道的.那么为什么含有NULL的字段不能走索引呢.下面通过实验来说明一下原因.
<1>创建测试用的数据表TEST_3.
SQL> drop table test_3;
Table dropped
SQL> create table TEST_3
2 (
3 IP_ID NUMBER(4),
4 NAMENO NUMBER(4)
5 );
Table created
SQL> INSERT INTO TEST_3 VALUES(1,2);
1 row inserted
SQL> INSERT INTO TEST_3 VALUES(1,NULL);
1 row inserted
SQL> INSERT INTO TEST_3 VALUES(NULL,1);
1 row inserted
SQL> INSERT INTO TEST_3 VALUES(NULL,NULL);
1 row inserted
SQL> COMMIT;
Commit complete
SQL> SELECT * FROM TEST_3;
IP_ID NAMENO
----- ------
1 2
1
1
<2>根据表test_3的IP_ID和NAMENO字段建立索引in_test_3.
SQL> create unique index in_test_3 on test_3(IP_ID,NAMENO);
Index created
SQL> analyze index in_test_3 validate structure;
Index analyzed
<3>查看索引存储的行数,很显然索引中只存储了3行数据,而实际有四行,未存储的就是都为NULL的数据.
SQL> select a.name,a.lf_rows from index_stats a;
NAME LF_ROWS
------------------------------ ----------
IN_TEST_3 3
SQL> INSERT INTO TEST_3 VALUES(1,NULL);
INSERT INTO TEST_3 VALUES(1,NULL)
ORA-00001: 违反唯一约束条件 (ACCOUNT.IN_TEST_3)
因为建立的是唯一索引,所以再次重复插入违反了唯一约束条件.
SQL> INSERT INTO TEST_3 VALUES(NULL,NULL);
1 row inserted
因为索引不能存储NULL,所以当再次插入为NULL的数据记录时,不受索引唯一性的约束.
SQL> COMMIT;
Commit complete
SQL> SELECT * FROM TEST_3;
IP_ID NAMENO
----- ------
1 2
1
1
<4>NULL对COUNT()、MAX()和MIN()影响
SQL> SELECT A.IP_ID,COUNT(A.IP_ID) FROM TEST_3 A GROUP BY A.IP_ID;
IP_ID COUNT(A.IP_ID)
----- --------------
1 2
0
SQL> SELECT B.IP_ID,B.NAMENO,COUNT(*) FROM TEST_3 B GROUP BY B.IP_ID,B.NAMENO;
IP_ID NAMENO COUNT(*)
----- ------ ----------
1 1
2
1 2 1
1 1
SQL> SELECT MIN(A.IP_ID),MAX(A.IP_ID) FROM TEST_3 A;
MIN(A.IP_ID) MAX(A.IP_ID)
------------ ------------
1 1
3.在数据库中,NULL与空字符串存储到数据库中之后的表现形式都是一样的,即都是不可见的.那么NULL与空字符串到底有区别没有呢?在ORACLE数据库中' '是一个确定的值,而NULL则可以表示任何的一个值.
<1>创建试验数据表TEST_4.
SQL> create table TEST_4
2 (
3 IP_ID VARCHAR2(4),
4 NAMENO VARCHAR2(4)
5 );
Table created
SQL> INSERT INTO TEST_4 VALUES('1','2');
1 row inserted
SQL> INSERT INTO TEST_4 VALUES('1',' ');
1 row inserted
SQL> INSERT INTO TEST_4 VALUES(' ','1');
1 row inserted
SQL> INSERT INTO TEST_4 VALUES(' ',' ');
1 row inserted
SQL> COMMIT;
Commit complete
<2>创建索引及查看索引存储情况,跟上面为NULL进行对比.
SQL> CREATE UNIQUE INDEX IN_TEST_4 ON TEST_4(IP_ID,NAMENO);
Index created
SQL> ANALYZE INDEX IN_TEST_4 VALIDATE STRUCTURE;
Index analyzed
SQL> SELECT B.name,B.lf_rows FROM INDEX_STATS B;
NAME LF_ROWS
------------------------------ ----------
IN_TEST_4 4
在ORACLE数据库中空字符串是可以存储到索引中的,显然这与NULL是不同的.
SQL> INSERT INTO TEST_4 VALUES(' ',' ');
INSERT INTO TEST_4 VALUES(' ',' ')
ORA-00001: 违反唯一约束条件 (ACCOUNT.IN_TEST_4)
因为在数据库中' '被认为是一个确定的值,可以存储在索引中,索引当再次重复插入时,提示违反了唯一约束条件.
SQL> SELECT * FROM TEST_4;
IP_ID NAMENO
----- ------
1 2
1
1
SQL> SELECT MAX(A.IP_ID),MIN(A.IP_ID) FROM TEST_4 A;
MAX(A.IP_ID) MIN(A.IP_ID)
------------ ------------
1
与NULL相比,空字符串在数据库中是可以被MIN()与MAX()统计的,而且空字符串比不是空字符串的值要少.
SQL> SELECT A.IP_ID,COUNT(DISTINCT A.IP_ID) FROM TEST_4 A GROUP BY A.IP_ID;
IP_ID COUNT(DISTINCTA.IP_ID)
----- ----------------------
1
1 1
与NULL相比,空字符串是可以被COUNT()统计的.
SQL> SELECT A.IP_ID,A.NAMENO,COUNT(*) FROM TEST_4 A GROUP BY A.IP_ID,A.NAMENO;
IP_ID NAMENO COUNT(*)
----- ------ ----------
1 1
1 2 1
1 1
1
在统计数据表中的记录条数时,NULL跟NULL被认为是不一样的,但却属于同一类型.而空字符串则不同,空字符串被认为不是同一类型的.为形成对比做如下调整.
SQL> select * from test_4 t;
IP_ID NAMENO
----- ------
1 2
1
SQL> select a.ip_id,a.nameno,count(*) from test_4 a group by a.ip_id,a.nameno;
IP_ID NAMENO COUNT(*)
----- ------ ----------
1
1 2 1
1 1
1
显然,空字符串在被统计时,被认为是不一样的.
从上面的实验可以看出,在数据库中,不仅要小心与NULL有关的操作,而且还要做到能对NULL和空字符串进行有效的区分.这样能避免某些错误的发生.