详解数据库语言中的null值

2024-04-02 07:21:03 24

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

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

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

Null不支持大小/相等判断

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

select*fromuserswheredeleted_at=null;

–result:0rows

select*fromuserswheredeleted_at!=null;

–result:0rows

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

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

selectnull>0;

–result:null

selectnull<0;

–result:null

selectnull=0;

–result:null

selectnull=null;

–result:null

selectnull!=null;

–result:null

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

select*fromusers

wheredeleted_atisnull;

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

如果想要判断两列的值是否不相同，则可以使用isdistinctfrom:

select*fromusers

wherehas_addressisdistinctfromhas_photo

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

notin与Null

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

select*fromusers

whereidnotin(selectuser_idfrompackages)

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

select*fromusers

whereidnotin(1,2,null)

这个SQL语句会被转换为:

select*fromusers

whereid!=1andid!=2andid!=null

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

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

select*fromusers

whereidin(selectuser_idfrompackages)

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

select*fromusers

whereidin(1,2,null)

这条SQL被转换为:

select*fromusers

whereid=1orid=2orid=null

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

Null与排序

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

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

selectname,points

fromusers

orderby2desc;

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

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

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

selectname,coalesce(points,0)

fromusers

orderby2desc;

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

selectname,points

fromusers

orderbycoalesce(points,0)desc;

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

selectname,coalesce(points,0)

fromusers

orderby2descnullslast;

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

被0除

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

selectcasewhennum_users=0then0

elsetotal_sales/num_usersend;

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

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

selecttotal_sales/nullif(num_users,0);

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

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

selectcoalesce(total_sales/nullif(num_users,0),0);

null再转换回0

Conclusion

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

详解数据库语言中的null值

热门推荐

随机推荐