MySQL比较具有空值 [英] MySQL comparison with null value

问题描述

我在MySQL表中有一列称为CODE的列，该列可以为NULL.假设我有一些带有CODE ='C'的行，我想在我的选择结果集中忽略这些行.我的结果集中可以有CODE = NULL或CODE！='C'.

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.

以下查询不会返回CODE为NULL的行:

The following query does not return a row with CODE as 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'

我的问题是为什么只有CODE！='C'不会返回CODE = NULL的行?绝对"C"不是NULL.我们在这里将无值与字符进行比较.有人可以说明为什么它不能那样工作吗?

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?

推荐答案

在MySQL中，NULL被视为缺少的未知值"，而不是无值.看看此NULL上的MySQL参考 .

In MySQL, NULL is considered as a 'missing, unknown value', as opposed to no value. Take a look at this MySQL Reference on NULL.

与NULL的任何算术比较都不返回true或false，而是返回NULL.因此，NULL != 'C'返回NULL，而不是返回true.

Any arithmetic comparison with NULL does not return true or false, but returns NULL instead., So, NULL != 'C' returns NULL, as opposed to returning true.

任何带有'NULL'的算术比较都将返回false.要在SQL中进行检查:

Any arithmetic comparison with 'NULL' will return false. To check this in SQL:

SELECT IF(NULL=123,'true','false') 

要检查NULL值，我们需要使用IS NULL& IS NOT NULL运算符.

To check NULL values we need to use IS NULL & IS NOT NULL operator.

这篇关于MySQL比较具有空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！