MySQL NULL 值处理 - MySQL 教程 | 小傅哥技术教程

NULL 值概念

在 MySQL 中，NULL 表示"未知"或"缺失"的值，它不等于空字符串('')、数字0或任何其他值。NULL 值在数据库操作中有特殊的行为和处理规则。

                    NULL 值的特点：
                    NULL 不等于任何值，包括它自己
任何与 NULL 的算术运算结果都是 NULL
NULL 值在排序中有特定的位置
聚合函数通常忽略 NULL 值
需要使用特殊的操作符来检测 NULL

                

NULL 值检测

IS NULL 和 IS NOT NULL

-- 检查 NULL 值
SELECT * FROM table_name WHERE column_name IS NULL;

-- 检查非 NULL 值
SELECT * FROM table_name WHERE column_name IS NOT NULL;
                

注意：不能使用 = NULL 或 != NULL 来检测 NULL 值，这些表达式总是返回 NULL（即 false）。

错误和正确的 NULL 检测

-- 错误的方式（不会返回预期结果）
SELECT * FROM users WHERE email = NULL;     -- 错误
SELECT * FROM users WHERE email != NULL;    -- 错误
SELECT * FROM users WHERE email <> NULL;    -- 错误

-- 正确的方式
SELECT * FROM users WHERE email IS NULL;     -- 正确
SELECT * FROM users WHERE email IS NOT NULL; -- 正确
                

示例数据准备

-- 创建包含 NULL 值的测试表
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),
    salary DECIMAL(10,2),
    bonus DECIMAL(10,2),
    hire_date DATE
);

-- 插入包含 NULL 值的测试数据
INSERT INTO employees VALUES
(1, '张三', 'zhangsan@email.com', '13800138001', 8000.00, 1000.00, '2020-01-15'),
(2, '李四', NULL, '13800138002', 6000.00, NULL, '2019-03-20'),
(3, '王五', 'wangwu@email.com', NULL, 9500.00, 1500.00, '2021-06-10'),
(4, '赵六', NULL, NULL, NULL, NULL, '2018-11-05'),
(5, '钱七', 'qianqi@email.com', '13800138005', 7500.00, NULL, NULL);
                

NULL 值查询

查找 NULL 值

-- 查找邮箱为空的员工
SELECT id, name, email FROM employees WHERE email IS NULL;
                

id	name	email
2	李四	NULL
4	赵六	NULL

查找非 NULL 值

-- 查找有邮箱的员工
SELECT id, name, email FROM employees WHERE email IS NOT NULL;
                

多列 NULL 检查

-- 查找邮箱和电话都为空的员工
SELECT id, name, email, phone 
FROM employees 
WHERE email IS NULL AND phone IS NULL;

-- 查找邮箱或电话为空的员工
SELECT id, name, email, phone 
FROM employees 
WHERE email IS NULL OR phone IS NULL;

-- 查找所有联系方式都不为空的员工
SELECT id, name, email, phone 
FROM employees 
WHERE email IS NOT NULL AND phone IS NOT NULL;
                

NULL 值处理函数

IFNULL() 函数

IFNULL(expr1, expr2) - 如果 expr1 不是 NULL，返回 expr1，否则返回 expr2。

-- 使用默认值替换 NULL
SELECT 
    id,
    name,
    IFNULL(email, '未提供邮箱') as email,
    IFNULL(phone, '未提供电话') as phone,
    IFNULL(salary, 0) as salary
FROM employees;
                

COALESCE() 函数

COALESCE(expr1, expr2, ...) - 返回第一个非 NULL 的表达式。

-- 返回第一个非空的联系方式
SELECT 
    id,
    name,
    COALESCE(email, phone, '无联系方式') as contact
FROM employees;

-- 计算总收入（薪资+奖金）
SELECT 
    id,
    name,
    salary,
    bonus,
    COALESCE(salary, 0) + COALESCE(bonus, 0) as total_income
FROM employees;
                

NULLIF() 函数

NULLIF(expr1, expr2) - 如果 expr1 = expr2，返回 NULL，否则返回 expr1。

