mysql中NULL值的判断与IS NULL操作的基本语法

mysql中不能用= NULL判断NULL值，必须使用IS NULL或IS NOT NULL；它们是专用谓词，可用于WHERE、HAVING及if/CASE等布尔上下文，且不可加括号。

MySQL中不能用= NULL判断NULL值

在MySQL里，NULL不是值，而是“缺失值”的标记，所以它不满足任何等于（=）或不等于（!=、）比较。写WHERE col = NULL永远返回空结果集，哪怕该列全是NULL。

常见错误现象：

• 查询select * FROM user WHERE name = NULL查不到任何记录
• 用IF(col = NULL, 'yes', 'no')总是返回'no'

正确做法是使用IS NULL或IS NOT NULL——它们是专门设计来处理NULL语义的谓词，不是比较操作符。

IS NULL和IS NOT NULL的基本语法与位置

这两个操作符只能用于WHERE子句、HAVING子句，以及支持布尔表达式的上下文（如IF()、CASE），不能直接赋值或用于SELECT列表中作为字段别名（除非包裹在函数里）。

基本写法：

SELECT * FROM orders WHERE shipped_at IS NULL; SELECT count(*) FROM users WHERE email IS NOT NULL;

注意：

• IS NULL后面**不能加括号**，IS (NULL)是语法错误
• 可以和逻辑运算符连用：WHERE status = 'pending' AND created_at IS NULL
• 在CASE中必须用IS NULL，不能写WHEN col = NULL

NULL在函数和聚合中的行为容易被忽略

很多函数对NULL输入有隐式处理逻辑，比如SUM()、AVG()、COUNT()会自动跳过NULL；而COUNT(*)统计所有行，COUNT(col)只统计col IS NOT NULL的行。

典型陷阱：

• SELECT AVG(score) FROM exam —— 如果score有5条NULL，它们不参与计算，也不拉低平均值
• SELECT IF(age IS NULL, 'unknown', age) AS age_label FROM person —— 必须显式用IS NULL，IF(age = NULL, ...)无效
• CONCAT(first_name, ' ', last_name) —— 任一参数为NULL，整个结果为NULL，需用COALESCE()兜底

索引对IS NULL查询是否有效？

是的，只要列上有普通B+树索引（包括单列索引或联合索引的最左前缀），IS NULL条件就能走索引扫描（type: ref或range），但前提是该列允许NULL（即定义时没加NOT NULL约束）。

验证方式：

EXPLAIN SELECT * FROM product WHERE category_id IS NULL;

如果category_id有索引且允许NULL，key列会显示索引名，rows明显小于全表行数。但如果该列定义为NOT NULL，优化器可能直接忽略IS NULL条件（因为永假），甚至报Impossible WHERE警告。

额外提醒：联合索引中，只有当IS NULL出现在最左列且后续列也满足范围/等值条件时，才能充分利用索引深度。