8

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.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
splattru
  • 608
  • 1
  • 9
  • 19

2 Answers2

16

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 | 
+---------+
AamirR
  • 11,672
  • 4
  • 59
  • 73
Caspar
  • 7,039
  • 4
  • 29
  • 41
0

I appreciate this was answered in 2011 - as of MySQL 5.7 this seems to be more readable and (as per explain on a table of 15M rows) faster than the left join solution:

select min(id+1) from tbl
where id+1 not in (select id from tbl)

Beware as the above will give you max(id)+1 as well, but you can exclude it at virtually no extra cost:

select min(id+1) from tbl
where id+1 not in (select id from tbl)
and id < (select max(id) from tbl)
Gergely
  • 365
  • 2
  • 7