How to find missing IDs in MySQL table [duplicate]

Question

I have a table where I have a row id that is my primary key. It is a table with ~250 thousand rows. Usually, the id should be a sequential number. Now I want to find out, if there is a number missing in the sequence of numbers.

Example: The table contains the ids

1, 2, 4, 6, 7, 8, 9, 10

That means, in the sequence of numbers from 1 to 10, the numbers 3 and 5 are missing.

How would a MySQL query look like that only returns the missing ids? Is there a way to increment a counter and compare if that id exists?

Sure, just compare COUNT(*) with MAX(id); that's assuming you don't have master-master replication. — Ja͢ck
– Ja͢ck, Commented Jul 16, 2014 at 13:18
@juergend This table is kind of an OLAP table. So I want to know the missing ids to check if all "parent items" are being analyzed. — dialogik
– dialogik, Commented Jul 16, 2014 at 13:45
@Jack Doesn't return the missing ids, but I like this approach. Nice and easy! — dialogik
– dialogik, Commented Jul 16, 2014 at 13:45
So you rely on an auto_increment to provide referential integrity while not actually using foreign keys and you rely on the notion that auto_increment is sequential (hint: it's purpose is not to be sequential). tl;dr - your architecture is, by definition, wrong. — N.B.
– N.B., Commented Jul 16, 2014 at 13:50
@N.B. You're right: My question does not make sense because the original items are not sequential (because items may have been deleted in that original sequence). Thanks for that hint. I was already wondering why COUNT(*) <> MAX(id). — dialogik
– dialogik, Commented Jul 16, 2014 at 13:52

dialogik · Accepted Answer · 2014-07-16 13:44:10Z

0

Using a Linux script populate a table test(id:auto-increment) up to your max value of your_table. Then execute something like

select id from test where id not in (select id from your_table)

The output will be the missing.

edited Jul 16, 2014 at 13:44

dialogik

9,59218 gold badges79 silver badges122 bronze badges

answered Jul 16, 2014 at 13:25

Antonios

1908 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dialogik Over a year ago

Yeah, this helps. Unfortunately it is not dynamically; I have to update this help table each time my original table changes.

Antonios Over a year ago

I am not sure for this statement: Select id+1 from your_table Where not EXISTS (select distinct id from your_table)

Antonios Over a year ago

Also this post might help you: stackoverflow.com/questions/4340793/…

Collectives™ on Stack Overflow

How to find missing IDs in MySQL table [duplicate]

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related