SQL 中的 NULL

发表于 更新于 分类于

NULL 本身的含义

NULL本身在数据库中的含义为“unknown”：

  • 代表未知；
  • 不等于空字符串；
  • 不等于 0；

NULL 的运算

逻辑运算

在 SQL 中判断是否为 NULL 不能用简单的 = NULL，如：

1
SELECT * FROM `users` WHERE `password` = NULL;

这样子的语句相当于

1
SELECT * FROM `users` WHERE false;

原因是：

  • = NULL 本身的结果是 unknown；
  • 在逻辑表达式的其它部分已经执行完毕后，若“unknown”仍然存在，则将其按照 false 处理。

逻辑运算：

  • (true and unknown) = unknown,
  • (false and unknown) = false,
  • (unknown and unknown) = unknown，
  • (unknown or true) = true,
  • (unknown or false) = unknown，
  • (unknown or unknown) = unknown

举例：

1
2
SELECT * FROM `users` WHERE true or `password` = NULL;
# where 子句结果为 true

正确的判断是否为 NULL 的方式应改为：

  • is NULL
  • is not NULL

可能会产生 unknown 的情况

  • 属性没有默认值且设置可以为空，在该记录的该属性没有插入值时，默认为 NULL；

  • 左连接、右连接中存在元组没有在另一个表中找到满足 on 指定条件的元组；

  • 算数运算中包含 NULL；

    1
    2
    3
    1 + NULL
    1 * NULL
    ...

  • 逻辑运算中包含 NULL；

    凡是逻辑运算中包含 NULL 的结果都为 unknown，没错， NULL = NULL 结果同样为 unknown

    1
    2
    3
    = NULL
    > NULL
    != NULL

NULL 可能引发的问题及解决方法

标量子查询问题

问题场景：如果我们想将所有用户的硬币数设置为所有用户的硬币总数：

1
2
3
UPDATE `users` SET `coins` = (
    SELECT SUM(`coins`) FROM `users`
);

此时，若存在用户的 coins 为 NULL 的话，那子查询得到的结果就变成了 NULL，原语句在这种情况下等价于：

1
UPDATE `users` SET `coins` = NULL;

这显然不是我们想要的结果。

解决方法有：

  • 设置默认值。由于之前一直没理解 NULL 的含义，所以一直没有为属性设置默认值的习惯。其实设置默认值往往是解决 NULL 问题的高效且简单的解决方法。

  • 筛除 NULL 的情况

    1
    2
    3
    UPDATE `users` SET `coins` = (
        SELECT SUM(`coins`) FROM `users` WHERE `coins` is not NULL;
    );