MySQL 与空值的比较
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9608639/
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
MySQL comparison with null value
提问by dev_musings
I have a column called CODE in a MySQL table which can be NULL. Say I have some rows with CODE='C' which I want to ignore in my select result set. I can have either CODE=NULL or CODE!='C' in my result set.
我在 MySQL 表中有一个名为 CODE 的列，它可以为 NULL。假设我有一些带有 CODE='C' 的行，我想在我的选择结果集中忽略它们。我的结果集中可以有 CODE=NULL 或 CODE!='C'。
The following query does not return a row with CODE as NULL:
以下查询不会返回 CODE 为 NULL 的行：
SELECT * from TABLE where CODE!='C'
But this query works as expected and I know it is the right way to do it.
但是这个查询按预期工作，我知道这是正确的方法。
SELECT * from TABLE where CODE IS NULL OR CODE!='C'
My question is why does having only CODE!='C' does not return rows where CODE=NULL? Definitely 'C' is not NULL. We are comparing no value to a character here. Can someone throw some light as why it doesn't work that way?
我的问题是为什么只有 CODE!='C' 不会返回 CODE=NULL 的行？绝对'C'不是NULL。我们在这里将没有价值与字符进行比较。有人可以解释为什么它不能那样工作吗？
回答by Sam DeHaan
In MySQL, NULLis considered as a 'missing, unknown value', as opposed to no value. Take a look at this MySQL Reference on NULL.
在 MySQL 中，NULL被视为“缺失的、未知的值”，而不是没有值。看看这个 MySQL Reference on NULL。
Any arithmetic comparison with NULLdoes not return true or false, but returns NULLinstead., So, NULL != 'C'returns NULL, as opposed to returning true.
任何算术比较NULL都不会返回真或假，而是返回NULL。因此，NULL != 'C'返回NULL，而不是返回真。
Any arithmetic comparison with 'NULL' will return false. To check this in SQL:
任何与 'NULL' 的算术比较都将返回 false。要在 SQL 中检查这一点：
SELECT IF(NULL=123,'true','false')
To check NULLvalues we need to use IS NULL& IS NOT NULLoperator.
要检查NULL值，我们需要使用IS NULL&IS NOT NULL运算符。
回答by Alan Fullmer
Based on my tests and the documentation here: http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html
基于我的测试和这里的文档：http: //dev.mysql.com/doc/refman/5.0/en/comparison-operators.html
You can compare null and get a boolean result using <=>
NOTE: it looks like NOT EQ operator, but it's EQ operator
您可以比较 null 并使用 <=> 获得布尔结果
注意：它看起来像 NOT EQ 运算符，但它是 EQ 运算符
For example:
例如：
select x <=> y;
or
select @x <=> @y;
This also compares string vs null, string vs string, etc.
这也比较了字符串与空值、字符串与字符串等。
回答by cornuz
In SQL, the NULL value is a special value, not comparable with any other one. The result of a direct comparison with a NULL is always NULL, although (unfortunately) you may find FALSE in some implementation.
在 SQL 中，NULL 值是一种特殊值，无法与任何其他值进行比较。与 NULL 直接比较的结果始终为 NULL，尽管（不幸的是）您可能会在某些实现中发现 FALSE。
To test a null value you should use IS NULLand IS NOT NULL.
要测试空值，您应该使用IS NULL和IS NOT NULL。
回答by Vasil Nikolov
SELECT *
FROM `table_name`
WHERE IFNULL(`column_name` != 'C', TRUE)
回答by Vipin Pandey
select * from user where application_id='1223333344' and name is null;
select * from user where application_id='1223333344' and name is null;
