贝塔中的DBA

topNULL是什么

NULL，字典的解释是空值。

如果你对ASCII表还有印象的话应该记得ASCII的第一个字符就是NUL(00)，在C/C++语言中它被用来作为一个字符串的结尾，通常表示为”\00″，因此从ASCII码的意义上说NULL还是算是“看得见摸得着”的东西。

但是当NULL放到SQL当中的时候，它的意义就会有些不同了，在SQL中，NULL就是空，但又不仅仅是空，实际上NULL代表着的是未知、是空洞、是虚无缥缈。这一点SQL Server和Oracle都达成了共识，且看SQL Server中对于NULL的描述：

空值一般表示数据未知、不适用或将在以后添加数据。

在Oracle中的描述也是类似的：

null
Absence of a value in a column of a row. Nulls indicate missing, unknown, or inapplicable data.

既然NULL代表的是未知，那当然就不能用常规等于或者是不等于来判断说一个值是否为NULL了，当然也就不能说NULL等于NULL，因为你是不能去判断两个未知是否相等，那是没有意义的。

由于SQL Server和Oracle都实现了标准的SQL，因此那些在标准SQL中所定义的判断是否为NULL的方法和规则在两中数据库引擎当中的实现都是一样的，也就是说下面这些语句在两种数据库当中运行的结果都是一样的。

判断一个列NULL与否：

-- 判断一个列为空
SELECT * FROM TEST_TABLE WHERE COL IS NULL
-- 判断一个列非空
SELECT * FROM TEST_TABLE WHERE COL IS NOT NULL

“NULL=NULL”么？

-- Oracle的回答
SQL> SELECT
  2      CASE
  3      WHEN NULL=NULL THEN 'NULL is equal to NULL'
  4          ELSE 'NULL is not equal to NULL'
  5      END "NULL=NULL?"
  6  FROM DUAL;

NULL=NULL?
-------------------------
NULL is not equal to NULL

-- SQL Server的回答
SELECT
    CASE
    WHEN NULL=NULL THEN 'NULL is equal to NULL'
        ELSE 'NULL is not equal to NULL'
    END "NULL=NULL?"

NULL=NULL?
-------------------------
NULL is not equal to NULL

既然NULL在SQL Server和Oracle中的实现都是一样的那么这篇文章想表达什么呢？嗯，在某些方面两种数据库对待NULL的态度是不一样的，文章接下来就说说这个不同到底在那里。

top空值(NULL)与空白(”)

如果你是一个从SQL Server转入到Oracle的DBA，你很有可能做得一个操作就是往某个表里面的某一字符串列插入一些空白(”)，然后想通过下面的语句找出那些列为空白的行来，就如下面的演示：

表结构和数据

-- 表结构
CREATE TABLE test ( a VARCHAR2(6), b CHAR(6), c VARCHAR2(6));

-- 数据
INSERT INTO TEST VALUES ('AAAAAA', 'BBBBBB', NULL);
INSERT INTO TEST VALUES ('CCCCCC', NULL, NULL);
INSERT INTO TEST VALUES ('DDDDDD', '', '');
commit;

你的目的是得到表中的第三行，就是列c为”的行，然后就执行

SQL> SELECT * FROM test WHERE c='';
no rows selected

同样的语句在SQL Server中的执行结果就是：

-- 创建表
CREATE TABLE test ( a VARCHAR(6), b CHAR(6), c VARCHAR(6));
-- 插入数据
INSERT INTO TEST VALUES ('AAAAAA', 'BBBBBB', NULL);
INSERT INTO TEST VALUES ('CCCCCC', NULL, NULL);
INSERT INTO TEST VALUES ('DDDDDD', '', '');
-- 查询
SELECT * FROM test WHERE c='';
a      b      c
------ ------ ------
DDDDDD

