Find the minimum unused value in mysql table

Question

I have a MySQL table as follows:

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

As you can see, port 27003 was here, but it was deleted some time ago (for example, for example).

Is there a way to find out the minimum value that is not used (e.g. more than 27000) in the mysql table? Or is there no way, and I need to create a separate table with all ports and columns with “used” or “not used”?

Explanation of the situation: I am creating a website for hosting games, and I can use auto-increment for ports, but after a while I will have many remote / unused ports, and I want to use all of them before expanding the range of ports.

Answer 1

A quick google search for “the first missing number from the mysql sequence ” gives this page of general MySQL queries .

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

You have a tbl (id int) table with values ​​(1,2,4,18,19,20,21) and you want to find the first missing number in your 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 personally verified.

Answer 2

 SELECT * FROM tableName t, (SELECT @last:= NULL) dm WHERE (@last:= IF(@last IS NULL OR @last + 1 = ID, ID, NULL)) IS NULL ORDER BY ID LIMIT 1