Mysql, finding missing numbers in a sequence

Question

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:

I am trying to get it to show:

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.

This question should help: http://stackoverflow.com/questions/1389605/sql-find-missing-ids-in-a-table — mcriecken, Jul 25 '13 at 15:00
If you can create a temporary table containing all the IDs from `min(id)` to `max(id)`, you can do a left join between them. — Barmar, Jul 25 '13 at 15:01
Is there a way I can do this within the query without creating another table? I am trying to do this as simple as possible so I can use it within a PHP query. I saw another example that used: SELECT a.order_id+1 AS start, MIN(b.order_id) - 1 AS end — Scott, Jul 25 '13 at 15:25

score 0 · Answer 1 · answered Jul 25 '13 at 15:04

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.

Mysql, finding missing numbers in a sequence

1 Answers1