Mysql，在序列中找到缺失的数字[重复]

Question

This question already has an answer here:

• SQL: find missing IDs in a table 17 answers

I have a huge table with a couple hundred thousand records. They all have a unique id for the first column though some are missing in the sequence of numbers.

I am trying to show a list of the numbers that are missing individually.

So for example:

10029
10032
10034
10036

I am trying to get it to show:

10030
10031
10033
10035

I found this query but it seems to be missing numbers when there's a range:

SELECT t1.id+1 AS Missing 
FROM data AS t1 
LEFT JOIN data AS t2 
    ON t1.id+1 = t2.id 
WHERE t2.id IS NULL 

Result:

10030
10033

As you can see, 10031 and 10035 are missing from that list.

</div>

Answer 1

Perhaps something like this as a starting point. Not tested, but might just work

SELECT @min := (SELECT min(id) FROM yourtable); // get the smallest ID in the table
SELECT @last := @min; // cache the min value for the where clause

SELECT id, id - @last AS difference, @last := id
FROM yourtable
WHERE id > @min
ORDER BY id ASC
HAVING difference > 1

it won't give you the individual missing ids, but it'll tell you where the gaps are, and how big they are.