Compare range of ip addresses with start and end ip address in MySQL

I have a MySQL table where I store IP ranges. It is setup in the way that I have the start address stored as a long, and the end address (and an id and some other data). Now I have users adding ranges by inputting a start and end ip address, and I would like to check if the new range is not already (partially) in the database.

I know I can do a between query, but that doesn't seem to work with 2 different column开发者_如何转开发s, and I also cannot figure out how to pass a range to compare it. Doing it in a loop in PHP is a possibility, but would with a range of e.g. 132.0.0.0-199.0.0.0 be quite a big amount of queries..

When you say you have the addresses stored as a long, I'm assuming you mean you've got them stored such that (say) 10.1.2.3 will be stored as 0x0a010203. In that case, to find if an address is already present, you can do:

SELECT ...
FROM ipranges
WHERE (<NEWADDR> >= startaddr)
    AND (<NEWADDR> <= endaddr)

and then if you get any rows back, the address is already in the table. (replace <NEWADDR> with the new address, of course!)

As for checking overlapping rows, that's only slightly more complicated:

SELECT ...
FROM ipranges
WHERE NOT ((<NEWENDADDR> < startaddr) OR (<NEWSTARTADDR> > endaddr))

i.e. the new range doesn't overlap the old range provided that either it starts after it, or ends before it.

Both start_address and end_address are longs? Then why not just convert the IP you're looking for to a long and do WHERE start_address <= $myip AND end_address >= $myip?

For a range, just do it three times in the where clause

WHERE (start_address <= $startAddress AND end_address >= $startAddress) 
   OR (start_address <= $endAddress AND end_address >= $endAddress) 
   OR (start_address >= $startAddress AND end_address <= $endAddress)

The first grouping finds ranges that encompass the start address. The second one finds ranges that encompass the end address. That means that there's still a possibility that the range that was inputted is a superset of a range in the db. That's what the third one checks for.

That should return you all the ranges which intersect your inputted range...

How about storing the IP addres ranges as decimal that way you can do simple checks using the BETWEEN.

4294967295 - 4294967040 -> FFFFFF00 - FFFFFFFF -> 255.255.255.0 - 255.255.255.255

What about something like:

SELECT * FROM addresses WHERE
    (start < $new_start AND end > $new_start) OR
    (start > $new_end AND end < $new_end);

That should give you all the existing addresses which will overlap the new address.