奇怪了吧，这就是SQL Server和Oracle上面对待NULL的不同，或者说是对待空白的不同，在SQL Server中空白和NULL会被认为是不一样的东西，而在Oracle中则被认为是一样的，在SQL Server的文档中可以找到下面的描述：

空值表示值未知。空值不同于空白或零值。没有两个相等的空值。比较两个空值或将空值与任何其他值相比均返回未知，这是因为每个空值均为未知。

下面再例举一些对比性的测试语句。

SQL Server中执行：

SELECT CASE
	WHEN '' IS NULL THEN ''''' is NULL'
	ELSE ''''' is not NULL' END "'' is NULL?";

'' is NULL?
--------------
'' is not NULL

SELECT
    CASE
    WHEN ''='' THEN ''''' is equal to '''''
        ELSE ''''' is not equal to '''''
    END "''''=''''?";

''''=''''?
-----------------
'' is equal to ''

同样的语句在Oracle执行：

SQL> SELECT CASE
  2     WHEN '' IS NULL THEN ''''' is NULL'
  3     ELSE ''''' is not NULL' END "'' is NULL?"
  4  FROM dual;

'' is NULL?
--------------
'' is NULL

SQL> SELECT
  2      CASE
  3      WHEN ''='' THEN ''''' is equal to '''''
  4          ELSE ''''' is not equal to '''''
  5      END "''''=''''?"
  6  FROM dual;

''''=''''?
---------------------
'' is not equal to ''

为什么会有这样的差异呢，在我认为还是觉得SQL Server做的更好些，空白(”)应该和整数值为0一样属于已知更合适一些，Oracle中这么处理多少会让人觉得有点的迷惑。不过从ASCII的角度看这也是没有什么差别的，一个空字符串就是”\00″，而”\00″就是空值。

除了从实现上看NULL之外，接下来再看看再这两种数据库中NULL是怎么存储的，同时也通过数值的存储来看看”和NULL究竟有什么区别。

topNULL和空白的存储

为了查看NULL和空白在SQL Server和Oracle数据库上面的存储情况，在这里我们使用相同的表结构和数据来进行对比看，创建表的结构和数据代码如下（跟上面的测试是一样的）：

CREATE TABLE test ( a VARCHAR2(6), b CHAR(6), c VARCHAR2(6));

INSERT INTO TEST VALUES ('AAAAAA', 'BBBBBB',NULL);
INSERT INTO TEST VALUES ('CCCCCC',NULL, NULL);
INSERT INTO TEST VALUES ('DDDDDD','', '');

COMMIT;

topOracle中的数据存储

首先我们找出表test中数据所在的数据文件和数据块，然后将数据库dump出来：

SQL> SET NULL NULL
SQL> SELECT header_file, header_block FROM dba_segments
  2  WHERE segment_name = 'TEST';

HEADER_FILE HEADER_BLOCK
----------- ------------
          8        20554

SQL> SELECT a, b, c, DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) blockno
  2  FROM test;

A      B      C         BLOCKNO
------ ------ ------ ----------
AAAAAA BBBBBB NULL        20558
CCCCCC NULL   NULL        20558
DDDDDD NULL   NULL        20558

SQL> ALTER SYSTEM DUMP DATAFILE 8 BLOCK 20558;

System altered.

找到相应的dump文件之后我们直接看关于数据存储的那一个dump记录：

        Repeat 500 times
07C9B7FD0 00000000 2C000000 44060101 44444444  [.......,...DDDDD]
07C9B7FE0 01012C44 43434306 2C434343 41060201  [D,...CCCCCC,...A]
07C9B7FF0 41414141 42420641 42424242 50DD0601  [AAAAA.BBBBBB...P]
Block header dump:  0x0200504e
 Object id on Block? Y
 seg/obj: 0x4d93  csc: 0x00.4350dc  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x2005048 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.01f.00000cf7  0x0080e832.0571.0e  --U-    3  fsc 0x0000.004350dd
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x0200504e
data_block_dump,data header at 0x7c9b6064
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x7c9b6064
     76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f73
