异常教程

PostgreSQL NULL 值（一文讲透）

更新时间:

💡一则或许对你有用的小广告

欢迎加入小哈的星球 ，你将获得：专属的项目实战 / 1v1 提问 / Java 学习路线 / 学习打卡 / 每月赠书 / 社群讨论

  • 新项目:《从零手撸：仿小红书（微服务架构）》 正在持续爆肝中，基于 Spring Cloud Alibaba + Spring Boot 3.x + JDK 17...，点击查看项目介绍 ;
  • 《从零手撸：前后端分离博客项目（全栈开发）》 2 期已完结，演示链接： http://116.62.199.48/ ;

截止目前， 星球 内专栏累计输出 82w+ 字，讲解图 3441+ 张，还在持续爆肝中.. 后续还会上新更多项目，目标是将 Java 领域典型的项目都整一波，如秒杀系统, 在线商城, IM 即时通讯，权限管理，Spring Cloud Alibaba 微服务等等，已有 2900+ 小伙伴加入学习 ，欢迎点击围观

前言

在数据库开发中，NULL 是一个既常见又容易引发误解的概念。对于编程初学者和中级开发者而言，理解 PostgreSQL 中 NULL 值的特性及处理方法，是避免逻辑错误、提升数据查询效率的关键。本文通过 循序渐进 的方式，结合 形象比喻 和 实战案例，深入解析 PostgreSQL NULL 值 的核心知识点，帮助读者建立系统化的认知框架。

NULL 的概念与特性

什么是 NULL？

在 PostgreSQL 中，NULL 表示“未知的值”或“不存在的值”，而非空字符串（''）或数字零（0）。例如，一个用户表中，某个字段 last_login_time 可能因用户从未登录而被标记为 NULL。

类比说明：
假设图书馆的书架上有多个书籍，每个书架代表一个数据库表的行。若某本书的出版日期字段为空，则相当于该书架的“出版日期”抽屉中没有存放任何内容，此时该字段的值就是 NULL。

NULL 与空字符串的区别

  • 空字符串（''）：表示字段存在但值为空，占用存储空间。
  • NULL：表示字段值不存在或未定义，不占用存储空间。

代码示例：

CREATE TABLE example (
    id INT,
    name TEXT,
    description TEXT
);

INSERT INTO example (id, name) VALUES (1, 'Alice'); -- description 字段未赋值，默认为 NULL
INSERT INTO example (id, name, description) VALUES (2, 'Bob', ''); -- description 明确赋值为空字符串

三值逻辑：NULL 的特殊性

在 PostgreSQL 中，涉及 NULL 的布尔表达式会触发 三值逻辑（True、False、Unknown）。例如：

  • NULL = 5 的结果是 NULL，而非 False。
  • WHERE 子句中，只有明确为 True 的条件才会返回记录。

关键原则：

  • 无法直接比较 NULL：WHERE column = NULL 是无效的，需使用 IS NULL 或 IS NOT NULL。
  • 算术运算与 NULL：任何与 NULL 相关的运算结果均为 NULL，例如 NULL + 5 = NULL。

如何查询和处理 NULL 值

查询 NULL：IS NULL 和 IS NOT NULL

要筛选包含 NULL 或非 NULL 的记录，需使用 IS NULL 和 IS NOT NULL：

代码示例：

-- 查询 description 字段为 NULL 的记录
SELECT * FROM example WHERE description IS NULL;

-- 查询 description 字段非 NULL 且不为空字符串的记录
SELECT * FROM example 
WHERE description IS NOT NULL 
AND description <> '';

替换或合并 NULL：COALESCE 函数

COALESCE 函数用于返回参数列表中第一个非 NULL 的值，常用于替换或补充缺失值。

应用场景：

-- 将 NULL 转换为默认值 "未填写"
SELECT id, name, COALESCE(description, '未填写') AS formatted_description 
FROM example;

生成 NULL：NULLIF 函数

NULLIF 函数用于比较两个表达式，若相等则返回 NULL，否则返回第一个表达式。

案例：

-- 当销售额与成本相等时，利润设为 NULL
SELECT sales, cost, NULLIF(sales, cost) AS profit 
FROM products;

