文档章节

SQL中的Null深入研究分析

e
 enosh
发布于 2015/04/15 09:36
字数 1360
阅读 6
收藏 0

虽然熟练掌握SQL的人对于Null不会有什么疑问,但总结得很全的文章还是很难找,看到一篇英文版的, 感觉还不错。

Tony Hoare 在1965年发明了 null 引用, 并认为这是他犯下的“几十亿美元的错误”. 即便是50年后的今天, SQL中的 null 值还是导致许多常见错误的罪魁祸首.

我们一起来看那些最令人震惊的情况。

Null不支持大小/相等判断

下面的2个查询,不管表 users 中有多少条记录,返回的记录都是0行:

select * from users where deleted_at = null;

– result: 0 rows

select * from users where deleted_at != null;

– result: 0 rows

怎么会这样子? 一切只因为 null 是表示一种“未知”的类型。也就是说,用常规的比较操作符(normal conditional operators)来将 null 与其他值比较是没有意义的。 Null 也不等于 Null(近似理解: 未知的值不能等于未知的值,两者间的关系也是未知,否则数学和逻辑上就乱套了)。

– 注意: 下面的SQL适合于MySQL,如果是Oracle,你需要加上 … from dual;

select null > 0;

– result: null

select null < 0;

– result: null

select null = 0;

– result: null

select null = null;

– result: null

select null != null;

– result: null

将某个值与 null 进行比较的正确方法是使用 is 关键字, 以及 is not 操作符:

select * from users

where deleted_at is null;

– result: 所有被标记为删除的 users

如果想要判断两列的值是否不相同,则可以使用 is distinct from:

select * from users

where has_address is distinct from has_photo

– result: 地址(address)或照片(photo)两者只有其一的用户

not in 与 Null

子查询(subselect)是一种很方便的过滤数据的方法。例如,如果想要查询没有任何包的用户,可以编写下面这样一个查询:

select * from users 

where id not in (select user_id from packages)

但此时假若 packages 表中某一行的 user_id 是 null 的话,问题就来了: 返回结果是空的! 要理解为什么会发生这种古怪的事情, 我们需要理解SQL编译器究竟干了些什么. 下面是一个更简单的示例:

select * from users 

where id not in (1, 2, null)

这个SQL语句会被转换为:

select * from users 

where id != 1 and id != 2 and id != null

我们知道,id != null 结果是个未知值, null. 而任意值和 null 进行 and 运算的结果都是 null, 所以相当于没有其他条件. 那么出这种结果的原因就是 null 的逻辑值不为 true.

如果条件调换过来, 查询结果就没有问题。 现在我们查询有package的用户.

select * from users 

where id in (select user_id from packages)

同样我们可以使用简单的例子:

select * from users

where id in (1, 2, null)

这条SQL被转换为:

select * from users 

where id = 1 or id = 2 or id = null

因为 where 子句中是一串的 or 条件,所以其中某个的结果为 null 也是无关紧要的。非真(non-true)值并不影响子句中其他部分的计算结果,相当于被忽略了。

Null与排序

在排序时, null 值被认为是最大的. 在降序排序时(descending)这会让你非常头大,因为 null值排在了最前面。

下面这个查询是为了根据得分显示用户排名, 但它将没有得分的用户排到了最前面!

select name, points

from users

order by 2 desc;

– points 为 null 的记录排在所有记录之前!

解决这类问题有两种思路。最简单的一种是用 coalesce 消除 null的影响:

– 在输出时将 null 转换为 0 :

select name, coalesce(points, 0)

from users

order by 2 desc;

– 输出时保留 null, 但排序时转换为 0 :

select name, points

from users

order by coalesce(points, 0) desc;

还有一种方式需要数据库的支持,指定排序时将 null 值放在最前面还是最后面:

select name, coalesce(points, 0)

from users

order by 2 desc nulls last;

当然, null 也可以用来防止错误的发生,比如处理除数为0的数学运算错误。

被 0 除

除数为0是一个非常 egg-painfull 的错误。昨天还运行得好好的SQL,突然被0除一下子就出错了。一个常用的解决方法是先用 case 语句判断分母(denominator)是否为0,再进行除法运算。

select case when num_users = 0 then 0 

else total_sales/num_users end;

ase 语句的方式其实很难看,而且分母被重复使用了。如果是简单的情况还好,如果分母是个很复杂的表达式,那么悲剧就来了: 很难读,很难维护和修改,一不小心就是一堆BUG.

