数据库中的 NULL 值有哪些坑，为什么它会让索引和统计失效？

学完这篇，你将掌握 NULL 在数据库中的特殊含义，理解为什么使用 = 查询 NULL 会失效，以及为什么它会导致统计信息和索引性能下降，并学会如何避免这些坑。

一、理解 NULL 的本质：它不是 0 也不是空字符串

这一步要创建一个测试表并插入数据，通过对比展示 NULL 到底代表什么。在数据库中，NULL 的意思是“未知”或“无值”，它既不是数字 0，也不是空字符串 ''，甚至不占用存储空间。

1. 打开你的数据库客户端（如 MySQL Workbench、Navicat 或命令行），执行以下 SQL 命令创建测试表：

    CREATE TABLE user_profile (
        id INT PRIMARY KEY,
        username VARCHAR(20),
        age INT
    );
    

1. 插入几条测试数据，注意观察 username 字段是否为空：

    INSERT INTO user_profile VALUES (1, 'Tom', 25);
    INSERT INTO user_profile VALUES (2, NULL, 30);
    INSERT INTO user_profile VALUES (3, 'Jerry', NULL);
    

1. 查询所有数据，确认 NULL 的存在：

    SELECT * FROM user_profile;
    

注意： 这里插入的 NULL 是真正的数据库 NULL 值，不是字符串 "NULL"。

二、逻辑陷阱：为什么 = 查询不到 NULL

这一步要演示最常见的坑，即试图用 = 号去匹配 NULL 值，结果会一无所获。因为数据库无法判断一个未知的值是否等于某个具体的值，所以 WHERE col = NULL 永远不会返回结果。

1. 尝试使用 = 号查询名为 NULL 的用户，观察结果：

    SELECT * FROM user_profile WHERE username = NULL;
    

1. 你会发现查询结果为空（0 行）。这是因为数据库把 NULL = NULL 视为 False。

注意： 在 SQL 中判断字段是否为空，必须使用 IS NULL，判断非空则使用 IS NOT NULL。

三、统计陷阱：COUNT 函数的歧义

这一步要展示 COUNT(*) 和 COUNT(字段名) 的区别，这会导致统计结果出错。COUNT(*) 统计表中所有行的总数，不管值是什么；而 COUNT(字段名) 只统计该字段有值的行数，NULL 值会被跳过。

1. 同时执行两个查询命令，对比结果：

    -- 统计总行数
    SELECT COUNT(*) FROM user_profile;
    -- 统计有名字的行数
    SELECT COUNT(username) FROM user_profile;
    

1. 你会发现 COUNT(username) 的结果比 COUNT(*) 少 1，因为第 2 条数据的 username 是 NULL。

注意： 如果你想要统计所有用户，永远使用 COUNT(*)，不要用 COUNT(列名)。

四、性能陷阱：为什么索引会失效

这一步要解释 NULL 对索引的影响，这是性能问题的根源。在 B-Tree 索引结构中，NULL 值通常不会被存储在索引树中，或者被特殊处理。这会导致两个问题：一是查询 col = NULL 时，数据库无法利用索引快速定位，只能进行全表扫描；二是当索引列包含大量 NULL 时，索引的效率会大打折扣。

1. 为 username 字段创建一个普通索引：

    CREATE INDEX idx_username ON user_profile(username);
    

1. 执行查询并查看执行计划，判断是否使用了索引：

    EXPLAIN SELECT * FROM user_profile WHERE username = NULL;
    

1. 你会发现执行计划中显示 type 为 ALL（全表扫描），说明索引失效了。

注意： 虽然 WHERE col IS NULL 通常可以利用索引，但混合使用 OR 和 IS NULL（例如 WHERE username = 'Tom' OR username IS NULL）往往会破坏索引的使用，导致性能下降。

五、解决方案：如何避免 NULL 的坑

这一步要介绍最佳实践，通过设置默认值来规避 NULL 带来的问题。通常建议在定义表结构时，给字段设置一个明确的默认值（如空字符串 '' 或 0），而不是留空。

1. 修改表结构，给 username 字段设置默认值为空字符串：

    ALTER TABLE user_profile MODIFY username VARCHAR(20) DEFAULT '';
    

1. 再次插入一条 NULL 数据，此时数据库会自动将其转换为空字符串：

    INSERT INTO user_profile (id, username, age) VALUES (4, NULL, 20);
    

1. 验证查询结果，现在 username 为空时不再是 NULL，而是空字符串，所有的逻辑和统计都能正常工作了。

注意： 只有在业务上确实存在“未知”情况（如订单金额未知、出生日期未知）时，才建议使用 NULL。对于状态、名称、金额等有明确含义的字段，尽量使用默认值。

小结

1. 含义不同：NULL 代表“未知”，不是 0 也不是空字符串。
2. 查询陷阱：查询 NULL 必须用 IS NULL，不能用 =。
3. 统计陷阱：COUNT(*) 统计总行数，COUNT(列名) 不统计 NULL。
4. 索引失效：NULL 值处理不当会导致索引无法使用，查询变慢。
5. 最佳实践：对于非必要的数据，建议设置默认值（如空字符串）以保持数据的一致性和索引的高效性。