-- 将空字符串转换为 NULL
SELECT 
    id,
    name,
    NULLIF(email, '') as email  -- 如果 email 是空字符串，返回 NULL
FROM employees;

-- 避免除零错误
SELECT 
    id,
    name,
    salary / NULLIF(bonus, 0) as salary_bonus_ratio
FROM employees;
                

ISNULL() 函数

ISNULL(expr) - 如果 expr 是 NULL，返回 1，否则返回 0。

-- 检查字段是否为 NULL
SELECT 
    id,
    name,
    email,
    ISNULL(email) as email_is_null,
    ISNULL(phone) as phone_is_null
FROM employees;
                

NULL 值在运算中的行为

算术运算

-- NULL 参与的算术运算结果都是 NULL
SELECT 
    id,
    name,
    salary,
    bonus,
    salary + bonus as total,           -- 如果 bonus 是 NULL，结果是 NULL
    salary * 1.1 as salary_increase,   -- 如果 salary 是 NULL，结果是 NULL
    bonus / 12 as monthly_bonus        -- 如果 bonus 是 NULL，结果是 NULL
FROM employees;
                

字符串连接

-- NULL 参与的字符串连接
SELECT 
    id,
    name,
    email,
    phone,
    CONCAT(name, ' - ', email) as name_email,  -- 如果 email 是 NULL，结果是 NULL
    CONCAT(IFNULL(email, ''), ' | ', IFNULL(phone, '')) as contact_info
FROM employees;
                

比较运算

-- NULL 的比较运算
SELECT 
    id,
    name,
    salary,
    bonus,
    salary > 7000 as high_salary,      -- 如果 salary 是 NULL，结果是 NULL
    bonus > 1000 as high_bonus,        -- 如果 bonus 是 NULL，结果是 NULL
    salary = bonus as salary_eq_bonus  -- 如果任一为 NULL，结果是 NULL
FROM employees;
                

NULL 值在聚合函数中的行为

聚合函数忽略 NULL

-- 聚合函数自动忽略 NULL 值
SELECT 
    COUNT(*) as total_employees,        -- 计算所有行数
    COUNT(email) as employees_with_email, -- 只计算非 NULL 的 email
    COUNT(phone) as employees_with_phone,  -- 只计算非 NULL 的 phone
    COUNT(salary) as employees_with_salary, -- 只计算非 NULL 的 salary
    AVG(salary) as avg_salary,          -- 平均值忽略 NULL
    SUM(bonus) as total_bonus,          -- 求和忽略 NULL
    MAX(salary) as max_salary,          -- 最大值忽略 NULL
    MIN(bonus) as min_bonus             -- 最小值忽略 NULL
FROM employees;
                

GROUP BY 中的 NULL

-- NULL 值会被分为一组
SELECT 
    IFNULL(bonus, 'No Bonus') as bonus_group,
    COUNT(*) as employee_count,
    AVG(salary) as avg_salary
FROM employees 
GROUP BY bonus;
                

NULL 值在排序中的行为

默认排序行为

-- MySQL 中 NULL 值在排序时排在最前面（升序）
SELECT id, name, salary, bonus
FROM employees 
ORDER BY bonus;  -- NULL 值排在最前面

SELECT id, name, salary, bonus
FROM employees 
ORDER BY bonus DESC;  -- NULL 值仍然排在最前面
                

控制 NULL 值排序位置

-- 将 NULL 值排在最后
SELECT id, name, salary, bonus
FROM employees 
ORDER BY bonus IS NULL, bonus;

-- 将 NULL 值排在最前
SELECT id, name, salary, bonus
FROM employees 
ORDER BY bonus IS NOT NULL, bonus;

-- 使用 IFNULL 控制排序
SELECT id, name, salary, bonus
FROM employees 
ORDER BY IFNULL(bonus, 0) DESC;  -- 将 NULL 当作 0 排序
                

NULL 值在 JOIN 中的行为

-- 创建部门表
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

-- 添加部门字段到员工表
ALTER TABLE employees ADD COLUMN dept_id INT;

