优秀的编程知识分享平台

网站首页 > 技术文章 正文

SQL 中的 NULL:奇特的存在与应对之策

nanyue 2025-03-04 13:24:33 技术文章 11 ℃

在 SQL 的世界里，NULL 值的处理方式常常让人感到困惑和意外。今天，我们就来深入探讨一下 SQL 中 NULL 值的那些奇特之处，以及如何应对它们带来的问题。

一、SQL NULL 值的独特性质

（一）与常规值的比较结果

我们先来建立一个基础的认知。在 SQL 中，使用逻辑等于（“=”）运算符比较不同的值时，结果可能会超出你的预期。例如，空字符串与空字符串比较（select '' = '';）会返回 1（表示 true），因为它们是相等的；数字 1 与 1 比较（select 1 = 1;）也返回 1，这符合我们的常规理解。然而，当比较两个 NULL 值时（select null = null;），返回的结果却是 NULL。这是因为 NULL 本质上是一个占位符，表示未知的值。两个未知值不能简单地被认定为相等，所以其比较结果既不是 true 也不是 false，而是 NULL，这确实让人感觉很奇特。

相比之下，使用 IS 运算符来检查 NULL 值的一致性时，select null is null; 会返回 1（true），因为 IS 运算符专门用于检查两个值是否都为 NULL 类型。通过下面这个更详细的示例，我们可以更清楚地看到 NULL 值与其他值在比较时的差异：

drop table if exists sample;
CREATE TABLE if not exists sample (
     id INTEGER PRIMARY KEY, -- 自动递增
     name TEXT -- UNIQUE（如果取消注释此行）
);
INSERT INTO sample (name) VALUES (NULL), (NULL), ('test'); --, ('test');
SELECT
    a.id as id1,
    b.id as id2,
    coalesce(a.name, 'null') || ', ' || coalesce(b.name, 'null') as names,
    a.name = b.name as equal_comparison,
    a.name IS b.name as is_comparison
FROM sample a
    CROSS JOIN sample b -- 在表的所有记录上创建一个 n by m 循环
WHERE a.id < b.id;

在这个示例中，当比较两个 NULL 值时，equal_comparison 列的值为 NULL，而 is_comparison 列的值为 1，这清晰地展示了两种比较方式的不同结果。

（二）在唯一性约束中的表现

在唯一性约束方面，NULL 值的行为也令人费解。通常我们认为，具有 UNIQUE 约束的列应该保证所有值都是唯一的，但对于 NULL 值却并非如此。例如，我们创建一个如下的表结构：

drop table if exists sample;
create table if not exists sample (
    id TEXT primary key,
    email TEXT,
    deleted_at TEXT,
    UNIQUE(email, deleted_at)
) strict;

然后尝试插入两条看起来似乎违反唯一性约束的记录：

insert into sample (id, email, deleted_at) values ('1', 'ray@mail.com', null);
insert into sample (id, email, deleted_at) values ('2', 'ray@mail.com', null);

令人惊讶的是，这两条记录都能成功插入表中。这是因为在 SQL 中，每个 NULL 值都被视为与其他 NULL 值不同，所以这两行数据在数据库看来是满足唯一性约束的。

二、这种处理方式的原因探究

根据 SQLite 的文档，SQLite 以及其他遵循 SQL 标准的数据库之所以这样处理 NULL 值，是为了与其他数据库的实现方式保持一致。然而，有趣的是，实际上没有一个数据库完全遵循 SQL 标准规范中关于 NULL 值的处理方式。SQL 标准文档似乎建议 NULL 值在所有情况下都应该是不同的，但在实际的 SELECT DISTINCT 语句或 UNION 操作中，目前测试过的 SQL 引擎都没有将 NULL 值视为不同的值。

以我们之前创建的表结构为例，UNIQUE(email, deleted_at) 约束确保没有两行具有相同的 email 和 deleted_at 组合，但允许存在相同 email 的多行记录，只要 deleted_at 不同。这种处理方式虽然在一定程度上保持了数据库之间的兼容性，但也给开发者带来了不少困惑。

三、确保唯一性的方法

（一）使用生成列（Generated Column）

为了解决 NULL 值不确定性带来的问题，我们可以创建一个具有确定性值的新字段。一种方法是使用生成列，它在插入和更新数据时会自动生成值。例如：

CREATE TABLE sample (
    id TEXT PRIMARY KEY,
    email TEXT,
    deleted_at TEXT, -- 可为空
    _deleted_at_coalesced TEXT GENERATED ALWAYS 
        AS (COALESCE(deleted_at, '1970-01-01')) STORED, -- 不可为空
    UNIQUE(email, _deleted_at_coalesced)
) STRICT;

在这个示例中，当 deleted_at 为 NULL 时，_deleted_at_coalesced 字段会被设置为 '1970-01-01'。这样就为每行数据提供了一个确定的值，从而确保了唯一性约束的有效性。

我们可以通过以下完整示例来测试这种方法：