avsp=0x1f5b
tosp=0x1f5b
0xe:pti[0]      nrow=3  offs=0
0x12:pri[0]     offs=0x1f87
0x14:pri[1]     offs=0x1f7d
0x16:pri[2]     offs=0x1f73
block_row_dump:
tab 0, row 0, @0x1f87
tl: 17 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 6]  41 41 41 41 41 41
col  1: [ 6]  42 42 42 42 42 42
tab 0, row 1, @0x1f7d
tl: 10 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 6]  43 43 43 43 43 43
tab 0, row 2, @0x1f73
tl: 10 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 6]  44 44 44 44 44 44
end_of_block_dump

从dump的结果可以看到，第二行和第三行是一样的，都是只有1列数据，因为另外的两列是空值，也是说插入的空白和NULL被统一的对待了。

topSQL Server中的数据存储

我们使用下列的语句找到数据页的页号，然后查看数据页里面的数据：

-- 查看表test的存储情况
DBCC IND(tempdb,test,-1)
-- 数据页结果
PageFID PagePID     IAMFID IAMPID      ObjectID    IndexID
------- ----------- ------ ----------- ----------- -----------
3       30          NULL   NULL        1655290070  0
1       86          3      30          1655290070  0
-- dump数据页
DBCC TRACEON(3604)
DBCC PAGE (tempdb, 1, 86, 3)

下面就是页86内容dump的部分结果了（这里仅列出关心的部分）：

Slot 0 Offset 0x60 Length 25

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x6389C060

00000000:   30000400 0300fc02 00130019 00434343 .0............CCC
00000010:   43434343 43434343 43.................CCCCCCCCC

Slot 0 Column 0 Offset 0xd Length 6

a = CCCCCC

Slot 0 Column 1 Offset 0x13 Length 6

b = CCCCCC
c = [NULL]

Slot 1 Offset 0x79 Length 17

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x6389C079

00000000:   30000400 0300fe01 00110044 44444444 .0..........DDDDD
00000010:   44...................................D

Slot 1 Column 0 Offset 0xb Length 6

a = DDDDDD
b = [NULL]
c = [NULL]

Slot 2 Offset 0x8a Length 17

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x6389C08A

00000000:   30000400 0300f801 00110044 44444444 .0..........DDDDD
00000010:   44........................D

Slot 2 Column 0 Offset 0xb Length 6

a = DDDDDD
b = [NULL]
c = [NULL]

从dump的结果我们同样可以看到第二行和第三行在数据的存储上是一样的，空白在这里都是以空值存储的，但是唯一的不同就在于对数据行性质的描述上面，现在先来看看SQL Server数据页行的详细格式：

数据页行数据格式（来源于《Microsoft SQl Server 2005技术内幕:存储引擎》）

从上图可以看出在每行数据中，存在一个标志行中的数据列是否为空的”NULL bitmap”，对照上面dump数据页，我们很容易能找到第二行的NULL标志位为fe，换算成二进制就是1111 1110，也就是除了第一列之外，其他所有列都为NULL（注意这里是倒着来的，下同）；第三行为f8，换算成二进制为1111 1000，也就是前三列都不为空，也是就说这三列中那两列显示值为NULL的列实际上是空白而非空值。

top小结一下

到这里其实已经很明确了，不管是在Oracle还是SQL Server中，空白和空值的存储都是一样的，都不占有任何的空间，所不同的是由于在承认是否有空白一说上面有分歧，所以SQL Server中会使用成为”NULL bitmap”的标志位来标识区分空白和空值，Oracle中则是一视同仁。

top延伸阅读

• Null character @ wikipedia
• Null (SQL)
• null @ 11gR2 Concepts
• 空值 @ SQL Server online books
• Oracle’s RDBMS SQL Command Dump Block
• Microsoft SQl Server 2005技术内幕:存储引擎