Find minimum not used value in mysql table

Question

I have a MySQL table like this:

+--------+-------+
| Server | Port  |
+--------+-------+
| 1      | 27001 |
| 2      | 27002 |
| 4      | 27004 |
+--------+-------+

How you can see - here was 27003 port - but it was deleted some time ago (just for example).

Is there some way to know the minimum value that is not used (higher than 27000 for example) in mysql table? Or there is no way and I need to make an separate table with all ports and column with "used" or "not used" ?

Situation explanation: I am creating game hosting website, and I can use auto-increment for ports, but I will have a lot of deleted/unused ports after some time, and I want to use all of them before increasing port range.

Answer 1

A quick search on Google for "first missing number from sequence mysql" gives this page of common MySQL queries.

It shows you how to find the first missing number from a sequence:

You have a table tbl(id int) with values (1,2,4,18,19,20,21), and you wish to find the first missing number in its sequence of id values:

SELECT t1.id+1 AS Missing 
FROM tbl AS t1 
LEFT JOIN tbl AS t2 ON t1.id+1 = t2.id 
WHERE t2.id IS NULL 
ORDER BY t1.id LIMIT 1; 

+---------+ 
| Missing | 
+---------+ 
|       3 | 
+---------+