PostgreSQL空值处理

飞象数据 2020-05-22

4459

1 什么是NULL空值

由于在不同的语言中对空值的处理方式不同，因此常常会对空值产生一些混淆。因此，需要澄清什么是NULL空值，它在不同语言中是如何工作的，以及NULL背后的实际值是什么。在详细讨论之前，还需要理解三值逻辑[1]和二值逻辑（称为二值逻辑[2]）的概念。二值逻辑是布尔值的概念，其中值可以为真（true ）或假（false），但与二值逻辑相反，三值逻辑可以为真（true ）、假（false）或未知（unknown）。现在，我们返回来讨论空值。在某些语言中，NULL充当二值逻辑，而在另一些语言中，则是三值逻辑（特别是在数据库中）。

2 C/C++空值

在C++和C语言中NULL都被定义为0。

#if defined (_STDDEF_H) || defined (__need_NULL)
#undef NULL     /* in case <stdio.h> has defined it. */
#ifdef __GNUG__
#define NULL __null
#else   /* G++ */
#ifndef __cplusplus
#define NULL ((void *)0)
#else   /* C++ */
#define NULL 0
#endif  /* C++ */
#endif  /* G++ */
#endif  /* NULL not defined and <stddef.h> or need NULL.  */
#undef  __need_NULL

可以使用相等运算符“==”或“!=”直接对空值进行测试. 以下示例尝试检查C中的空值。

#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为0”，因此很明显，在C语言中，NULL被定义为“0”。

3 Java空值

Java与C中的NULL为0不同，NULL表示变量引用确实有值。该值可以由相等运算符针对NULL进行测试。当我们打印空值时，它将打印空值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

4 PostgreSQL空值

在PostgreSQL中，NULL表示没有值。换句话说，NULL列没有任何值。它不等于0、空字符串''或空格' '。不能使用任何类似“=”或‘’!=“等运算符测试空值。有一些特殊的语句可以测试空值，但除此之外，没有语句可以测试空值。

让我们做一些有趣的比较，这将澄清PostgreSQL中NULL的概念。在下面的代码片段中，我们将1与1进行比较，明显的结果是“t”（TRUE）。这使我们了解到，当两个值匹配时，PostgreSQL相等运算符为true。类似地，相等运算符也适用于文本值。

# 数值比较
postgres=# SELECT 1 = 1 result;
 result 
--------
 t
(1 row)

# 文本值比较
postgres=# SELECT 'foo' = 'foo' result;
 result 
--------
 t
(1 row)

让我们做更多的实验，比较一下NULL和NULL。如果NULL是正常值，则结果应为“t”。但空值不是一个正常值，因此没有结果。

# 空值比较
postgres=# SELECT NULL = NULL result;
result
--------


(1 row)

让我们使用不等运算符比较NULL和NULL。结果和之前一样。这证明了我们不能用等式和不等式算子来比较空和空。

Normal NULL Inequality ComparisonShell

postgres=# SELECT NULL != NULL result;
result
--------


(1 row)

类似地，不能对空值执行任何数学运算。当任何空值用作操作数时，PostgreSQL不产生任何结果，即任何运算符和空值计算结果为NULL空值。

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

4.1 空值的处理

因此，证明了空值不能与使用等式运算符的任何值进行比较。那么，如果我们不能使用任何运算符或数学运算，我们如何使用空值呢？PostgreSQL提供特殊的语句和函数来检查和测试空值，这在PostgreSQL中使用NULL是唯一的方法。

4.1.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)

4.1.2 COALESCE

PostgreSQL有一个函数名“COALESCE”[3]。函数接受n个参数并返回（按照从左到右）第一个非null参数，类似oracle 数据库中的nvl函数。

COALESCE (NULL, 2 , 1);

4.1.3 NULLIF

下面是另一个名为“NULLIF”[3]的函数，如果第一个和第二个参数相等，则返回NULL，否则返回第一个参数，下面是我们比较10和10的示例，我们已经知道它们相等，因此它将返回NULL。在第二个例子中，我们将10与100进行比较，在这种情况下，它将返回第一个参数值10。

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

(1 row)

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

4.2 空值的使用

如果NULL没有任何值，那么NULL的优点是什么？下面是一些用法示例：

如果一个字段没有任何值，例如，我们有一个名为first/middle和last name的数据库字段。实际上，每个人都有first/middle和last name吗？答案为否，一个字段应该可以没有任何值。

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)

让我们来查询没有sname的学生。这个查询在这里有效吗？实际没有查询到数据，原因就是前面说到的postgresql中空值并不等于0、空字符串''或空格' '。

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”。下面的例子，字段divorced（离异）对应为孩子时是没有意义的。我们不能把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具有重要意义，因此它不能用于表示空数字字段，即某个时间的未知值。

在这个例子中有三个学生：Alex有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)

5 结论

这篇文章的目的是明确每种语言都有自己的空值含义。因此，在使用NULL时要小心，否则会得到错误的结果。尤其是在数据库（PostgreSQL）中，NULL有一些不同的概念，所以在编写涉及NULL的查询时要小心。

[1] – https://en.wikipedia.org/wiki/Three-valued_logic

[2] – https://en.wikipedia.org/wiki/Principle_of_bivalence

[3] – https://www.postgresql.org/docs/current/functions-conditional.html

本文翻译自：https://www.percona.com/blog/2020/03/05/handling-null-values-in-postgresql/

postgresql

文章转载自飞象数据，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。