Question

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 columns, 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..

Answer 1

当你说这些地址是long时,我假定你指的是把这些地址储存起来,(say)10.1.2.3将储存为0x0a010203。在这种情况下,如果发现地址已经存在,你可以做到:

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

如果你回头来,地址已经摆在桌面上。 (replace <NEWADDR> with the new Address, 当然!)

在检查重叠的浏览量方面,这只是稍加复杂:

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

i. 即,如果新范围从后开始,或者在其之前结束,则新范围就没有重叠。

Answer 2

两者都是起步的:处理和最终解决时间长? 那么,为什么不把你重新寻找一个漫长的手法改为“条码”。 WHERE开始_address <=$myip and end_address >=$myip

就范围而言,在条款的三度上,情况就是如此。

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

第一组发现的范围包括起始地址。第二种情况是涵盖最终地址的范围。这意味着,目前仍有可能将所投入的范围放在b中。这是第三点检查。

这应当使你回到贯穿你们投入范围的所有范围......

Answer 3

如何储存IP添加物,其含义是,你可以采用BETWEEN进行简单的检查。

4294967295 - 4294967040 -> FFFF00 - FFFFFF-> 255.255.255.0 - 255.255.255

Answer 4

诸如:

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

这应当给你所有现有地址,这些地址将重叠。

友情链接