MySQL从表中获取缺少的ID

I have this table in MySQL, for example:

我在MySQL中有这个表，例如：

ID | Name
1  | Bob
4  | Adam
6  | Someguy

If you notice, there is no ID number (2, 3 and 5).

如果您注意到，则没有ID号（2,3和5）。

How can I write a query so that MySQL would answer the missing IDs only, in this case: "2,3,5" ?

如何编写查询以便MySQL只回答缺少的ID，在这种情况下：“2,3,5”？

4 个解决方案

#1

SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
    FROM testtable AS a, testtable AS b
    WHERE a.id < b.id
    GROUP BY a.id
    HAVING start < MIN(b.id)

Hope this link also helps http://www.codediesel.com/mysql/sequence-gaps-in-mysql/

希望这个链接也有助于http://www.codediesel.com/mysql/sequence-gaps-in-mysql/

#2

A more efficent query:

一个更有效的查询：

SELECT (t1.id + 1) as gap_starts_at, 
       (SELECT MIN(t3.id) -1 FROM my_table t3 WHERE t3.id > t1.id) as gap_ends_at
FROM my_table t1
WHERE NOT EXISTS (SELECT t2.id FROM my_table t2 WHERE t2.id = t1.id + 1)
HAVING gap_ends_at IS NOT NULL

#3

Above queries will give two columns so you can try this to get the missing numbers in a single column

上面的查询将提供两列，因此您可以尝试使用此列来获取单个列中缺少的数字

select start from 
(SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
    FROM sequence AS a, sequence AS b
    WHERE a.id < b.id
    GROUP BY a.id
    HAVING start < MIN(b.id)) b
UNION
select c.end from (SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
    FROM sequence AS a, sequence AS b
    WHERE a.id < b.id
    GROUP BY a.id
    HAVING start < MIN(b.id)) c order by start;

#4

To add a little to Ivan's answer, this version shows numbers missing at the beginning if 1 doesn't exist:

为了给Ivan的答案添加一点，如果1不存在，这个版本会显示开头缺少的数字：

SELECT 1 as gap_starts_at,
       (SELECT MIN(t4.id) -1 FROM testtable t4 WHERE t4.id > 1) as gap_ends_at
FROM testtable t5
WHERE NOT EXISTS (SELECT t6.id FROM testtable t6 WHERE t6.id = 1)
HAVING gap_ends_at IS NOT NULL limit 1
UNION
SELECT (t1.id + 1) as gap_starts_at, 
       (SELECT MIN(t3.id) -1 FROM testtable t3 WHERE t3.id > t1.id) as gap_ends_at
FROM testtable t1
WHERE NOT EXISTS (SELECT t2.id FROM testtable t2 WHERE t2.id = t1.id + 1)
HAVING gap_ends_at IS NOT NULL;

#1

SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
    FROM testtable AS a, testtable AS b
    WHERE a.id < b.id
    GROUP BY a.id
    HAVING start < MIN(b.id)