PostgreSQL中NULL的意义

yzsDBA

发布于 2022-12-27 13:53:58

2.2K0

发布于 2022-12-27 13:53:58

文章被收录于专栏：PostgreSQL研究与原理解析

PostgreSQL中NULL的意义

PG中，NULL可以表示空numeric值；不能进行数学操作符计算，NULL参与的结果都是NULL。

1、NULL是什么

首先需要理解NULL值是什么。因不同语言处理方式不同，所以NULL值经常引起一些混淆。在详细介绍之前，还需要了解三值逻辑和二值逻辑概念。二值是布尔值的概念，要么真，要么假。但三值逻辑可以真，也可以为假，还可以是中间值（未知）。某些语言中，NULL充当二值逻辑，而其他语言中则可能充当三值逻辑（尤其是数据库中）。

1)C/C++中

在C/C++语言中，NULL定义为0。可以使用等值运算符“==”或者“!=”，如：

#include <stddef.h>#include <stdio.h>void main(){    if ('0' == NULL)        printf("NULL is '0' \n");    if ("" == NULL)        printf("NULL is empty string \n");    if (' ' == NULL)        printf("NULL is space \n");    if (0 == NULL)        printf("NULL is 0 \n");}

上述程序的输出将是“NULL is 0”，所以很明显NULL在C语言中被定义为“0”

2)Java

与C/C++不一样，java中的NULL确实有值。该值可以通过等值运算符进行测试，当打印空值时，会打印空值。Java中，null区分大小写，必须全小写为“null”。

public class Test
{ 
    public static void main (String[] args) throws java.lang.Exception
    {
        System.out.println("Null is: " + null);
    }
}
Null is: null

3)PostgreSQL中的NULL

在 PostgreSQL 中，NULL 表示没有值。换句话说，NULL 列没有任何值。它不等于 0、空字符串或空格。NULL 值不能使用任何相等运算符（如“=”“!=”等）进行测试。有一些特殊的语句可以针对 NULL 测试该值，但除此之外，没有任何语句可以用于测试 NULL 值。

让我们做一些有趣的比较，这将清楚 PostgreSQL 中 NULL 的概念。在下面的代码片段中，我们将 1 与 1 进行比较，显而易见的结果是“t”（真）。这让我们明白，当两个值匹配时，PostgreSQL 相等运算符给了我们 true。同样，相等运算符适用于文本值。

postgres=# SELECT 1 = 1 result;
 result 
--------
 t
(1 row)
postgres=# SELECT 'foo' = 'foo' result;
 result 
--------
 t
(1 row)

让我们再做一些实验，比较 NULL 和 NULL。如果 NULL 是一个正常值，那么结果应该是“t”。但是 NULL 不是一个正常的值，因此，没有结果。

postgres=# SELECT NULL = NULL result;
result
--------
(1 row)

让我们使用不等式运算符比较 NULL 和 NULL。结果和我们之前得到的一样。这证明我们不能使用相等和不等运算符来比较 NULL 和 NULL。

postgres=# SELECT NULL != NULL result;
result
--------
(1 row)

同样，不能对 NULL 执行数学运算。当任何 NULL 用作操作数时，PostgreSQL 什么都不产生。

postgres=# SELECT NULL * 10 is NULL result;
result
--------
t
(1 row)

2、如何使用NULL

因此，可以证明不能使用等值操作符对NULL进行比较。那么如何使用NULL呢？PG提供了特殊的语句和函数来对NULL值进行检查和测试。

1)IS NULL/IS NOT NULL

postgres=# SELECT NULL is NULL result;
result
--------
t
(1 row)
postgres=# SELECT NULL is NOT NULL result;
result
--------
f
(1 row)

2)COALESCE

PG的COALESCE函数返回参数中第一个非NULL的值，要求参数中至少有一个是非NULL的，如果参数都为NULL则报错：

COALESCE (NULL, 2 , 1);

3)NULLIF

另外一个函数是NULLIF，如果两个参数相等，则返回NULL，否则返回第一个参数：

postgres=# SELECT NULLIF (10, 10);
nullif
--------

(1 row)
postgres=# SELECT NULLIF (10, 100);
nullif
--------
10
(1 row)

3、NULL的使用

NULL没有任何值，那么它的优势是什么？下面是使用例子：

包含名字、中间名、和姓氏字段的表。每个人不一定都有名字或者中间名或者姓氏。

postgres=# CREATE TABLE student(id INTEGER, fname TEXT, sname TEXT, lname TEXT, age INTEGER);
postgres=# SELECT * FROM STUDENT;
id | fname | sname | lname | age
----+-------+-------+-------+-----
1 | Adams | Baker | Clark |  21
2 | Davis |       | Evans | 22
3 | Ghosh | Hills |       | 24
(3 rows)

我们选择有中间名的学生。下面查询有效吗？

postgres=# SELECT * FROM STUDENT WHERE sname = '';
id | fname | sname | lname | age
----+-------+-------+-------+-----
(0 rows)

下面是正确的语句：

postgres=# SELECT * FROM STUDENT WHERE sname IS NULL;
id | fname | sname | lname | age
----+-------+-------+-------+-----
2 | Davis |       | Evans | 22
(1 row)

例如，该字段没有意义，因为单身人士的配偶姓名或孩子的详细信息不是“KID”。这是离婚领域中的 KID 没有意义的示例。我们不能设置 true 或 false，所以 NULL 在这里是正确的值。

postgres=# CREATE TABLE person(id INTEGER, name TEXT, type TEXT, divorced bool);
postgres=# SELECT * FROM person;
 id | name  | type | divorced 
----+-------+-------+---------
  1 | Alice | WOMAN | f
  3 | Davis | KID   | 
  2 | Bob   | MAN | t
(3 rows)

NULL 的另一种用法是表示空字符串和空数值。数字 0 具有重要意义，因此它不能用于表示空的数字字段，即某个时间的未知值。

在这个例子中，有 3 个学生：Alice 有 90 分，Bob 有 0 分，而 Davis 还没有分数。对于 Bob，我们插入了 0，对于 Davis，我们插入了 NULL。通过这样做，我们可以轻松区分谁有 0 分，谁还没有结果。

postgres=# SELECT * FROM students_mark;
 id | name  | marks 
----+-------+-------
  1 | Alex  | 90
  2 | Bob   | 0
  2 | Davis |      
(3 rows)

postgres=# SELECT * FROM students_mark WHERE marks IS NULL;
 id | name  | marks 
----+-------+-------
  2 | Davis |      
(1 row)

postgres=# SELECT * FROM students_mark WHERE marks = 0;
 id | name | marks 
----+------+-------
  2 | Bob  | 0
(1 row)