sql - 'in' 和 'not in' 计数不相加 - 怎么了？

Question

我有一些服务器。其中一些已分配 ip。我想弄清楚有多少人没有。显然有比分配 ips 更多的服务器，但我的数据库告诉我没有没有分配 ips 的服务器......

我已经无计可施了。我的数据库是否以某种奇怪的方式损坏了？

SELECT COUNT(*) 
  FROM server

...返回:

+----------+
| count(*) |
+----------+
|    23088 | 
+----------+
1 row in set (0.00 sec)

这个:

SELECT COUNT(*) 
  FROM server 
 WHERE server_id IN (SELECT DISTINCT(server_id) 
                       FROM ips)

...返回:

+----------+
| count(*) |
+----------+
|    13811 | 
+----------+
1 row in set (0.01 sec)

这个:

SELECT COUNT(*) 
  FROM server 
 WHERE server_id NOT IN (SELECT DISTINCT(server_id) 
                           FROM ips);

...返回:

+----------+
| count(*) |
+----------+
|        0 | 
+----------+
1 row in set (0.02 sec)

结果已被编辑以保护罪犯，但你明白了。

• 所有表都是 InnoDB。
• Check table 在这两个表上均返回 ok。

编辑:感谢您提出使用 LEFT JOIN 的建议。这肯定确认问题是 MySQL 的错误。

mysql> SELECT count(s.server_id) FROM server s LEFT JOIN ips i on s.server_id = i.server_id WHERE i.server_id IS NULL;
+--------------------+
| count(s.server_id) |
+--------------------+
|               9277 | 
+--------------------+
1 row in set (0.04 sec)

因为 9277 + 13811 = 23088，这意味着所有没有 ips 的服务器 + 所有有 ips 的服务器确实 == 所有服务器。

我已安排在下周初升级 mysql 服务器。请继续关注。

Answer 1

什么版本的MySQL？ < 5.0.25/5.1.12 中似乎有一个错误可能是罪魁祸首:

Bug #21282 : NOT IN, 超过 1000 返回不正确的结果与 INDEX:

Using a SELECT ... WHERE some_field NOT IN (...) and then 1000 or more values in the NOT IN part causes the server to return incorrect results if there is an INDEX/UNIQUE key on some_field. Less than 1000 criteria works correctly.