聚合函数与 NULL

大多数聚合函数（如 SUM, AVG, COUNT）会忽略 NULL 值。例如：

-- 计算非 NULL 的销售额总和
SELECT SUM(sales) AS total_sales FROM orders; -- NULL 值会被自动排除

常见误区与解决方案

误区 1：错误使用 = 比较 NULL

直接使用 WHERE column = NULL 会导致 所有记录被过滤，因为 NULL 的比较结果是 UNKNOWN。

正确写法：

-- 正确筛选 NULL 值
SELECT * FROM orders WHERE customer_id IS NULL;

误区 2：忽视 NULL 对计算的影响

在计算中，若涉及 NULL，结果可能被意外置空。例如：

-- 错误示例：当 price 或 quantity 为 NULL 时，total_price 也会是 NULL
SELECT price * quantity AS total_price FROM products;

解决方案：
使用 COALESCE 替换 NULL 为默认值：

SELECT COALESCE(price, 0) * COALESCE(quantity, 0) AS total_price 
FROM products;

误区 3：COUNT(*) 与 COUNT(column) 的区别

  • COUNT(*)：统计所有行，包括 NULL。
  • COUNT(column)：仅统计非 NULL 的值。

示例：

-- 假设 orders 表中部分订单的 `customer_id` 为 NULL
SELECT 
    COUNT(*) AS total_orders,      -- 包含所有订单
    COUNT(customer_id) AS valid_orders -- 仅统计非 NULL 的 customer_id
FROM orders;

实战案例：电商订单表的 NULL 处理

案例背景

假设有一个电商订单表 orders，结构如下：

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    product_name TEXT,
    quantity INT,
    order_date TIMESTAMP,
    discount DECIMAL
);

部分字段可能包含 NULL，例如未指定折扣（discount）或未记录客户（customer_id）。

典型查询需求

  1. 筛选未应用折扣的订单：

    SELECT * FROM orders 
WHERE discount IS NULL; -- 查找 discount 未设置的订单

  2. 计算订单总金额（忽略 NULL）：

    SELECT 
    order_id,
    product_name,
    quantity * price AS total_amount -- 假设 price 存在
FROM orders;

  3. 补充缺失的客户信息：

    SELECT 
    order_id,
    customer_id,
    COALESCE(customer_id, '匿名用户') AS customer_name 
FROM orders;

错误场景与修正

问题：统计订单的平均折扣时，NULL 导致结果异常。

-- 错误：返回 NULL，因为存在未指定 discount 的记录
SELECT AVG(discount) FROM orders;

修正：使用 FILTER 子句明确筛选非 NULL 值：

SELECT AVG(discount) FILTER (WHERE discount IS NOT NULL) AS avg_discount 
FROM orders;

最佳实践与设计建议

数据库设计阶段

  1. 明确字段是否允许 NULL：

    • 对于必填字段（如 order_date），设置 NOT NULL 约束： 
      CREATE TABLE orders (... order_date TIMESTAMP NOT NULL ...);
    • 对于可选字段（如 discount），保留 NULL 以表示“未应用折扣”。

  2. 使用默认值：
    通过 DEFAULT 关键字为字段指定默认值，避免意外的 NULL：

    CREATE TABLE users (... last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP ...);

查询阶段

  1. 显式处理 NULL：
    在 SELECT 或 WHERE 子句中，通过 COALESCE、FILTER 或 CASE 语句主动处理 NULL。

  2. 文档与注释：
    在表结构或查询注释中说明字段可能为 NULL，帮助其他开发者避免误解。

结论

PostgreSQL 中的 NULL 值既是数据缺失的标记，也是逻辑复杂性的来源。通过理解其特性（如三值逻辑）、掌握处理工具（如 COALESCE、FILTER），开发者可以有效避免因 NULL 引发的错误，并构建健壮的数据库应用。

对于初学者，建议从基础语法开始练习，逐步通过实际案例（如电商订单分析）深化理解；中级开发者则可结合更复杂的场景（如多表关联、聚合函数优化），进一步提升 NULL 值的处理能力。记住：NULL 不是错误，而是需要被合理设计与管理的数据状态。

最新发布

查看更多文章