MySQL - 从数字列表中选择那些在表的id字段中没有对应项的数字

Question

我有一个数字列表，比如{2,4,5,6,7}。 我有一张表foos，其中包括foos.ID，如{1,2,3,4,8,9}。

我想获取我的数字列表中，在foos表的ID字段中没有对应项的数字。

实现这个目标的一种方法是创建一个名为bars的表，将数字{2,4,5,6,7}加载到ID字段中。 然后，我会执行：

SELECT bars.* FROM bars LEFT JOIN foos ON bars.ID = foos.ID WHERE foos.ID IS NULL

但是，我想不用临时表也能完成此操作。

有人知道怎么做吗？

- SocialCensus

---

从上面的例子中，你需要得到5和6，对吗？ - Sunny Milenov

是的，我正在寻找5、6、7 - 抱歉，我本来想把它放在这里的。 - SocialCensus

Answer 1

这是一个相当普遍的问题：在不创建表的情况下动态生成关系。针对此问题的 SQL 解决方案相当麻烦。以下是使用派生表的示例：

SELECT n.id
FROM
  (SELECT 2 AS id 
   UNION SELECT 3 
   UNION SELECT 4 
   UNION SELECT 5 
   UNION SELECT 6 
   UNION SELECT 7) AS n
  LEFT OUTER JOIN foos USING (id)
WHERE foos.id IS NULL;

但这种方法并不很适用于大规模的情况，因为你可能有许多值而不仅仅是六个。构建一个需要每个值都使用一个UNION的长列表变得很繁琐。

另一种解决方案是保留一个通用的十位数字表，并将其反复用于多种目的。

CREATE TABLE num (i int);
INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

SELECT n.id
FROM 
  (SELECT n1.i + n10.i*10 AS id
   FROM num AS n1 CROSS JOIN num AS n10
   WHERE n1.i + n10.i*10 IN (2, 3, 4, 5, 6, 7)) AS n
  LEFT OUTER JOIN foos USING (id)
WHERE foos.id IS NULL;

我展示了内部查询生成从0到99的值，尽管对于这种情况并不必要。但是你的列表中可能有大于10的值。关键是，通过一个表num，你可以生成大量数字，而不必使用非常长的链，每个值一个UNION。此外，你可以在一个地方指定所需的值列表，这更方便和易读。

- Bill Karwin

---

遗憾的是，使用的数字大小和范围是随意的。这排除了你的第二个选择。第一个有点笨拙，而且在没有表的情况下生成关系并不是最好的办法。我真的希望能够使用一些基于集合的逻辑，但mysql的SET似乎比较弱。 - SocialCensus

这个解决方案对我很有效。如果你正在处理重复值，请使用 union all select。 - Seabass

八年过去了，这个问题仍然存在：)。 - zozo

现在是2023年，据我所知MySQL仍然没有一种优雅地制作关系值列表的方法。:facepalm: - craastad

@craastad，您可以使用CTE生成一系列数字，如此手册页面所示：https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive - Bill Karwin

Answer 2

我找不到一个不使用临时表解决你的具体问题的解决方案，但是可以使用子查询代替连接来执行查询的备选方法是:

SELECT bars.* FROM bars WHERE bars.ID NOT IN (SELECT ID FROM foos)

像其他海报一样，我最初写道：

->

跟其他的发帖者一样，我最开始写下了：

SELECT * FROM foos WHERE foos.ID NOT IN (2, 4, 5, 6, 7)

但后来我意识到这会产生与你期望相反的结果。

- Alnitak

---

没错，临时表是正确的方法。而且你说得对 - 我们读得太快了 :) 谢谢，我会删除我的帖子。 - Sunny Milenov

很遗憾，我宁愿不创建临时表……不过看起来这是最好的选择。谢谢！ - SocialCensus

Answer 3

如果您使用PHP，可以在不创建任何临时表的情况下使其工作。

SELECT ID FROM foos WHERE foos.ID IN (2, 4, 5, 6, 7)

您可以使用PHP的array_diff()函数将其转换为所需结果。如果您的列表（2,4,5,6,7）在名为$list的数组中，并且上述查询的结果在一个名为$result的数组中，则：

$no_counterparts = array_diff($list, $result);

...将返回您列表中所有没有在数据库表中对应的数字。虽然这个解决方案不会在查询中执行整个操作，但您需要在PHP中进行的后处理最小化，以获得您想要的结果，并且避免创建临时表可能是值得的。

- Alex Matulich

Answer 4

我曾经遇到过类似的问题。我的自增主键有一些缺失值，所以我首先找出了有多少个缺失值： select count(*) from node where nid > 1962。 将这个数字与最高值进行比较，就可以得到缺失的数量。然后我运行了这个查询： select n2.nid from node n1 right join node n2 on n1.nid = (n2.nid - 1) where n1.nid is null and n2.nid > 1962 这将找到非连续缺失记录的数量。它不会显示连续的缺失记录，我也不确定如何做到这一点，除非更改ON子句以允许更大的自由度（这将使JOIN表格大大增加）。 无论如何，这给了我五个结果，总共七个缺失中的另外两个保证与其中至少一个相邻。如果你有更多的缺失，你可能需要其他方法来找到剩下的缺失。

- Daniel

Answer 5

Alnitak的解决方案（以及您的）应该可以工作，我无法想到其他仅在SQL语言中有效的任何内容。

但是问题来了 - 您如何传递值列表？是否最好在调用代码中处理此问题 - 即请求ID并在调用代码中进行比较，这可能更适合处理此类操作的语言。

- Sunny

Answer 6

在寻找答案时，恰好到达了这里。早期的帖子都是针对MySQL 8之前的版本。由于MySQL自8.0.19版本开始引入了value语句，因此可以非常优雅地使用value语句以及与MySQL 8.0一起提供的CTE来解决问题。

步骤1： 结合CTE和value语句创建一组行，其中包含需要与表（此处为foo）进行比较的值。

with MyValues(val) as
(
  values row(2),row(4),row(5),row(6),row(7)
)

步骤2： 将CTE与表foo进行外连接，并过滤掉在与foo进行外连接后，来自CTE的空值行。

WITH myvalues(val)
     AS (VALUES ROW(2), ROW(4), ROW(5), ROW(6), ROW(7))
SELECT f.id
FROM   foo f
       LEFT OUTER JOIN myvalues m
                    ON f.id = m.val
WHERE  m.val IS NULL; 

轨迹

mysql> WITH myvalues(val)
    ->      AS (VALUES ROW(2), ROW(4), ROW(5), ROW(6), ROW(7))
    -> SELECT f.id
    -> FROM   foo f
    ->        LEFT OUTER JOIN myvalues m
    ->                     ON f.id = m.val
    -> WHERE  m.val IS NULL;
+------+
| id   |
+------+
|    1 |
|    3 |
|    8 |
|    9 |
+------+
4 rows in set (0.00 sec)

或者使用IN子句

mysql> WITH myvalues(val)
    ->      AS (VALUES ROW(2), ROW(4), ROW(5), ROW(6), ROW(7))
    -> SELECT f.id
    -> FROM   foo f
    -> WHERE  id NOT IN (SELECT val
    ->                   FROM   myvalues);
+------+
| id   |
+------+
|    1 |
|    3 |
|    8 |
|    9 |
+------+
4 rows in set (0.00 sec)

- rajorshi