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


本文转自Silent Void博客园博客,原文链接:http://www.cnblogs.com/happyhippy/archive/2008/12/13/1354344.html,如需转载请自行联系原作者

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
相关文章
|
7月前
|
SQL Oracle 关系型数据库
解决:Oracle数据库中Left join on 后面为null时匹配不上
解决:Oracle数据库中Left join on 后面为null时匹配不上
101 0
|
存储 SQL 缓存
为什么数据库字段要使用NOT NULL?
最近刚入职新公司,发现数据库设计有点小问题,数据库字段很多没有NOT NULL,对于强迫症晚期患者来说,简直难以忍受,因此有了这篇文章。
为什么数据库字段要使用NOT NULL?
|
安全 数据库
数据库——报错:无法创建链接服务器 “(null)“ 的 OLE DB 访问接口 “Microsoft.Ace.OLEDB.12.0“ 的实例。
数据库——报错:无法创建链接服务器 “(null)“ 的 OLE DB 访问接口 “Microsoft.Ace.OLEDB.12.0“ 的实例。
数据库——报错:无法创建链接服务器 “(null)“ 的 OLE DB 访问接口 “Microsoft.Ace.OLEDB.12.0“ 的实例。
|
SQL 数据库
数据库必知词汇:非空约束(NOT NULL)
NOT NULL 约束强制列不接受 NULL 值。NOT NULL 约束强制字段始终包含值。这意味着,如果不向字段添加值,就无法插入新记录或者更新记录。
642 0
|
SQL 架构师 关系型数据库
数据库允许空值(null),往往是悲剧的开始(1分钟系列)
数据库字段允许空值,会遇到一些问题,此处包含的一些知识点,和大家聊一聊。
526 0
数据库允许空值(null),往往是悲剧的开始(1分钟系列)
|
SQL 开发框架 JavaScript
数据库允许空值(null),往往是悲剧的开始(1分钟系列)
数据库字段允许空值,会遇到一些问题,此处包含的一些知识点,和大家聊一聊。
766 0
|
Java 数据库连接 数据库
使用mybatis对数据库执行更新操作时,parameterType为某个具体的bean,而bean中传入的参数为null时,抛出异常
使用mybatis对数据库执行更新操作时,parameterType为某个具体的bean,而bean中传入的参数为null时,抛出异常http://www.bieryun.com/3176.html 问题描述 在使用mybatis对数据库执行更新操作时,parameterType为某个具体的bean,而bean中传入的参数为null时,抛出异常如下:org.
1628 0
|
Java 数据库连接 数据库
使用mybatis对数据库执行更新操作时,parameterType为某个具体的bean,而bean中传入的参数为null时,抛出异常
使用mybatis对数据库执行更新操作时,parameterType为某个具体的bean,而bean中传入的参数为null时,抛出异常 问题描述 在使用mybatis对数据库执行更新操作时,parameterType为某个具体的bean,而bean中传入的参数为null时,抛出异常如下:org.
1939 0

热门文章

最新文章