MySQL where 子句中的 NULL 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6827010/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
NULL values in where clause
提问by caspermc
i've got a table "bla" like this:
我有一个像这样的表“bla”：
[id] [name] [fk]
1 test 4
2 foo 5
3 bar NULL
if i do the sql query
如果我做 sql 查询
SELECT * FROM bla WHERE fk <> 4
i only get the record with the id 2. i don't get the record with id 3 where fk is null. I thought NULL != 4. Seems that this is wrong.
我只得到 id 2 的记录。我没有得到 id 3 的记录，其中 fk 为空。我认为 NULL != 4。似乎这是错误的。
Why is this so?
为什么会这样？
回答by Mat
NULLdoesn't compare equal to anything. You'll need to accept nulls explicitly:
NULL不等于任何东西。您需要明确接受空值：
where fk <> 4 or fk is null;
See Working with NULLfor more information about NULLhandling.
有关处理的更多信息，请参阅使用 NULLNULL。
回答by MD Sayem Ahmed
Because NULLstands for UNKNOWN, and when you compare a value with UNKNOWN, the result will always be false.
因为NULL代表UNKNOWN，并且当您将一个值与 进行比较时UNKNOWN，结果将始终为假。
Take a look at this comparisons -
看看这个对比——
NULL = NULL -- false, since both are unknown, so the truth value of this expression can't be determined.
NULL = 4 -- false
4 = 4 -- true, since both values are known.
If you want to fetch the records containing NULL, you need to re-write your query this way -
如果要获取包含 的记录NULL，则需要以这种方式重新编写查询 -
where fk <> 4
OR fk is null;
For more information, see Wikipedia.
有关更多信息，请参阅维基百科。
回答by Oded
NULLis special in that it represents an "unknown" value. This can't be compared to numbers (or any other value for that matter), hence the result -
NULL特殊之处在于它代表一个“未知”值。这无法与数字（或任何其他与此相关的值）进行比较，因此结果 -
Is NULL<> 4? The answer is - don't know. Is 4 different from an unknown value?
是NULL<> 4？答案是——不知道。4 与未知值不同吗？
Try this instead:
试试这个：
SELECT * FROM bla WHERE fk <> 4 OR FK IS NULL
回答by Naltharial
NULL is not a value, but rather the unknown absence of a value. If you'd like to test for NULL, you have to do so explicitly by using IS NULLand IS NOT NULL. For example, NULL will test FALSEeven against NULL itself. So, working with NULLis only done with the aforementioned functions (and ISNULL()). Your query could be rewritten as
NULL 不是值，而是值的未知缺失。如果您想测试 NULL，则必须使用IS NULLand显式执行此操作IS NOT NULL。例如，即使针对 NULL 本身，NULL 也会测试FALSE。因此，使用 NULL只能使用上述函数（和ISNULL()）。您的查询可以改写为
SELECT * FROM bla WHERE fk <> 4 OR fk IS NULL
回答by AlexanderMP
How about
怎么样
SELECT * FROM bla WHERE NOT (fk = 4)
Logic:
逻辑：
NULL = 4 --false
5 = 4 --false
4 = 4 --true
NOT (NULL = 4) --true
NOT (5 = 4) --true
NOT (4 = 4) --false
回答by Srikanth Kattam
Following statement should help:
以下陈述应该有所帮助：
SELECT * FROM bla WHERE COALESCE(fk,0) <> 4
SELECT * FROM bla WHERE COALESCE(fk,0) <> 4