-- 更新部门信息（有些员工没有部门）
UPDATE employees SET dept_id = 1 WHERE id IN (1, 2);
UPDATE employees SET dept_id = 2 WHERE id = 3;
-- id 为 4, 5 的员工 dept_id 保持为 NULL

INSERT INTO departments VALUES (1, '技术部'), (2, '销售部'), (3, '人事部');

-- JOIN 操作中 NULL 值不会匹配
SELECT e.id, e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;
                

NULL 值的最佳实践

                    处理 NULL 值的最佳实践：
                    在表设计时明确哪些字段允许 NULL
为重要字段设置 NOT NULL 约束
使用适当的默认值减少 NULL 的使用
在查询中正确处理 NULL 值
在应用程序中验证和处理 NULL 值

                

表设计建议

-- 好的表设计示例
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20),                    -- 可选字段，允许 NULL
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,       -- 使用默认值而不是 NULL
    profile_image VARCHAR(255) DEFAULT 'default.jpg'  -- 使用默认值
);
                

查询中的 NULL 处理

-- 在查询中妥善处理 NULL
SELECT 
    id,
    name,
    COALESCE(email, phone, '无联系方式') as contact,
    IFNULL(salary, 0) + IFNULL(bonus, 0) as total_compensation,
    CASE 
        WHEN salary IS NULL THEN '薪资待定'
        WHEN salary < 5000 THEN '低薪'
        WHEN salary < 8000 THEN '中薪'
        ELSE '高薪'
    END as salary_level
FROM employees;
                

常见错误和陷阱

常见错误：

使用 = NULL 或 != NULL 检测 NULL 值
忽略 NULL 值在算术运算中的影响
不理解聚合函数对 NULL 的处理
在 JOIN 条件中忽略 NULL 值的行为

三值逻辑

-- MySQL 使用三值逻辑：TRUE, FALSE, NULL
SELECT 
    1 = 1,        -- TRUE
    1 = 2,        -- FALSE
    1 = NULL,     -- NULL
    NULL = NULL,  -- NULL（不是 TRUE！）
    
    TRUE AND NULL,   -- NULL
    FALSE AND NULL,  -- FALSE
    TRUE OR NULL,    -- TRUE
    FALSE OR NULL,   -- NULL
    
    NOT NULL;        -- NULL
                

WHERE 子句中的 NULL

-- WHERE 子句只返回条件为 TRUE 的行
-- 条件为 FALSE 或 NULL 的行都不会返回

-- 这个查询不会返回 salary 为 NULL 的行
SELECT * FROM employees WHERE salary > 5000;

-- 这个查询也不会返回 salary 为 NULL 的行
SELECT * FROM employees WHERE NOT (salary > 5000);

-- 要包含 NULL 值，需要显式检查
SELECT * FROM employees 
WHERE salary > 5000 OR salary IS NULL;
                

实际应用场景

1. 数据清理

-- 查找数据不完整的记录
SELECT id, name,
    CASE 
        WHEN email IS NULL THEN 'Missing Email'
        WHEN phone IS NULL THEN 'Missing Phone'
        WHEN salary IS NULL THEN 'Missing Salary'
        ELSE 'Complete'
    END as data_status
FROM employees
WHERE email IS NULL OR phone IS NULL OR salary IS NULL;
                

2. 报表生成

-- 生成员工信息报表，处理缺失数据
SELECT 
    name,
    IFNULL(email, 'N/A') as email,
    IFNULL(phone, 'N/A') as phone,
    CONCAT('$', FORMAT(IFNULL(salary, 0), 2)) as salary,
    IFNULL(DATE_FORMAT(hire_date, '%Y-%m-%d'), 'Unknown') as hire_date
FROM employees
ORDER BY name;
                

3. 条件统计

-- 统计各种数据完整性情况
SELECT 
    COUNT(*) as total_employees,
    COUNT(email) as has_email,
    COUNT(*) - COUNT(email) as missing_email,
    COUNT(phone) as has_phone,
    COUNT(*) - COUNT(phone) as missing_phone,
    COUNT(salary) as has_salary,
    COUNT(*) - COUNT(salary) as missing_salary
FROM employees;
                

❓ MySQL NULL 值处理