为什么在MySQL的关键字不考虑NULL值(Why MYSQL IN keyword not con

我使用下面的查询：

select count(*) from Table1 where CurrentDateTime>'2012-05-28 15:34:02.403504' and Error not in ('Timeout','Connection Error');

令人惊讶地，这种说法不列入包括具有错误值的行作为NULL.My意图是错误的值作为“超时”（或）“连接错误”仅过滤行。 我需要给一个附加条件（或错误是NULL）来获取正确的结果。

为什么MYSQL筛选出具有NULL值的结果吗？ 我认为，在关键字将返回布尔结果（1/0），现在我明白，有些MYSQL关键字犯规返回布尔值，它可能返回NULL太....但为什么治疗NULL作为特殊？

Answer 1:

这个 ：

Error not in ('Timeout','Connection Error');

在语义上等同于：

Error <> 'TimeOut' AND Error <> 'Connection Error'

关于空比较规则适用于过。 因此，如果误差值为NULL，则数据库不能使表达真实的。

要解决，你可以这样做：

COALESCE(Error,'') not in ('Timeout','Connection Error');

或者更好的是：

Error IS NULL OR Error not in ('Timeout','Connection Error');

或更多更好的：

 CASE WHEN Error IS NULL THEN 1
 ELSE Error not in ('Timeout','Connection Error') THEN 1
 END = 1

OR不短路情况下，可以在某种程度上短路查询

---

也许一个具体的例子可以说明，为什么NULL NOT IN expression返回任何内容：

鉴于这样的数据： http://www.sqlfiddle.com/#!2/0d5da/11

create table tbl
(
  msg varchar(100) null,
  description varchar(100) not null
  );


insert into tbl values
('hi', 'greet'),
(null, 'nothing');

而这样做的表达：

select 'hulk' as x, msg, description 
from tbl where msg not in ('bruce','banner');

这将输出“HI”而已。

该NOT IN被翻译为：

select 'hulk' as x, msg, description 
from tbl where msg <> 'bruce' and msg <> 'banner';

NULL <> 'bruce'无法确定，即使不是真的，甚至没有假

NULL <> 'banner'无法确定，甚至不真不伪连

所以，空值表达式，有效地下决心：

can't be determined AND can't bedetermined

事实上，如果你的RDBMS支持在SELECT（如MySQL和PostgreSQL）布尔，你可以看到为什么： http://www.sqlfiddle.com/#!2/d41d8/828

select null <> 'Bruce' 

这将返回null。

这将返回空太：

select null <> 'Bruce' and null <> 'Banner'

由于您使用NOT IN ，这基本上是一个和表达。

NULL AND NULL

结果为NULL。 所以，这就像你正在做的一个： http://www.sqlfiddle.com/#!2/0d5da/12

select * from tbl where null

没有什么会被退回

Answer 2:

因为空是未定义所以空不等于空。 你总是必须明确地处理空。

Answer 3:

IN返回NULL如果在左手侧的表达式为NULL 。 为了得到NULL值，你必须做的：

select count(*) from Table1 where CurrentDateTime>'2012-05-28 15:34:02.403504' and (Error not in ('Timeout','Connection Error') or Error is null);

Answer 4:

IN返回一个三价BOOLEAN （它接受NULL作为值）。 NOT IN返回的三价否定IN ，以及否定NULL是NULL 。

试想一下，我们与所有数字的表1到1,000,000的id和此查询：

SELECT  *
FROM    mytable
WHERE   id IN (1, 2, NULL)

或等值：

SELECT  *
FROM    mytable
WHERE   id = ANY
             (
             SELECT  1
             UNION ALL
             SELECT  2
             UNION ALL
             SELECT  NULL
             )

该谓词返回TRUE为1和2和NULL对于所有其它的值，因此1和2被返回。

在其oppposite：

SELECT  *
FROM    mytable
WHERE   id NOT IN (1, 2, NULL)

， 要么

SELECT  *
FROM    mytable
WHERE   id <> ALL
             (
             SELECT  1
             UNION ALL
             SELECT  2
             UNION ALL
             SELECT  NULL
             )

，断言返回FALSE为1和2以及NULL对于所有其他值，所以什么都不会返回。

需要注意的是布尔非不仅改变了操作符（ = ，以<>但量词太（ ANY到ALL ）。

Answer 5:

@迈克尔布恩的答案是我的情况下，正确的答案，但让我简化原因。

@迈克尔说，在他的职位：

---

错误未在（“超时”，“连接错误”）;

在语义上等同于：

错误<>“超时”和错误<>“连接错误”

关于空比较规则适用于过。 因此，如果误差值为NULL，则数据库不能使表达真实的。

而在[1]我发现这句话这印证了他最重要的语句理解为什么失败，NULL。 在规范（“规范”）[1]你会：“如果有一个或两个参数是NULL，比较的结果是NULL，除了空安全<=>相等比较运算符”

所以，是的，事情是可悲的是Mysql的获取在这种情况下丢失。 我认为，mysql的设计师不应该这样做，因为当我比较2为NULL时，MySQL应该能够看到它们是不同的，而不是简单地扔错的结果。 举例来说，我所做的：

select id from TABLE where id not in (COLUMN WITH NULLS);

那么它抛出空的结果。 但。 如果我做

select id from TABLE where id not in (COLUMN WITH OUT NULLS);

它显示了正确的结果。 因此，使用IN操作符的时候，你必须筛选出空值。 这不是我作为一个用户所期望的行为，但它在规范文件[1]。 我认为，语言和技术应该是简单的，因为你应该能够无需读取规格推断感。 真正，2是null不同，我应该是一个负责控制，并采取更高的抽象水平的错误的照顾，但与特定的值进行比较空当MySQL应该抛出错误的结果。

对于规格的参考文献：[1] http://dev.mysql.com/doc/refman/5.6/en/type-conversion.html

Answer 6:

Sorry for posting twice in the same forum, but I want to illustrate another example:

I agree with @Wagner Bianchi in [2] in this forum when he says: << It’s very trick when dealing with data and subqueries>>

Moreover, this should NOT be the behavior, I think Mysql's designers are mistaken when they made this decision documented in [1]. The design should be different. Let me explain: You know that when comparing

select (2) not in (1, 4, 3);
    you will get:
        +----------------------+
        | (2) not in (1, 4, 3) |
        +----------------------+
        |                    1 |
        +----------------------+
        1 row in set (0.00 sec)

BUT if in the list you have at least one NULL then:

select (2) not in (1, NULL, 3);
    throws:
        +-------------------------+
        | (2) not in (1, NULL, 3) |
        +-------------------------+
        |                    NULL |
        +-------------------------+
        1 row in set (0.00 sec)
    This is pretty absurd.

We are not the first ones in getting confused by this. See [2]

References:

[1] http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in

[2] http://blog.9minutesnooze.com/sql-not-in-subquery-null/comment-page-1/#comment-86954

文章来源: Why MYSQL IN keyword not considering NULL values