如何解决SQL查询使用DISTINCT时多个NULL值被折叠的问题

SQL标准规定DISTINCT将所有NULL视为相等值，多行NULL仅保留一个；主流数据库均遵循此规则，若需区分不同空值类型，应使用COALESCE、CASE WHEN等显式处理而非依赖DISTINCT。

SQL中DISTINCT对NULL的处理规则是什么

标准SQL规定，DISTINCT 在去重时将所有 NULL 视为相等值——也就是说，哪怕表里有100行某列为 NULL，SELECT DISTINCT col FROM t 只会返回一个 NULL。这不是Bug，是规范行为，但常被误认为“丢失数据”。

常见错误现象：

• 你预期看到多个“空状态”，比如不同原因导致的缺失（NULL、空字符串、占位符），结果全被压成一行 NULL
• 使用 GROUP BY 替代 DISTINCT 后发现结果一样，误以为是数据库bug

关键点：所有主流引擎（PostgreSQL、MySQL 8.0+、SQL Server、Oracle）都严格遵循这一规则；SQLite 默认也遵循，除非启用 PRAGMA nulls_first = OFF（极少用）

想保留多个NULL逻辑区分，该用什么替代方案

不能靠改 DISTINCT 行为，得换表达意图的方式：

• 如果目标是“把真正缺失和其他占位值区分开”，先统一清洗：

  SELECT DISTINCT COALESCE(col, '<null>') AS col_clean FROM t

• 如果要统计每种空值来源（比如日志中 NULL、'N/A'、''），用 CASE WHEN 显式分类：

  SELECT CASE WHEN col IS NULL THEN 'explicit_null' WHEN col = '' THEN 'empty_string' ELSE col END AS category FROM t

• 如果只是想避免 NULL 被折叠而又要保持原值，加一列唯一标识：

  SELECT DISTINCT col, id FROM t

  （前提是 id 能区分行）

注意：加 ROW_NUMBER() 或 GENERATE_SERIES() 等生成序号再 DISTINCT 没用——只要 col 值相同（含 NULL），仍会被合并。

MySQL和PostgreSQL在NULL去重上有什么实际差异

表面上没差异，但底层行为影响查询计划：

• MySQL 8.0+ 的 DISTINCT 会走临时表 + 排序，遇到大量 NULL 时排序开销略高（因为 NULL 在B-tree中位置不固定）
• PostgreSQL 使用哈希去重，NULL 被哈希到同一桶，效率更高；但若字段定义了 COLLATE "C"，且存在非ASCII空值（如 x00），可能被误判为等价于 NULL

实操建议：

• 在 PostgreSQL 中，如需严格区分 NULL 和空白字符串，确保字段类型不含默认 COLLATE，或显式写 col COLLATE "default"
• 在 MySQL 中，避免在 DISTINCT 字段上建前缀索引（如 VARCHAR(255) 加 INDEX(col(10))），否则 NULL 和短空值可能因截断产生哈希冲突

为什么ORDER BY + DISTINCT组合有时让NULL跑最前面

这不是 DISTINCT 的问题，而是 ORDER BY 对 NULL 的排序策略不同：

• PostgreSQL 默认 NULLS FIRST（除非显式写 NULLS LAST）
• MySQL 和 SQL Server 默认 NULLS LAST（但 MySQL 8.0 以前不支持 NULLS FIRST/LAST 语法，靠隐式规则）

所以这个语句：

SELECT DISTINCT col FROM t ORDER BY col

在 PostgreSQL 中，结果里 NULL 一定排第一；在 MySQL 中，NULL 排最后——但 DISTINCT 结果本身不变，只是展示顺序不同。

容易踩的坑：

• 用 ORDER BY col DESC 时，误以为 NULL 会排末尾（其实 PostgreSQL 还是第一，除非加 NULLS LAST）
• 在应用层依赖排序结果做分页，没意识到数据库间 NULL 位置不一致，导致翻页错位

NULL 的“相等性”是SQL语义根基，改不了；能改的只有你怎么定义“空”的业务含义。别试图绕过标准，先厘清你要的到底是“技术去重”还是“业务分类”。