-1

I am trying to find a way to identify empty range of primary key.

I have a table that have for primary key only numbers, named id.

I am trying to fill the gaps when receiving batch of products.

My column id (primary) has numbers that can be following each other but for each product type we jump to another thousands and some employees are not following the rule of taking next free spot, they find an empty and use it!

I would need to find something that would have a functionality similar to:

select product.id
from product
where product.id >= x
and next product.id > x + y
  • x would be the last used product
  • y is the amount of product i have to fill in this batch.

For example; if x is my starting point and has a value of 25000 and y = 50, the first time 50 unused number is reach is from 26600 to 27500.

The result would give me 25999... which would be the last product entered.

It is mandatory that batch product have consecutive numbers.

Is there any query that can give that result?

thank you in advance!

Thomas Jensen
  • 2,138
  • 2
  • 25
  • 48
Francois Lemieux
  • 59
  • 1
  • 11
  • 2
    "i am trying to fill the gaps when receiving batch of products." - the fact you want to do that means you are probably doing something wrong. – Mitch Wheat Oct 10 '14 at 02:22
  • see http://stackoverflow.com/questions/1824600/fragmentation-of-ids-auto-increment-column-in-mysql – Leo Oct 10 '14 at 02:24

1 Answers1

0

this query should produce the first suitable gap using 50 as the size required. note that you should query the gap and insert the batch in the same transaction, to prevent someone else uses the same gap at the same time.

select a.id+1
  from yourtable a 
 where not exists (select id from yourtable where id between a.id+1 and a.id+50)
order by a.id
limit 1

if the table is empty this query will return no rows.

beware that filling the gaps in primary keys in a context with high demand and many simultaneous inserts would be a bad idea since it would serialize all transactions.

1010
  • 1,779
  • 17
  • 27