下文以 MySQL 为例。NULL 用于表示缺失或未知的数据,应与 0 和空字符串区分。普通比较 NULL = NULL 的结果为 UNKNOWN(MySQL 以 NULL 表示),不是 TRUE,也不是 FALSE;判断某列是否为 NULL 应使用 IS NULL 或 IS NOT NULL。
一、NULL的传递
在SQL中,NULL这个“未知”的状态,是可传递的。譬如,对NULL进行算术运算,结果都是NULL。
SELECT 2 + NULL;
SELECT 2 - NULL;
SELECT 2 * NULL;
SELECT 2 / NULL;
值得注意的是,用NULL除以0时,许多数据库系统并不会返回"data exception — division by zero"的错误,而是返回NULL。此外,NULL还可以运用于字符串的连接中。MySQL 的 CONCAT() 中任意参数为 NULL 时结果为 NULL;不同数据库或其他拼接函数的行为需分别查看文档。
二、三值逻辑
二值逻辑。传统的真假逻辑,就是二值逻辑。对于一个给定的命题,它要么为真,要么为假,非真即假,非假即真。在二值逻辑中,逻辑运算的结果只有真和假,不存在任何中间态。非真即假,非好即坏,是理想中的世界,而现实世界要复杂得多。对于一个命题,它不仅有可能是真的,有可能是假的,还有可能是咱不知道的、判断不了的。那么,现在就有真、假、未知,三种结果,即三值逻辑。NULL就是这个表示「未知」的标记符。对于一个命题来说,如果它非真,不意味着它一定为假,因为它也有可能是NULL;如果它非假,也并不意味着它一定成立,因为它也有可能是NULL。TRUE/FALSE/NULL,三种逻辑运算结果,形成了三值逻辑体系。 对于不确定的对象,无法使用=进行比较(也无法用 >、<、!= 等符号进行比较),当=两边有NULL时,结果一律是NULL。如果我们需要从数据库中筛选出含/不含NULL的数据,需要使用IS NULL/IS NOT NULL。
SELECT * FROM users WHERE name IS NULL;
SELECT * FROM users WHERE name IS NOT NULL;
三、使用NULL注意点
1、COUNT列数据丢失
当某列存在NULL值时,再使用COUNT查询该列,就会出现数据“丢失”问题,如下SQL所示
SELECT COUNT(name) FROM users;
如果指定列存在NULL值时,需要用COUNT(*)进行数据统计,就可以统计全部匹配行,包括 name 为 NULL 的行;COUNT(name) 仅统计 name 非 NULL 的行。
2、SELECT数据列丢失
如果某列存在NULL值时,如果执行非等于查询(<>/!=)会导致为NULL值的结果丢失
SELECT * FROM users WHERE name!='lisi';
查询时如果name列有NULL值并不会出现在结果中,如果需要则把NULL值包含在结果中即可
SELECT * FROM users WHERE name!='lisi' OR name IS NULL;
3、查询返回NULL值
如果某列存在NULL值时,可能会导致SUM(column) 的返回结果为NULL而非0,如果SUM查询的结果为NULL就可能导致未处理可空结果的程序出错
SELECT SUM(weight) FROM users WHERE name!='lisi';
如果 WHERE 条件命中零条结果,或匹配行的 weight 全部为 NULL,SUM 都返回 NULL;需要返回 0 时可以使用 IFNULL 处理
SELECT IFNULL(SUM(weight),0) FROM users WHERE name!='lisi';
4、IN/NOT IN“异常”
没法判断某一个值在不在NULL,也没法判断NULL在不在某一个值中,所以此种情况下返回NULL。
NULL IN <任意值>/NULL NOT IN <任意值>判断一律返回NULL值,以下语句都返回NULL。
SELECT NULL IN (1);
SELECT NULL NOT IN (1);
<任意值> IN (NULL)/<任意值> NOT IN (NULL)判断也一律返回NULL,以下语句都返回NULL。
SELECT 1 IN (NULL);
SELECT 1 NOT IN (NULL);
特别的,参数列表里有NULL值时,如果根据其他的值没有确定结果,则也会返回NULL。
SELECT 1 IN (2, NULL); /*NULL*/
SELECT 2 IN (2, NULL); /*true*/
SELECT 1 NOT IN (2, NULL); /*NULL*/
SELECT 2 NOT IN (2, NULL); /*false*/