MySQL 8.0 运算符深度对比：!=、<>、=、<=> 在 5 种 NULL 值场景下的行为差异

NULL 值是 SQL 中最令人困惑的概念之一，也是许多开发者容易踩坑的地方。MySQL 提供了多种比较运算符来处理 NULL 值，包括传统的 = 、 != / <> 以及专门用于 NULL 处理的 <=> 运算符。本文将深入分析这四种运算符在五种典型 NULL 值场景下的行为差异，帮助开发者编写更健壮的 SQL 查询。

1. NULL 值的特殊性

在深入比较运算符之前，我们需要理解 NULL 在 SQL 中的特殊含义：

  • NULL 表示"未知"或"不存在"的值，而不是零或空字符串
  • 任何与 NULL 的比较操作都会返回 NULL（除了 <=> 和专门的 IS NULL / IS NOT NULL ）
  • NULL 不等于 NULL（在标准 SQL 中），这与大多数编程语言不同
-- 演示 NULL 的特殊性
SELECT NULL = NULL;   -- 返回 NULL，不是 1
SELECT NULL != NULL;  -- 返回 NULL，不是 0

2. 四种比较运算符概述

MySQL 8.0 提供了四种主要的比较运算符来处理 NULL 值：

运算符 名称 NULL 处理能力 标准 SQL
= 等于 不能处理 NULL 是
!=/<> 不等于 不能处理 NULL 是
<=> NULL 安全等于 能处理 NULL MySQL 特有
IS NULL 是否为 NULL 专门处理 NULL 是

3. 五种 NULL 值场景下的行为对比

3.1 WHERE 条件中的 NULL 比较

这是最常见的 NULL 值处理场景。我们来看四种运算符在 WHERE 子句中的表现：

-- 测试数据准备
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

INSERT INTO users VALUES 
(1, 'Alice', 25),
(2, 'Bob', NULL),
(3, NULL, 30),
(4, 'Charlie', 40);

-- 场景1: 查找 age = NULL 的记录
SELECT * FROM users WHERE age = NULL;      -- 错误方式，返回空集
SELECT * FROM users WHERE age <=> NULL;    -- 正确方式，返回 id=2 的记录
SELECT * FROM users WHERE age IS NULL;     -- 标准方式，返回 id=2 的记录

-- 场景2: 查找 age != 25 的记录
SELECT * FROM users WHERE age != 25;       -- 只返回 id=4，不包含 NULL 记录
SELECT * FROM users WHERE age != 25 OR age IS NULL; -- 正确方式，返回 id=2,4

关键发现 ：

  • = 和 != / <> 在遇到 NULL 时会返回 NULL，WHERE 子句只接受 TRUE 条件
  • <=> 可以正确处理 NULL 比较，但它是 MySQL 特有的语法
  • IS NULL 是标准 SQL 中检查 NULL 的正确方式

3.2 JOIN 条件中的 NULL 处理

JOIN 操作中的 NULL 比较是另一个常见陷阱：

-- 测试数据准备
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
);

INSERT INTO departments VALUES
(1, 'Sales', 101),
(2, 'Marketing', NULL),
(3, 'IT', 103);

CREATE TABLE managers (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO managers VALUES
(101, 'John'),
(102, 'Sarah'),
(103, 'Mike');

-- 内连接: NULL 不会匹配
SELECT d.name, m.name 
FROM departments d
JOIN managers m ON d.manager_id = m.id;  -- 不返回 Marketing 部门

-- 使用 <=> 也不能解决，因为 JOIN 条件需要两边都 NULL 才匹配
SELECT d.name, m.name 
FROM departments d
JOIN managers m ON d.manager_id <=> m.id; -- 仍然不匹配，因为 m.id 不是 NULL

-- 正确做法是使用 LEFT JOIN 和显式 NULL 检查
SELECT d.name, IFNULL(m.name, 'No Manager') 
FROM departments d
LEFT JOIN managers m ON d.manager_id = m.id;

关键发现 ：

  • 在 JOIN 条件中， = 和 <=> 对 NULL 的处理差异不大
  • 要包含 NULL 匹配的记录，应该使用 OUTER JOIN 而不是依赖比较运算符

3.3 UNION 和 DISTINCT 中的 NULL 处理

UNION 和 DISTINCT 操作对 NULL 值的处理也有其特殊性：

-- 测试数据
CREATE TABLE t1 (x INT);
CREATE TABLE t2 (x INT);

INSERT INTO t1 VALUES (1), (NULL), (2);
INSERT INTO t2 VALUES (2), (NULL), (3);

-- UNION 中的 NULL 处理
SELECT x FROM t1
UNION
SELECT x FROM t2;
-- 结果包含: 1, NULL, 2, 3 (NULL 被视为相同值)

-- 使用 = 和 <=> 的比较
SELECT 1 WHERE NULL = NULL;    -- 无结果
SELECT 1 WHERE NULL <=> NULL;  -- 返回 1

关键发现 ：

  • 在集合操作中，所有 NULL 值被视为相等
  • 这与 = 运算符的行为不同，但与 <=> 的行为一致

3.4 聚合函数中的 NULL 处理

聚合函数通常忽略 NULL 值，但 COUNT 的行为有所不同：

-- 测试数据
CREATE TABLE sales (
    id INT PRIMARY KEY,
    amount DECIMAL(10,2),
    region VARCHAR(50)
);

INSERT INTO sales VALUES
(1, 100.00, 'North'),
(2, NULL, 'North'),
(3, 150.00, 'South'),
(4, NULL, NULL);

-- 聚合函数行为
SELECT 
    COUNT(*),        -- 计数所有行: 4
    COUNT(amount),   -- 计数非 NULL 值: 2
    SUM(amount),     -- 忽略 NULL: 250.00
    AVG(amount)      -- 忽略 NULL: 125.00
FROM sales;

-- 使用 = 和 <=> 的分组差异
SELECT 
    region, 
    COUNT(*) as count_all,
    SUM(amount) as total
FROM sales
GROUP BY region;
-- 分组结果包含 NULL 组

SELECT 
    IF(region <=> NULL, 'Unknown', region) as region_group,
    COUNT(*) as count_all
FROM sales
GROUP BY region_group;

关键发现 ：

  • 聚合函数通常忽略 NULL 值（COUNT(*) 除外）
  • GROUP BY 将 NULL 视为一个独立的分组
  • <=> 可以在分组前统一处理 NULL 值

3.5 子查询和 EXISTS 中的 NULL

子查询中的 NULL 处理可能导致意外的结果：

-- 测试数据
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    category_id INT
);

