MySQL中ENUM字段WHERE查询未包含NULL行的异常问题咨询

问题场景 #

先看以下操作及现象：

建表SQL：

CREATE TABLE employee (
  id INT, 
  name VARCHAR(20)
);

插入初始数据：

INSERT INTO employee VALUES(1, 'John');

添加ENUM类型列：

ALTER TABLE employee
    ADD COLUMN type1 ENUM ('REGULAR', 'PART_TIME');

插入新数据：

INSERT INTO employee VALUES(2, 'Dave', 'REGULAR');
INSERT INTO employee VALUES(3, 'Bob', 'PART_TIME');

此时表中数据为：

'1', 'John', NULL
'2', 'Dave', 'REGULAR'
'3', 'Bob', 'PART_TIME'

执行查询 SELECT * FROM employee where type1 != 'REGULAR'，预期返回第1、3行，但实际仅返回第3行；尝试 SELECT * FROM employee where type1 != 'REGULAR' or type1 = NULL;，结果依旧不符合预期。

1. 第一个查询逻辑未达预期的原因 #

这是SQL三值逻辑的特性导致的：

• 在SQL中，NULL代表“未知值”，任何普通比较运算符（=、!=、<、>等）和NULL进行比较时，结果既不是TRUE也不是FALSE，而是UNKNOWN。
• WHERE子句只会筛选出条件结果为TRUE的行，UNKNOWN和FALSE都会被过滤。
• 你尝试的type1 = NULL同样遵循这个规则，结果是UNKNOWN，所以type1 != 'REGULAR' OR type1 = NULL的整体结果还是UNKNOWN（UNKNOWN OR UNKNOWN仍为UNKNOWN），自然不会包含type1为NULL的行。

2. 正确的查询写法 #

要包含NULL值的行，需要用专门判断NULL的运算符，或者利用NULL安全比较特性，以下是几种可行写法：

写法一：用IS NULL显式判断

SELECT * FROM employee 
WHERE type1 != 'REGULAR' OR type1 IS NULL;

写法二：利用NULL安全比较的否定形式

MySQL的<=>是NULL安全的相等运算符，a <=> b会在a和b都为NULL时返回TRUE，否则和普通=一致。我们可以用它的否定来筛选：

SELECT * FROM employee 
WHERE NOT (type1 <=> 'REGULAR');

写法三：用COALESCE转换NULL值

把NULL转换成一个不属于ENUM范围的值（比如'OTHER'），再进行比较：

SELECT * FROM employee 
WHERE COALESCE(type1, 'OTHER') != 'REGULAR';

内容的提问来源于stack exchange，提问作者Ganesh Satpute