详解数据库语言中的null值
作者:periscope 发布时间:2024-01-15 02:50:29
虽然熟练掌握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
Conclusion
Tony Hoare 也许会后悔自己的错误, 但至少 null 存在的问题很容易地就解决了. 那么快去练练新的大招吧,从此远离 null 挖出来的无效大坑(nullifying)!
猜你喜欢
- 原来的程序是使用sqlite这个嵌入式数据库作为Remit(code name)的数据源的,因为NHibernate支持这个,然而有一点不好
- 平时在PL/SQL中的编程中遇到一些问题,这里以问答的形式来进行把它们总结下来,以供大家分享。1、当需要向表中装载大量的数据流或者需要处理大
- kmp算法kmp算法用于字符串的模式匹配,也就是找到模式字符串在目标字符串的第一次出现的位置比如abababc那么bab在其位置1处,bc在
- 本文详细归纳整理了Python中列表、字典、元组、集合数据结构。分享给大家供大家参考。具体分析如下:列表:shoplist = ['
- MyBatis 如何写配置文件和简单使用MyBatis3.x这里简单贴一下MyBatis的介绍,具体使用方法会在代码中贴出。MyBatis的
- python2:print语句,语句就意味着可以直接跟要打印的东西,如果后面接的是一个元组对象,直接打印python3:print函数,函数
- 利用Python中的socket模块中的来实现UDP协议,这里写一个简单的服务器和客户端。为了说明网络编程中UDP的应用,这里就不写图形化了
- 在推广Web标准的今天,那些崇尚Web标准的人经常说XHTML比HTML更加严格,当然从某种意义上说是的,比如它要求所有的标签关闭并且所有的
- 基于以下三个原因,我们选择Python作为实现机器学习算法的编程语言:(1) Python的语法清晰;(2) 易于操作纯文本文件;(3) 使
- 目录1.Python 变量的概述:2.Python 变量的命名3.Python 变量赋值3.1 Python 变量赋值概述3.2 Pytho
- 1.官网下载Pycharm community版如pycharm-community-2017.3.1.tar.gz。2. #解压tar.g
- 简单使用了一下之后,我觉得如果有机会(公司里面编码是极不自由的,也无所谓,我在公司不做数据分析),我肯定是更喜欢使用IPython作为我的P
- Python 中的 Operator 模块可以让它支持函数式编程。1 计算函数假设我们需要一个计算阶乘的函数,一般做法是使用递归。如果使用函
- Python装饰器(decorator)在实现的时候,被装饰后的函数其实已经是另外一个函数了(函数名等函数属性会发生改变),为了不影响,Py
- 随着网站访问量的加大,每次从数据库读取都是以效率作为代价的,很多用ACCESS作数据库的更会深有体会,静态页加在搜索时,也会被优先考虑。互联
- 引言使用python接口来运行caffe程序,主要的原因是python非常容易可视化。所以不推荐大家在命令行下面运行python程序。如果非
- 以下方案皆为引用,仅供参考。方案一:1.先声明一下,这种解决方法适用于任何版本的永久破解启动不了的情况(包括:2019版本的)2.下面直接切
- 是否应该开启缓冲器? 通过脚本程序启动缓冲器 在ASP脚本的顶部包含Response.Buffer=True ,IIS就会将页面的内容缓存。
- 一、Excel环境配置 服务器端的环境配置从参考资料上看,微软系列的配置应该都行,即:1.Win9x+PWS+Office2.Wi
- 数据库在时回加for xml auto调用方法 SqlCommand SqlComm=