ORACLE中小心与NULL有关的操作

ORACLE中小心与NULL有关的操作

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和空字符串进行有效的区分.这样能避免某些错误的发生.