INSERT INTO products VALUES
(1, 'Laptop', 1),
(2, 'Phone', 1),
(3, 'Desk', NULL),
(4, 'Chair', 2);

CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO categories VALUES
(1, 'Electronics'),
(2, 'Furniture');

-- IN 子查询中的 NULL
SELECT * FROM products 
WHERE category_id IN (SELECT id FROM categories);  -- 不返回 category_id=NULL 的记录

-- NOT IN 子查询中的 NULL 陷阱
SELECT * FROM products 
WHERE category_id NOT IN (SELECT id FROM categories WHERE id < 10);
-- 不返回任何记录，因为子查询可能包含 NULL

-- 使用 <=> 解决
SELECT * FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM categories c 
    WHERE p.category_id <=> c.id
);

关键发现 ：

  • NOT IN 子查询如果可能返回 NULL 会导致整个条件为 NULL
  • <=> 可以在相关子查询中安全地比较 NULL
  • EXISTS 通常比 IN 更安全，特别是涉及 NULL 值时

4. 运算符选择决策树

根据上述分析，我们总结出以下决策树来帮助选择正确的比较运算符：

  1. 需要比较两个可能为 NULL 的值？

    • 是 → 使用 <=>
    • 否 → 进入下一步

  2. 需要检查一个值是否为 NULL？

    • 是 → 使用 IS NULL 或 IS NOT NULL
    • 否 → 进入下一步

  3. 需要标准的不等于比较？

    • 是 → 使用 != 或 <> （两者等效）
    • 否 → 使用 =

  4. 在 JOIN 条件中涉及 NULL？

    • 考虑使用 OUTER JOIN 而不是依赖比较运算符

  5. 在子查询中使用 NOT IN？

    • 确保子查询不会返回 NULL，或改用 NOT EXISTS

5. 性能考虑

除了功能差异外，这些运算符在性能上也有细微差别：

  • <=> 通常比 = 稍微慢一点，因为它需要额外的 NULL 检查逻辑
  • IS NULL 可以使用索引，但 column = NULL 不能
  • != 和 <> 在性能上完全等效
-- 创建索引测试
CREATE INDEX idx_age ON users(age);

-- 使用索引的情况
EXPLAIN SELECT * FROM users WHERE age IS NULL;  -- 可能使用索引
EXPLAIN SELECT * FROM users WHERE age <=> NULL; -- 可能使用索引
EXPLAIN SELECT * FROM users WHERE age = NULL;   -- 不会使用索引

在实际应用中，除非处理大量数据，否则这些性能差异通常可以忽略不计。更重要的还是选择语义正确的运算符。

# 数据库开发
Logo
https://edu.csdn.net/learn/39067/627173?utm_source=2019755004

汇聚全球AI编程工具，助力开发者即刻编程。

更多推荐

cover

四天蒸发近2000亿，宇树科技股价近乎“腰斩”；苹果举办库克告别派对：9月1日正式卸任CEO；Anthropic两款新Claude模型曝光 | 极客头条

avatar AI编程社区
cover

Claude Code反超GitHub Copilot登顶第一、90%程序员已用上Agent，最新AI编码调查报告来了

avatar AI编程社区
cover

如何用好 Claude Code 写后端代码

avatar AI编程社区