MySQL 与空值的比较

2021-11-20 00:00:00 sql null mysql isnull

我在 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.

相关文章