数据库中的 NULL 值有哪些坑，为什么它会让索引和统计失效？
学完这篇，你将掌握 NULL 在数据库中的特殊含义，理解为什么使用 = 查询 NULL 会失效，以及为什么它会导致统计信息和索引性能下降，并学会如何避免这些坑。
一、理解 NULL 的本质：它不是 0 也不是空字符串
这一步要创建一个测试表并插入数据，通过对比展示 NULL 到底代表什么。在数据库中，NULL 的意思是“未知”或“无值”，它既不是数字 0，也不是空字符串 ''，甚至不占用存储空间。
- 打开你的数据库客户端（如 MySQL Workbench、Navicat 或命令行），执行以下 SQL 命令创建测试表：
CREATE TABLE user_profile (
id INT PRIMARY KEY,
username VARCHAR(20),
age INT
);
- 插入几条测试数据，注意观察
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);
- 查询所有数据，确认 NULL 的存在：
SELECT * FROM user_profile;
注意： 这里插入的 NULL 是真正的数据库 NULL 值，不是字符串 "NULL"。
二、逻辑陷阱：为什么 = 查询不到 NULL
这一步要演示最常见的坑，即试图用 = 号去匹配 NULL 值，结果会一无所获。因为数据库无法判断一个未知的值是否等于某个具体的值，所以 WHERE col = NULL 永远不会返回结果。
- 尝试使用
=号查询名为 NULL 的用户，观察结果：
SELECT * FROM user_profile WHERE username = NULL;
- 你会发现查询结果为空（0 行）。这是因为数据库把
NULL = NULL视为 False。
注意： 在 SQL 中判断字段是否为空，必须使用
IS NULL，判断非空则使用IS NOT NULL。
三、统计陷阱：COUNT 函数的歧义
这一步要展示 COUNT(*) 和 COUNT(字段名) 的区别，这会导致统计结果出错。COUNT(*) 统计表中所有行的总数，不管值是什么；而 COUNT(字段名) 只统计该字段有值的行数，NULL 值会被跳过。
- 同时执行两个查询命令，对比结果：
-- 统计总行数
SELECT COUNT(*) FROM user_profile;
-- 统计有名字的行数
SELECT COUNT(username) FROM user_profile;
- 你会发现
COUNT(username)的结果比COUNT(*)少 1，因为第 2 条数据的username是 NULL。
注意： 如果你想要统计所有用户，永远使用
COUNT(*)，不要用COUNT(列名)。
四、性能陷阱：为什么索引会失效
这一步要解释 NULL 对索引的影响，这是性能问题的根源。在 B-Tree 索引结构中，NULL 值通常不会被存储在索引树中，或者被特殊处理。这会导致两个问题：一是查询 col = NULL 时，数据库无法利用索引快速定位，只能进行全表扫描；二是当索引列包含大量 NULL 时，索引的效率会大打折扣。
- 为
username字段创建一个普通索引：
CREATE INDEX idx_username ON user_profile(username);
- 执行查询并查看执行计划，判断是否使用了索引：
EXPLAIN SELECT * FROM user_profile WHERE username = NULL;
- 你会发现执行计划中显示
type为ALL（全表扫描），说明索引失效了。
注意： 虽然
WHERE col IS NULL通常可以利用索引，但混合使用OR和IS NULL（例如WHERE username = 'Tom' OR username IS NULL）往往会破坏索引的使用，导致性能下降。
五、解决方案：如何避免 NULL 的坑
这一步要介绍最佳实践，通过设置默认值来规避 NULL 带来的问题。通常建议在定义表结构时，给字段设置一个明确的默认值（如空字符串 '' 或 0），而不是留空。
- 修改表结构，给
username字段设置默认值为空字符串：
ALTER TABLE user_profile MODIFY username VARCHAR(20) DEFAULT '';
- 再次插入一条 NULL 数据，此时数据库会自动将其转换为空字符串：
INSERT INTO user_profile (id, username, age) VALUES (4, NULL, 20);
- 验证查询结果，现在
username为空时不再是 NULL，而是空字符串，所有的逻辑和统计都能正常工作了。
注意： 只有在业务上确实存在“未知”情况（如订单金额未知、出生日期未知）时，才建议使用 NULL。对于状态、名称、金额等有明确含义的字段，尽量使用默认值。
小结
- 含义不同：NULL 代表“未知”，不是 0 也不是空字符串。
- 查询陷阱：查询 NULL 必须用
IS NULL，不能用=。 - 统计陷阱：
COUNT(*)统计总行数，COUNT(列名)不统计 NULL。 - 索引失效：NULL 值处理不当会导致索引无法使用，查询变慢。
- 最佳实践：对于非必要的数据，建议设置默认值（如空字符串）以保持数据的一致性和索引的高效性。
转载请注明出处，版权归原作者所有。