How to select available IP address from mysql db table

Question

I have mysql table called ip_address with column called ip and it contains following data

192.168.1.52
192.168.1.7
192.168.1.21
192.168.1.107
. 
. 
.

my problem is how to get the available IP address from the subnet, I mean this subnet must contain the range from 192.168.1.1 to 192.168.1.255 but not all IPs were stored, so i managed to sort them by:

SELECT ip FROM ip_address ORDER BY INET_ATON(ip)

I want to retrieve all IPs which are not found in ip_address table from that range.

score 0 · Answer 1 · answered Oct 10 '17 at 17:31

Assuming you can create a table called numbers that includes (at least) all integers between 1 and 255, then you can use a query like this to find the unused ip addresses for a given subnet:

SELECT CONCAT('192.168.1.',numbers.n) as available_ip
FROM numbers
LEFT OUTER JOIN ip_address ON ip_address.ip = concat('192.168.1.',numbers.n)
WHERE numbers.n BETWEEN 1 AND 255
  AND ip_address.ip IS NULL
ORDER BY numbers.n;

How to select available IP address from mysql db table

1 Answers1