SQL query to deduce next available id in range-CodePudding

I am attempting to figure out the easiest way to find the next available id, in a particular range of ids, from a single MySQL table. Assume the following id result set from a trivial query:

SELECT id from `foo` fields the results

1,
2,
3,
10,
50
52,
53,
54,
60,
70,
80

I want to find the first available id between the range of 50 to 59. In this particular case, the answer would be 51. What's the best way to accomplish this with MySQL 5.7.x?

CodePudding user response：

Create another lookup table bar with enough id-values and use query:

select min(b.id) 
from bar b
 left join foo f on f.id=b.id 
where b.id between 50 and 59 and f.id is null;

If you cannot use another table, you can use a recursive CTE to mimic one:

with recursive bar (id) AS
(
  select 1
  union all
  select id   1 from bar where id < 100
)
select min(b.id) 
from bar b
 left join foo f on f.id=b.id 
where b.id between 50 and 59 and f.id is null;

CodePudding user response：

You can always use a left join to find if for n there is n 1 in the table, if it does not then there is a gap after n. For the edge case where other answers fail, we just use a trick:

set @a = 50;
set @b = 59;
select t1.id   1
from (
    select id from t union
    select @a - 1
) as t1
left join t as t2 on t1.id   1 = t2.id
where t1.id between @a - 1 and @b
and t2.id is null
order by 1