如何在MySQL中使用INET_ATON进行通配符搜索IP地址? [英] How to do wildcard search for IP addresses using INET_ATON in MySQL?

问题描述

我发现此方法使用INET_ATON将IP地址存储在MySQL数据库中为整数: https://stackoverflow.com/a/5133610/4491952

I found this method to store IP addresses in MySQL database as integer using INET_ATON: https://stackoverflow.com/a/5133610/4491952

由于IPv4地址的长度为4个字节，因此您可以使用 INT(UNSIGNED)正好有4个字节:

Since IPv4 addresses are 4 byte long, you could use an INT (UNSIGNED) that has exactly 4 bytes:

`ipv4` INT UNSIGNED

和 INET_ATON 和 INET_NTOA 转换它们:

And INET_ATON and INET_NTOA to convert them:

INSERT INTO `table` (`ipv4`) VALUES (INET_ATON("127.0.0.1"));
SELECT INET_NTOA(`ipv4`) FROM `table`;

对于IPv6地址，您可以使用 BINARY 代替:

For IPv6 addresses you could use a BINARY instead:

`ipv6` BINARY(16)

并使用 PHP的inet_pton 和

And use PHP’s inet_pton and inet_ntop for conversion:

'INSERT INTO `table` (`ipv6`) VALUES ("'.mysqli_real_escape_string(inet_pton('2001:4860:a005::68')).'")'
'SELECT `ipv6` FROM `table`'
$ipv6 = inet_pton($row['ipv6']);

但是如何使用INET_ATON和PHP的ip2long函数进行通配符搜索，例如192.168.％?

But how can I do a wildcard search, for example 192.168.%, using INET_ATON and PHP's ip2long function?

推荐答案

通配符搜索对字符串进行操作，并且由于通常无法从索引中受益，因此通配符搜索往往非常慢.

Wildcard search operates on strings and, since it can't normally benefit from indexes, it tends to be extremely slow.

如果以针对机器的标准化表示形式存储IP地址(相对于人类可读的点符号)，则可以将它们视为数字，使用许多标准运算符并充分利用索引.一个例子:

If you store IP addresses in a normalised representation aimed at machines (vs the human-readable dot-notation) you can treat them as if they were numbers, use many standard operators and make good use of indexes. An example:

SELECT *
FROM foo
WHERE dot_notation LIKE '192.168.%';

...可以重写为:

SELECT *
FROM foo
WHERE as_integer BETWEEN INET_ATON('192.168.0.0') AND INET_ATON('192.168.255.255');

即使这些INET_ATON()实例仅出于可读性考虑，您也可以只输入结果整数.如果您使用PHP，那么这很简单，因为您可以将其外包给PHP:

Even these INET_ATON() instances are for mere readability, you could just enter the resulting integer. If you use PHP it's trivial because you can outsource it to PHP:

$sql = 'SELECT *
    FROM foo
    WHERE as_integer BETWEEN ? AND ?';
$params = [
   // Not sure whether you still need the sprintf('%u') trick in 64-bit PHP
   ip2long('192.168.0.0'), ip2long('192.168.255.255')
];

我目前无法对其进行测试，但我知道这也适用于IPv6.

I cannot test it right now but I understand this should work with IPv6 as well.

这篇关于如何在MySQL中使用INET_ATON进行通配符搜索IP地址?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！