Inventing a bike or finding a missing ID value in a MySQL table

Developers and administrators of systems based on sql data, for sure, faced with the task - to get the missing (missing) value in a number of id records of the table. For example, the contract number, the serial number of the document, phone number, ip address, etc. When working with MySQL, this trivial task is disproportionately resource intensive.

For example, we have a pool of internal company phone numbers from 2001 to 2999 and a table with numbers issued from them for employees:

• t1.phone
• 2001
• 2002
• 2003
• 2004
• 2005
• 2009
• 2015
• 2016

We need to find the first free value (in this case, 2006) to allocate the next number to the next employee. If there are no free values, then you need to select the next one from the range. Familiar task? Solutions that the Internet abounds in boil down to two principles:

1) Do a brute force loop: for example, in SQL, create a cursor CUR i + 1 from 2001 to 2999 and make queries

SELECT t1.phone FROM t1 WHERE phone = i 

to empty value. The cycle can be done with any external software, the meaning of the principle does not change.

2) The second principle is to use the LEFT (OUTER) JOIN sequence 2001 ... 2009 with the table t1 (WHERE t1.phone IS NULL of course), or the table t1 with itself with a shift to the step:

 SELECT MIN(t1.phone)+1 FROM t1 LEFT JOIN t1 AS diff ON (t1.phone = diff.phone+1) WHERE diff.phone IS NULL 

Another option using IN

 SELECT ... WHERE phone NOT IN (....) 

I do not consider it at all because of bulkiness.
')
On small data volumes, both solutions (and even with IN) work fine, and with a large number of records, these solutions are either resource-intensive, or time-consuming, or both.
It depends on the server's capacity and database settings, but in any case, if one goes through a million records or squeezes such a table, even on a powerful server, execution will take considerable time.

I wanted to solve the problem quickly, without straining the server, and, preferably, in one request. In one, not in one, but that's what happened:

 /*  */ select 2000,2999 into @num,@maxid; select min(f.id) /*     ""  union */ from (select s.num, min(s.num) /*    */ id from ( select /*  ,    .,      ,   */ @num:=@num+1 num, /*    */ r.id from t1 as r order by id ) as s where /*     */ s.id != s.num /*       ,         - min */ /*    ,           null,    */ union select @num+1 num, if(@num+1<@maxid,@num+1,null) id ) as f where /*    union */ f.id is not null limit 1; 

Compared to joins, simple selects are performed hundreds of times faster.

It is clear that such a solution is known, but, strangely enough, it was not possible to find it on the Internet, so I want to share these simple “bikes”.

UPD.
The perfect decision was suggested in a personal stepmex party
Without any additional construction of numbering and comparison of series, he elegantly solved the problem through (SELECT 1 .....) IS NULL
A great find, I believe:

 SELECT (`t1`.`phone`+1) as `empty_phone` FROM `t1` WHERE ( SELECT 1 FROM `t1` as `st` WHERE `st`.`phone` = (`t1`.`phone` + 1) ) IS NULL ORDER BY `t1`.`phone` LIMIT 1