Find available IP range in mysql table

Question

I have following sql. Works fine for searching IPs. But I also would like to be able so search

select * from table 
where (INET_ATON(ip) BETWEEN INET_ATON('10.12.77.1') AND INET_ATON('10.12.77.10') )
limit 30

results is fine

10.12.77.2
10.12.77.5
10.12.77.6

how can I get available IPs like

10.12.77.1
10.12.77.3
10.12.77.4
10.12.77.7
10.12.77.8
10.12.77.9
10.12.77.10

Is it possible to do in mysql, or should I do this in Java?

Thx

It ain't clear, what you are asking... Do you want spaces at the start of each ip? — Kamran Ahmed, May 11 '14 at 07:10
no no, I want complement of first result. It means all address in range - first result. — user2475153, May 11 '14 at 07:34
Applying `INET_ATON` on each row is inefficient. Consider storing the values numerically, i. e., the return values of `INET_ATON`. — Gumbo, May 11 '14 at 09:30
Generally, it's best to handle the issue of missing data in the presentation layer/application-level code — Strawberry, May 11 '14 at 09:54

score 1 · Answer 1 · answered Mar 05 '15 at 20:49

1

Did you try not between :

select * from table 
where (INET_ATON(ip) NOT BETWEEN INET_ATON('10.12.77.1') AND INET_ATON('10.12.77.10') )
limit 30

By the way your query is looking 1 to 10, so why limit 30 ? It should be 10.

Regards.

answered Mar 05 '15 at 20:49

EngineerCoder

1,445
15
30

score 0 · Answer 2 · answered May 11 '14 at 08:52

Apache Commons Net has a SubnetUtils class which is easy and useful for IP and Netmask calculations. You can initialize an object with valid netmask values and get SubnetInfo class to get various information.

SubnetUtils subnet = new SubnetUtils("10.12.77.1", "255.255.255.242");
SubnetInfo subnetInfo = subnet.getInfo();

Find available IP range in mysql table

2 Answers2