Find the minimum value that is not used in the mysql table

advertisements

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.


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 |
+---------+

Disclaimer: Not tested personally.