MySQL:WHERE status NOT LIKE 'example%' 未返回 NULL 状态的结果

Question

我有一个如下所示的表格:

+---------+----------+-----------------+
| name    |   age    | status          |
+---------+----------+-----------------+
| Clark   |   25     | Example         |
+---------+----------+-----------------+
| Peter   |   28     | Example2        |
+---------+----------+-----------------+
| Waldo   |   37     | NULL            |
+---------+----------+-----------------+
| Tarzan  |   31     | Unknown         |
+---------+----------+-----------------+

当我执行这样的查询时:

SELECT * FROM records WHERE status NOT LIKE 'example%'

我得到:

+---------+----------+-----------------+
| name    |   age    | status          |
+---------+----------+-----------------+
| Tarzan  |   31     | Unknown         |
+---------+----------+-----------------+

如果我将查询更改为(请注意我删除了NOT):

SELECT * FROM records WHERE status LIKE 'example%'

然后我得到以下信息:

+---------+----------+-----------------+
| name    |   age    | status          |
+---------+----------+-----------------+
| Clark   |   25     | Example         |
+---------+----------+-----------------+
| Peter   |   28     | Example2        |
+---------+----------+-----------------+

我的问题是:沃尔多在哪里？

Answer 1

在 SQL 中，NULL 值无法参与大多数比较操作，包括 LIKE，相反，您必须使用 单独考虑 NULL 值>IS NULL 运算符:

WHERE (`status` NOT LIKE '%example%' OR `status` IS NULL)

这很愚蠢，但这就是生活。

这记录在此处:https://dev.mysql.com/doc/refman/5.7/en/working-with-null.html

You cannot use [...] comparison operators such as =, <, or <> to test for NULL. Because the result of any [...] comparison with NULL is also NULL, you cannot obtain any meaningful results from such comparisons.