数据库中罪恶的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聚集运算时,会被忽略掉,从而达到特殊值不参与聚集函数运算的功能。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值