搜索存储为整数的部分IP地址 [英] Search on partial IP addresses stored as integers

问题描述

我目前有一个包含IP地址的MySQL数据库.在搜索表单上，客户端希望在部分IP地址上进行搜索，并且弹出(也许)很多结果.我目前将IP地址存储在mysql中为unsigned int.我正在使用PHP 5.2，因此无法访问PHP 5.7及其INET6_NTOA函数.

I currently have a MySQL database that includes IP addresses. On a search form, the client wants to search on a partial IP address and have (perhaps) many results pop up. I am currently storing the IP addresses in mysql as unsigned int. I am using PHP 5.2, so do not have access to PHP 5.7 and its INET6_NTOA function.

当前的数据库有50,000多条记录，并且还在继续增长，所以我不想将所有IP都转换为点分表示法，然后进行匹配-似乎有点笨拙.

The current database has over 50,000 records and continues to grow, so I don't want to have to convert all the IP's to dotted notation, then do a match - that seems a bit unwieldy.

我有更好的方法来搜索部分IP地址吗?

Is there a better way for me to search on a partial IP address?

推荐答案

实际上，无符号整数列已经是在部分IP地址上搜索匹配项的最有效方法！请不要浪费您的精力或CPU时间来转换回点分符号或在某种字符串列上进行LIKE搜索.

Actually, the unsigned integer column is already the most efficient way to search for matches on partial ip addresses! Please don't waste your energy nor CPU time on converting back to dotted notation or going for a LIKE search on some kind of string column.

有几种记下部分IP地址的方法，但最后，它们全都归结为带有网络掩码的基本ip.另外，假设部分是指所有具有共同前缀的IP，那么这也等同于指定IP范围.

There exist several way of writing down partial IP address, but in the end, they all come down to a base ip with a net mask. Also, assuming that by partial, you mean all IPs with a common prefix, Then this is also equivalent to specifying a range of IPs.

无论哪种方式，部分IP地址规范最终都被描述为两个32位无符号整数，以与数据库列相同的格式编码.您可以有一个起始ip和结束ip，或者有一个基本ip和一个掩码.这些整数可以直接在SQL查询中使用，以有效地获取匹配项.更好的是，如果您使用ip range方法，那么引擎将能够利用ip列上的有序索引.您再也期待不到.

Either way, the partial IP address specification ends up being described as two 32 bits, unsigned integers, encoded in the same format as your database column. Either you have a starting ip and end ip, or you have a base ip and a mask. These integers can be used directly inside your SQL query to obtain matches efficiently. Even better, if you use the ip range approach, then the engine will be able to take advantage of an ordered index on your ip column. You can't expect any better.

那么如何建立IP范围呢?我们将首先取决于如何指定您的部分地址，但假设您确实知道网络掩码，则起始地址等于(base ip& net mask)，结束地址为((base ip& net mask)|(〜netmask))，其中& ;, |和〜分别表示按位与，按位或非.

So how to build the IP range? We'll that depends how your partial addresses were specified on the first place, but assuming that you do know the net mask, then the start address is equal to (base ip & net mask), and the end address is ((base ip & net mask) | (~netmask)), where &, | and ~ respectively means bitwise-and, bitwise-or and bitwise-not.

更新

这是应用我描述的策略的示例代码.

Here is a sample code to apply the strategy I described.

现在，距离我上一次编写PHP代码已经很长时间了，以下代码从未执行过，所以请原谅我可能引入的任何错误.我还故意选择扩展"每个符号方案，以使它们更易于理解，而不是将所有符号都压缩在一个非常复杂的正则表达式中.

Now, it's been a very long time since I last write PHP code, and the following has never been executed, so please excuse any error I might have introduced. I also chose deliberately to "expand" each notation scenario in order to make them easier to understand, rather than to squeeze all of them in a single, very complex regex.

if (preg_match(' /^ (\d{1,3}) [.] (\d{1,3}) [.] (\d{1,3}) [.] (\d{1,3}) [/] (\d{1,2}) $/x', $input, $r)) {
    // Four-dotted IP with number of significant bits: 123.45.67.89/24

    $a = intval($r[1]);
    $b = intval($r[2]);
    $c = intval($r[3]);
    $d = intval($r[4]);
    $mask = intval($r[5]);

} elseif (preg_match(' /^ (\d{1,3}) (?: [.] [*0] [.] [*0] [.] [*0] )? $/x', $input, $r)) {
    // Four-dotted IP with three-last numbers missing, or equals to 0 or '*':
    // 123.45, 123.45.0.0, 123.45.*.*  (assume netmask of 8 bits)

    $a = intval($r[1]);
    $b = 0;
    $c = 0;
    $d = 0;
    $mask = 8;

} elseif (preg_match(' /^ (\d{1,3}) [.] (\d{1,3}) (?: [.] [*0] [.] [*0] )? $/x', $input, $r)) {
    // Four-dotted IP with two-last numbers missing, or equals to 0 or '*':
    // 123.45, 123.45.0.0, 123.45.*.*  (assume netmask of 16 bits)

    $a = intval($r[1]);
    $b = intval($r[2]);
    $c = 0;
    $d = 0;
    $mask = 16;

} elseif (preg_match(' /^ (\d{1,3}) [.] (\d{1,3}) [.] (\d{1,3}) (?: [.] [*0] )? $/x', $input, $r)) {
    // Four-dotted IP with last number missing, or equals to 0 or *:
    // 123.45.67, 123.45.67.0, 123.45.67.*  (assume netmask of 24 bits)

    $a = intval($r[1]);
    $b = intval($r[2]);
    $c = intval($r[3]);
    $d = 0;
    $mask = 24;

} elseif (preg_match(' /^ (\d{1,3}) [.] (\d{1,3}) [.] (\d{1,3}) [.] (\d{1,3}) $/x', $input, $r)) {
    // Four-dotted IP: 123.45.67.89 (assume netmask of 32 bits)

    $a = intval($r[1]);
    $b = intval($r[2]);
    $c = intval($r[3]);
    $d = intval($r[4]);
    $mask = 32;

} else {
    throw new Exception('...');
}

if ($a < 0 || $a > 255) {  throw new Exception('...') };
if ($b < 0 || $b > 255) {  throw new Exception('...') };
if ($c < 0 || $c > 255) {  throw new Exception('...') };
if ($d < 0 || $d > 255) {  throw new Exception('...') };
if ($mask < 1 || $mask > 32) {  throw new Exception('...') };

$baseip = ($a << 24) + ($b << 16) + ($c << 8) + ($d);
$netmask = (1 << (32 - $mask)) - 1;

$startip = $baseip & netmask;
$endip = ($baseip & netmask) | (~netmask);

// ...

doSql( "SELECT ... FROM ... WHERE ipaddress >= ? && ipaddress <= ?", $startip, $endip);

// or

doSql( "SELECT ... FROM ... WHERE ((ipaddress & ?) = ?)", $netmask, $startip);

这篇关于搜索存储为整数的部分IP地址的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！