这时候我们可以看看 null 的好处. 使用 nullif 使得分母为0时变成 null. 这样就不再报错, num_users = 0 时返回结果变为 null.

select total_sales/nullif(num_users, 0);

nullif 是将其他值转为 null, 而Oracle的 nvl 是将 null 转换为其他值。

如果不想要 null,而是希望转换为 0 或者其他数, 则可以在前一个SQL的基础上使用 coalesce函数:

select coalesce(total_sales/nullif(num_users, 0), 0);

null 再转换回0

总结

Tony Hoare 也许会后悔自己的错误, 但至少 null 存在的问题很容易地就解决了. 那么快去练练新的大招吧,从此远离 null 挖出来的无效大坑(nullifying)!

本文转载自:http://www.codeceo.com/article/sql-null.html

共有 人打赏支持
e
粉丝 7
博文 75
码字总数 19035
作品 0
大连
私信 提问
mysqlbinlog解析binlog乱码问题解密

发现mysql库的binlog日志出来都是乱码,如下所示: BINLOG ’ IXZqVhNIAAAALQAAAGcBAAAAAHoAAAAAAAEABHRlc3QAAno0AAEDAABUOcnY IXZqVh5IAAAAKAAAAI8BAAAAAHoAAAAAAAEAAgAB//4BAAAAcu+UpA==,......

gool_hard
06/26
0
0
hibernate中load和get方法的区别

1.读取时机不同(当lazy=true的时候) load是采用延迟机制(load语句不读库,等使用非主键时才去读库),而get不采用延 迟机制(get语句时马上读库); 2.搜索不到数据时的情况 当搜索的数据不存...

桃源闲人
2012/04/01
0
0
mybatis学习笔记(9)-订单商品数据模型分析

mybatis学习笔记(9)-订单商品数据模型分析 标签: mybatis [TOC] 本文对接下来几篇博客中用到的数据模型进行分析,并附上建表sql文件和测试数据文件 数据模型分析思路 每张表记录的数据内容 ...

brianway
2016/03/01
61
0
T-SQL中常用的set语句

1、SET ANSI_DEFAULTS { ON | OFF }示例: 设置 SET ANSI_DEFAULTS ON 并使用 DBCC USEROPTIONS 语句显示受影响的设置。 2、SET ANSI_NULLS { ON | OFF }T-SQL 支持在与空值进行比较时,允许...

AlunE
2017/12/22
0
0
[渗透&攻防] 三.数据库之差异备份及Caidao利器

这是最近学习渗透和网站攻防的文章,前面文章从数据库原理解读了防止SQL注入、SQLMAP的基础用法。这篇文章主要讲解数据库知识之差异备份及强大的利器Caidao。希望能深入地学习这部分知识,自...

Eastmount
2017/07/23
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Kafka+Flink 实现准实时异常检测系统

1.背景介绍 异常检测可以定义为“基于行动者(人或机器)的行为是否正常作出决策”,这项技术可以应用于非常多的行业中,比如金融场景中做交易检测、贷款检测;工业场景中做生产线预警;安防...

架构师springboot
21分钟前
3
0
DecimalFormat 类基本使用

/* * DecimalFormat 类主要靠 # 和 0 两种占位符号来指定数字长度 * 0 表示如果位数不足则以 0 填充 * # 表示只要有可能就把数字拉上这个位置 * */ public static void main(String[] args){...

嘴角轻扬30
38分钟前
3
0
This APT has Super Cow Powers.

在Debian/Ubuntu上,apt包管理器内嵌着一个彩蛋. 如果你在命令行界面输入 apt help 在最后一行能找到This APT has Super Cow Powers. 说明该apt具有超级牛力 牛力是个什么梗? 则说明你的系统...

taadis
55分钟前
2
0
起薪2万的爬虫工程师,Python需要学到什么程度才可以就业?

爬虫工程师的的薪资为20K起,当然,因为大数据,薪资也将一路上扬。那么,Python需要学到什么程度呢?今天我们来看看3位前辈的回答。 1、前段时间快要毕业,而我又不想找自己的老本行Java开发...

糖宝lsh
今天
9
0
携手开发者共建云生态 首届腾讯云+社区开发者大会在京举办

本文由云+社区发表 北京时间12月15日,由腾讯云主办,极客邦科技、微信、腾讯TEG协办的首届腾讯云+社区开发者大会在北京朝阳悠唐皇冠假日酒店举办。在会上,腾讯云发布了重磅产品开发者平台以...

腾讯云加社区
今天
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部