数据库中罪恶的NULL

摘要:1.Null表示Unknow;

2.聚合运算;
3.性能;
4.NO NULL!

1.Null表示Unknow:

    SQL基于三值逻辑true、false、unknow；Null与任何类型的值进行比较(=、>、>=、<、<=)或运算(+、-、*、/、In)的结果为Unknow；看起来好像很容易理解，但在使用中我们却经常容易忽略细节，下面具体的测试：

--Query1.初始化测试数据

Create Table #T1(Col nvarchar(10) default null)

INSERT INTO #T1 default values;

INSERT INTO #T1 values('a');

INSERT INTO #T1 values('b');

Create Table #T2(Col nvarchar(10) default null)

INSERT INTO #T2 default values;

INSERT INTO #T2 values('a');

INSERT INTO #T2 values('b');

INSERT INTO #T2 values('c'); //比#T1多一条记录

    现在希望找出#T2中比#T1中多出的记录(Y的，这还不简单，这不侮辱咱的智商吗...一句就Not IN或者NOT Exists就搞定了；肉眼金睛：#T2比#T1中仅多一条记录)，于是我们写出下面的两个版本的SQL：
(1). NOT IN

SELECT * FROM #T2

  WHERE Col NOT IN(SELECT Col FROM #T1)

    很遗憾，这句SQL失败了，得到了空结果集！于是我们开骂：顶In个肺，IN太垃圾了，效率又低；我们应该改用NOT EXISTS！
(2). NOT EXISTS (=)

SELECT * FROM #T2

  WHERE NOT EXISTS(SELECT * FROM #T1 WHERE #T1.Col=#T2.Col)

    很遗憾，这句SQL又失败了，找出了两条记录！太打击了，靠～

    说明：可以设置当前会话SET ANSI_Nulls OFF，来使Null==Null；

2.聚合运算:

    下面的SQL，看看输出结果多少：

--Query1中已创建#T1并插入测试数据

SELECT Count(Col) FROM #T1

SELECT Count(*) FROM #T1

    #T1就只有一列Col，凭直觉，Count(Col)和Count(*)的结果应该是相等的吧。
    等等，别被直觉给忽悠了，上面的语句执行得到的结果是：Count(Col)=2，Count(*)=3。
    说明：Count(columnName)、Sum、AVG、Max()、Min()等聚集函数会忽略null值；但Count(*)不会忽略值全部为null的行;

3.性能

    SQL Server通过系统表SysObject的位图列来记录表中列是否允许为null，所以处理允许为null的列时需要耗费一定的资源；(当然也有个特例，譬如某列Column不允许为空，则根据Column IS NULL来进行查询过滤时，会直接返回空集，而不会傻到去表中取数据判断；但这样的过滤条件是没有任何现实意义的。)

    某些数据库上，例如Oracle，使用IS NULL进行过滤时，将不会使用索引。http://www.eygle.com/archives/2006/02/index_null_hints_explain.html

引自上面的链接："由于B*Tree索引不存储Null值,所以在索引字段允许为空的情况下,某些Oracle查询不会使用索引。很多时候,我们看似可以使用全索引扫描(Full Index Scan)的情况,可能Oracle就会因为Null值的存在而放弃索引。在此情况下即使使用Hints,Oracle也不会使用索引,其根本原因就是因为Null值的存在。"

    我在SQL Server 2000上测试，貌似是可以用到索引(暂还没有搞清楚是咋实现的)，下面是具体的测试过程：
(1) 测试SQL:

IF(Object_ID('TestNull') IS NOT NULL)

    DROP TABLE TestNull;

CREATE TABLE [TestNull] (

[ID] [int],

[Value] [nchar] (1000) NULL 

);

Create CLUSTERED Index ID_Index ON TestNull(ID);

--插入测试数据

DECLARE @Index int;

SET @Index=-1;

WHILE(@Index<10000)

BEGIN

    INSERT INTO TestNull([ID],Value) Values(@Index, 10000-@Index);

    SET @Index=@Index+1;

END

INSERT INTO TestNull([ID],Value) Values(NULL,10000);

GO

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

SET STATISTICS IO ON

SET STATISTICS TIME ON

SELECT * FROM TestNull WHERE ID=-1

SET STATISTICS TIME OFF

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

SET STATISTICS TIME ON

SELECT * FROM TestNull WHERE ID IS NULL

SET STATISTICS TIME OFF

SET STATISTICS IO OFF

(2). 测试结果:


<1> SELECT * FROM TestNull WHERE ID=-1

（所影响的行数为 1 行）

表 'TestNull'。扫描计数 1，逻辑读 3 次，物理读 1 次，预读 0 次。

SQL Server 执行时间:    CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。

<2> SELECT * FROM TestNull WHERE ID IS NULL

（所影响的行数为 1 行）

表 'TestNull'。扫描计数 1，逻辑读 3 次，物理读 1 次，预读 0 次。

SQL Server 执行时间:    CPU 时间 = 0 毫秒，耗费时间 = 1 毫秒。

    从执行计划和统计信息来看，这里是使用了索引查找；从SQL Server 2005的联机丛书中，我也找到下面这句：For indexing purposes, NULL values compare as equal.

 

4.NO Null！

    在我以前参与过的几个项目中，都没有在使用Null的地方进行规范，譬如一个允许为null的nvarchar(xx)类型的字段，有些记录中值为null，有些记录中值为空字符串''，每次进行条件过时就要同时写上IS NULL OR ColumnName=''，nvarchar算是比较幸运的，因为Convert.ToString(DBNull)可以将DBNull转成空字符串；但相比之下，int或者DateTime就没有这么幸运了，Convert.ToInt32(DBNull)和Convert.ToDateTime(DBNull)会抛出异常，以至于我们经常需要写如下的代码来处理Null值：

object columnValue = ReadFromDB;

DateTime? time = null;

if(columnValue==DBNull)

    time = Convert.ToDateTime(columnValue);

    这样使用起来相当地不爽；可以看到，nvarchar中，NULL其实和空字符串('')代表同样的含义，我们完全可以用''来代替NULL；DateTime中，我们也可以用特殊值'9999-12-31'来代替空值，从而可以在C#中的统一处理，而不用加个扯淡判断(columnValue==DBNull)；我们完全可以用一些特殊值(系统的正常逻辑中，不会用到的值，例如Int.MinValue、0、-1、'N/A'、''、'1900-01-01'、'9999-12-31'等)来替代null，以避免使用null！

    这样替换后，也带来一个问题：Null不参与聚集函数(Count(*)除外)的运算，如何让替换后的特殊值也不参与聚集函数的运算呢？这里，我们可以使用NullIf来实现：
    SELECT SUM(NullIF(ColumnName,'-1')) FROM TableName;
    NullIF:当列值与替换后的特殊值-1相等时，则返回null；null参与SUM聚集运算时，会被忽略掉，从而达到特殊值不参与聚集函数运算的功能。