搜索以整数形式存储的部分IP地址

Question

我目前有一个包含IP地址的MySQL数据库。在搜索表单上，客户端希望搜索部分IP地址，并(可能)弹出许多结果。我目前在mysql中将IP地址存储为unsigned int。我使用的是PHP5.2，所以不能访问PHP5.7及其INET6_NTOA函数。

当前的数据库有超过50,000条记录，并且还在继续增长，所以我不想将所有的IP转换为点符号，然后进行匹配-这看起来有点笨拙。

有没有更好的方法搜索部分IP地址？

Answer 1

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

有几种写下部分IP地址的方法，但最终都归结为带有网络掩码的基本ip。另外，假设部分指的是具有公共前缀的所有is，那么这也等同于指定一个is范围。

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

那么如何构建IP范围呢？这取决于你的部分地址最初是如何指定的，但是假设你知道网络掩码，那么起始地址等于(基本ip &网络掩码)，结束地址是((基本ip &网络掩码)| (~netmask))，其中&，|和~分别表示逐位and，逐位or和逐位not。

更新

以下是应用我所描述的策略的示例代码。

现在，距离我上次编写PHP代码已经有很长一段时间了，下面的代码从未执行过，所以请原谅我可能引入的任何错误。我还特意选择“扩展”每个符号场景，以使它们更容易理解，而不是将它们全部挤在一个非常复杂的正则表达式中。

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);

Answer 2

假设您处理的是IPv4地址，则每个地址只有32位。

有一个MySQL INET_NTOA函数，负责根据您的IP返回字符串。

因此，您可能希望使用smth，如下所示：

SELECT ... FROM ... WHERE INET_NTOA(...) LIKE (...)

希望能有所帮助。

更新：为了提高生产率，我建议您更新表，为IP的字符串表示添加新的CHAR(16)字段，以及使用INET_NTOA(...)值填充该字段的触发器ON UPDATE。在此字段中进行选择将非常有用。

Answer 3

这就是了。

$ip = '127.5.3';

if (preg_match('/^([0-9]*)?\.?([0-9]*)?\.?([0-9]*)?\.?([0-9]*)$/',$ip, $m)) {
  $from = (int)$m[1]*256*256*256 +(int)$m[2]*256*256 + (int)$m[3]*256 + (int)$m[4];
  // or $from = ip2long($m[1].'.'.$m[2].'.'.$m[3].'.'.$m[4]);
  $to = ($m[1]>0?$m[1]:255)*256*256*256 + ($m[2]>0?$m[2]:255)*256*256 + ($m[3]>0?$m[3]:255)*256+($m[4]>0?$m[4]:255);
  // select * from sometable where ip between $from and $to
} else
  echo "Incorrect IP";