MySQL 空值（NULL）处理深度指南

MySQL 空值（NULL）处理深度指南

⚠️ NULL 处理的八大关键注意事项

1. NULL 的本质特性

SELECT NULL = NULL;   -- NULL (不是true!)
SELECT NULL IS NULL;  -- 1 (true)
SELECT 1 + NULL;      -- NULL

核心认知：

• NULL 表示未知或不存在的值
• 任何与 NULL 的运算结果都是 NULL
• NULL 不等于空字符串 '' 或 0

2. 比较运算符陷阱

-- 错误方式（返回空结果集）
SELECT * FROM employees WHERE bonus = NULL;

-- 正确方式
SELECT * FROM employees WHERE bonus IS NULL;
SELECT * FROM employees WHERE bonus IS NOT NULL;

3. 聚合函数中的 NULL 忽略

CREATE TABLE sales (amount INT);
INSERT INTO sales VALUES (100), (NULL), (200);

SELECT 
  SUM(amount)   -- 300 (忽略NULL)
  AVG(amount)   -- 150 (300/2)
  COUNT(amount) -- 2 (非NULL行数)
FROM sales;

4. 空值在索引中的处理

-- 普通索引不包含NULL值
ALTER TABLE products ADD INDEX idx_price (price);

-- 查询NULL值无法使用索引
SELECT * FROM products WHERE price IS NULL; -- 全表扫描

-- 解决方案：函数索引（MySQL 8.0+）
ALTER TABLE products 
  ADD INDEX idx_price_null ((IF(price IS NULL, 1, NULL)));

5. UNIQUE 约束的特殊性

CREATE TABLE users (
  email VARCHAR(100) UNIQUE -- 允许多个NULL
);

INSERT INTO users VALUES (NULL); -- 成功
INSERT INTO users VALUES (NULL); -- 成功！(非重复)

6. 空字符串 vs NULL

7. 外键约束中的 NULL

CREATE TABLE departments (
  id INT PRIMARY KEY
);

CREATE TABLE employees (
  dept_id INT,
  FOREIGN KEY (dept_id) REFERENCES departments(id)
);

-- 允许插入NULL（表示无部门）
INSERT INTO employees (dept_id) VALUES (NULL); -- 有效

8. 排序中的 NULL 行为

SELECT * FROM products ORDER BY price;
-- NULL 默认排在最前（ASC）或最后（DESC）

-- 控制NULL排序位置
SELECT * FROM products 
ORDER BY 
  CASE WHEN price IS NULL THEN 1 ELSE 0 END, -- NULL在后
  price;

🛠️ 空值处理函数详解

1. 基础空值转换

2. 条件判断中的 NULL

-- CASE处理NULL
SELECT 
  CASE
    WHEN bonus IS NULL THEN '无奖金'
    WHEN bonus > 0 THEN CONCAT('奖金:', bonus)
    ELSE '其他'
  END AS bonus_status
FROM employees;

3. 安全计算函数

-- 避免NULL污染计算结果
SELECT 
  COALESCE(salary, 0) + COALESCE(bonus, 0) AS total
FROM employees;

🔍 空值检测技术

1. 多列空值检测

-- 检测所有联系信息均为空
SELECT * FROM customers
WHERE 
  phone IS NULL 
  AND email IS NULL 
  AND address IS NULL;

2. 动态空值检查

-- 使用JSON函数检测任意空值
SELECT * FROM orders
WHERE JSON_CONTAINS_PATH(order_info, 'one', '$**.null');

3. 空值比例分析

-- 统计每列的空值率
SELECT 
  COUNT(*) AS total,
  COUNT(phone)/COUNT(*) AS phone_null_rate,
  COUNT(email)/COUNT(*) AS email_null_rate
FROM customers;

🚀 高级空值处理技巧

1. 空值数据清洗

-- 三步清洗法
UPDATE employees
SET 
  bonus = COALESCE(bonus, 0),
  department = IFNULL(department, '未分配'),
  hire_date = COALESCE(hire_date, '2023-01-01')
WHERE id IN (
  SELECT id FROM employees 
  WHERE bonus IS NULL 
     OR department IS NULL
     OR hire_date IS NULL
);

2. 窗口函数中的 NULL

-- 跳过空值计算排名
SELECT 
  name, score,
  RANK() OVER (ORDER BY score DESC NULLS LAST) AS rank
FROM students;

3. 空值优化索引策略

-- 对包含NULL的列创建过滤索引
ALTER TABLE products
ADD INDEX idx_price_not_null (price)
WHERE price IS NOT NULL; -- MySQL 8.0+

-- 虚拟列索引
ALTER TABLE employees
ADD COLUMN bonus_flag TINYINT 
  AS (IF(bonus IS NULL, 0, 1)) VIRTUAL,
ADD INDEX idx_bonus_flag (bonus_flag);

💡 最佳实践总结

1. 设计阶段预防

-- 建表时合理设置默认值
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL, -- 禁止NULL
  last_login DATETIME DEFAULT CURRENT_TIMESTAMP -- 默认值
);

2. 查询优化原则

-- 避免 WHERE 条件中的函数
SELECT * FROM orders 
WHERE COALESCE(total,0) > 100; -- 全表扫描 ❌

SELECT * FROM orders 
WHERE total > 100 OR total IS NULL; -- 可能使用索引 ✅

3. 安全更新策略

-- 先备份再更新NULL
CREATE TABLE tmp_employees AS 
SELECT * FROM employees WHERE bonus IS NULL;

UPDATE employees 
SET bonus = 0 
WHERE bonus IS NULL;

4. 空值迁移方案

-- 将NULL迁移到归档表
INSERT INTO inactive_users
SELECT * FROM users 
WHERE last_login IS NULL 
  AND created_at < '2020-01-01';

DELETE FROM users 
WHERE last_login IS NULL 
  AND created_at < '2020-01-01';

5. 应用程序处理

// Java示例：结果集处理
while (rs.next()) {
  BigDecimal bonus = rs.getBigDecimal("bonus");
  if (rs.wasNull()) {
    bonus = BigDecimal.ZERO;
  }
}

📊 空值处理决策树

graph TD
A[遇到NULL值] --> B{需要处理吗？}
B -->|是| C[设计阶段]
B -->|否| D[保留NULL]
C --> E[设置NOT NULL约束]
C --> F[设置DEFAULT值]
D --> G[查询使用IS NULL]
G --> H[聚合函数自动忽略]
D --> I[外键允许NULL]

掌握这些空值处理技术，将极大提高你的数据库开发能力和数据质量保障水平！