drop table if exists sample;
CREATE TABLE sample (
    id TEXT PRIMARY KEY,
    email TEXT,
    deleted_at TEXT, 
    _deleted_at_coalesced TEXT GENERATED ALWAYS AS (COALESCE(deleted_at, '1970-01-01')) STORED, 
    UNIQUE(email, _deleted_at_coalesced)
) STRICT;
insert into sample (id, email, deleted_at) values ('1', 'ray@mail.com', null); -- 由于 email 和生成列的约束，此行插入将失败，取消注释可测试
-- insert into sample (id, email, deleted_at) values ('2', 'ray@mail.com', null);
insert into sample (id, email, deleted_at) values ('3', 'ray@mail.com', '2024-11-12T00:00:00.000Z');
insert into sample (id, email, deleted_at) values ('4', 'ray@mail.com', '2024-11-11T01:00:00.000Z');
insert into sample (id, email, deleted_at) values ('6', 'different@mail.com', null);
update sample set deleted_at = '2024-11-11T02:00:00.000Z' where deleted_at is null;
insert into sample (id, email, deleted_at) values ('7', 'different@mail.com', null);
update sample set deleted_at = '2024-11-11T03:00:00.000Z' where deleted_at is null;
insert into sample (id, email, deleted_at) values ('8', 'different@mail.com', null);
select * from sample;

需要注意的是，这种方法虽然有效，但也存在一个缺点。当尝试删除相同的记录两次（即当记录元组已经存在时），可能会出现问题。例如：

drop table if exists sample;
CREATE TABLE sample (
    id TEXT PRIMARY KEY,
    email TEXT,
    deleted_at TEXT, 
    _deleted_at_coalesced TEXT GENERATED ALWAYS AS (COALESCE(deleted_at, '1970-01-01')) STORED, 
    UNIQUE(email, _deleted_at_coalesced)
) STRICT;
insert into sample (id, email, deleted_at) values ('1', 'ray@mail.com', null);
update sample set deleted_at = '2024-11-11T03:00:00.000Z' where id is 1;
insert into sample (id, email, deleted_at) values ('2', 'ray@mail.com', null);
select * from sample; -- 由于 email 和生成列元组已经存在，此行更新将失败，取消注释可测试
-- update sample set deleted_at = '2024-11-11T03:00:00.000Z' where id is 2;

（二）使用部分索引（Partial Index）

另一种更好的解决方法是使用部分索引。我们可以在 email 字段上创建一个部分索引，条件是 deleted_at 字段为 NULL。例如：

CREATE UNIQUE INDEX if not exists idx_sample_email_deleted_at
    ON sample(email) WHERE deleted_at IS NULL;

通过以下测试示例可以看到这种方法的效果：

drop table if exists sample;
create table if not exists sample (
    id TEXT primary key,
    email TEXT,
    deleted_at TEXT
) strict;
CREATE UNIQUE INDEX if not exists idx_sample_email_deleted_at
    ON sample(email) WHERE deleted_at IS NULL;
insert into sample (id, email, deleted_at) values ('1', 'ray@mail.com', null); -- 由于 idx_sample_email_deleted_at 索引，此行插入将失败，取消注释可测试
-- insert into sample (id, email, deleted_at) values ('2', 'ray@mail.com', null);
insert into sample (id, email, deleted_at) values ('3', 'ray@mail.com', '2024-11-12T00:00:00.000Z');
insert into sample (id, email, deleted_at) values ('4', 'ray@mail.com', '2024-11-11T01:00:00.000Z');
insert into sample (id, email, deleted_at) values ('6', 'different@mail.com', null);
update sample set deleted_at = '2024-11-11T02:00:00.000Z' where deleted_at is null;
insert into sample (id, email, deleted_at) values ('7', 'different@mail.com', null);
update sample set deleted_at = '2024-11-11T03:00:00.000Z' where deleted_at is null;
insert into sample (id, email, deleted_at) values ('8', 'different@mail.com', null);
select * from sample;

使用部分索引不仅可以确保唯一性约束的有效性，而且不会使表变得更宽，无需管理额外的字段，占用空间更少，并且在重复删除相同记录对时也不容易出错。

四、总结与启示

对于有经验的工程师来说，SQL 中 NULL 值的这些特性可能看起来微不足道，尤其是在使用对象关系映射（ORM）时，这些细节可能会被隐藏起来。然而，如果不了解其背后的原理，很容易在实际应用中产生困惑和错误。

另外一个有趣的发现是，SQL 标准文档并不像 HTTP RFC 那样公开免费提供，而是需要付费获取。这也在一定程度上增加了开发者深入理解 SQL 标准的难度。

在数据库开发和使用过程中，我们必须充分认识到 SQL NULL 值的特殊性，并根据实际需求选择合适的方法来确保数据的完整性和一致性。无论是使用生成列还是部分索引，都需要权衡其优缺点，以达到最佳的解决方案。希望通过本文的介绍，能帮助大家更好地理解和应对 SQL 中的 NULL 值问题。

你在实际使用 SQL 过程中是否也遇到过与 NULL 值相关的困惑呢？欢迎在评论区分享你的经验和问题，让我们一起共同学习和进步。记得关注我们的公众号，获取更多数据库相关的技术知识和实用技巧。

科技脉搏，每日跳动。

与敖行客 Allthinker一起，创造属于开发者的多彩世界。

- 智慧链接 思想协作 -

Tags：

猜你喜欢

最近发表
标签列表