mysql - 奇怪的 MySQL 行为，似乎是一个 SQL 错误

Question

我在 MySQL 中遇到了一个非常奇怪的行为，这看起来像是某种奇怪的错误。我知道将一个人的错误归咎于久经考验的工具是很常见的，但我已经讨论了一段时间。

我有 2 个表，I 有 2797 条记录，C 有 1429 条记录。C 引用 I.
我想删除 I 中所有未被 C 使用的记录，所以我在做:

select * from i where id not in (select id_i from c);

返回 0 条记录，考虑到每个表中的记录数，这在物理上是不可能的。我也非常确定该查询是正确的，因为它与我在过去 2 小时内一直使用的相同类型的查询来清理具有孤立记录的其他表。

让事情变得更奇怪......

select * from i where id in (select id_i from c);

有效，并给我带来了 1297 条我不想删除的记录。
因此，IN 有效，但 NOT IN 无效。

更糟的是:

select * from i where id not in (
  select i.id from i inner join c ON i.id = c.id_i
);

这确实有效，尽管它应该等同于第一个查询(此时我只是在尝试疯狂的东西)。
唉，我不能使用这个查询来删除，因为我在子查询中使用的是要从中删除的同一个表。

我假设此时我的数据库中的某些已损坏。

万一重要，这些都是没有任何外键的 MyISAM 表，无论如何，我在我的开发机器和生产服务器中运行相同的查询并得到相同的结果，所以无论有什么损坏都可能幸存下来mysqldump/source cycle，这听起来很奇怪。

关于可能出现的问题有什么想法，或者更重要的是，我如何解决/解决这个问题？

谢谢!
丹尼尔

Answer 1

我猜你有一行包含 NULL 值。当存在 NULL 值时，IN 和 NOT IN 的行为方式可能出乎您的意料。来自manual :

To comply with the SQL standard, IN returns NULL not only if the expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL.

NOT NULL 的值仍然是 NULL，不是您希望的那样。

这是一个简化示例的行为演示:

CREATE TABLE c (id_i INT NULL);
INSERT INTO c (id_i) VALUES
(1),
(2),
(NULL);

CREATE TABLE i (id INT NOT NULL);
INSERT INTO i (id) VALUES
(1),
(2),
(3);

SELECT * FROM i WHERE id NOT IN (SELECT id_i FROM c); -- returns nothing
SELECT * FROM i WHERE id IN (SELECT id_i FROM c);     -- returns 1,2
SELECT * FROM i WHERE id NOT IN (                     -- returns 3
  SELECT i.id FROM i INNER JOIN c ON i.id = c.id_i
);