SQLite - NULL 值

SQLite - 理解 NULL 值

在 SQLite 中，NULL 代表值的缺失。它是一种状态，而不是值本身。包含 NULL 的列是空的，表示该字段没有输入任何数据。

理解 NULL 与零 (0) 或包含空字符串 ('') 的字段是不同的，这一点至关重要。将它们视为相同是应用程序中常见的 Bug 来源。

三值逻辑的挑战

涉及 NULL 的比较会引入一个名为三值逻辑（TRUE、FALSE 和 UNKNOWN）的概念。任何使用标准运算符（如 =、!= 或 >）直接与 NULL 进行比较都会导致 UNKNOWN，而不是 TRUE 或 FALSE。例如，NULL = NULL 的结果是 UNKNOWN。

因此，您必须使用特殊的运算符 IS NULL 和 IS NOT NULL 来检查是否存在缺失值。

定义可空列的语法

创建表时，您可以指定列是否可以存储 NULL 值。如果您不指定 NOT NULL，则该列默认可以包含 NULL 值。

CREATE TABLE employees (
   id INTEGER PRIMARY KEY, -- 隐式为 NOT NULL
   first_name TEXT NOT NULL,
   last_name TEXT NOT NULL,
   age INTEGER NOT NULL,
   department TEXT, -- 可以为 NULL
   salary REAL -- 可以为 NULL
);

在这里，department 和 salary 列是可空的，这意味着在插入新记录时它们可以留空。

实际示例

我们以 employees 表为例，其中包含以下记录：

id | first_name | last_name | age | department | salary
---|------------|-----------|-----|------------|--------
1  | 'John'     | 'Doe'     | 32  | 'Sales'    | 50000.0
2  | 'Jane'     | 'Smith'   | 25  | 'HR'       | 45000.0
3  | 'Peter'    | 'Jones'   | 23  | 'Sales'    | 48000.0
4  | 'Mary'     | 'Williams'| 35  | 'IT'       | 75000.0
5  | 'Chris'    | 'Green'   | 28  | NULL       | NULL

最后一条记录 ‘Chris Green’ 的 department 和 salary 字段为 NULL，这可能是因为他们是新员工，详细信息尚未最终确定。

要查找所有薪资已知的员工，请使用 IS NOT NULL：

SELECT id, first_name, last_name, salary
FROM employees
WHERE salary IS NOT NULL;

此查询返回前四条记录，不包括 Chris。

id | first_name | last_name | salary
---|------------|-----------|--------
1  | 'John'     | 'Doe'     | 50000.0
2  | 'Jane'     | 'Smith'   | 45000.0
3  | 'Peter'    | 'Jones'   | 48000.0
4  | 'Mary'     | 'Williams'| 75000.0

要查找所有部门信息缺失的员工，请使用 IS NULL：

SELECT id, first_name, last_name, department
FROM employees
WHERE department IS NULL;

此查询仅返回 Chris Green 的记录。

id | first_name | last_name | department
---|------------|-----------|------------
5  | 'Chris'    | 'Green'   | NULL

常见陷阱和最佳实践

1. 不正确的比较： 切勿使用 column = NULL 或 column != NULL。它们不会按预期工作。始终使用 IS NULL 和 IS NOT NULL。

2. 聚合函数： 像 COUNT(column_name)、SUM() 和 AVG() 这样的函数会忽略 NULL 值。COUNT(*) 会计算所有行，而 COUNT(salary) 只会计算 salary 不为 NULL 的行。请注意这种区别。

3. 提供默认值： 查询数据时，NULL 可能会带来不便。使用 COALESCE() 函数可以在列为 NULL 时提供一个默认值。它返回其参数列表中的第一个非 NULL 值。

-- 如果 department 为 NULL，则显示 'Unassigned'
SELECT first_name, COALESCE(department, 'Unassigned') as department
FROM employees;

这个强大的函数简化了应用程序代码中 NULL 值的处理。