如何在MySQL中使用INET_ATON对IP地址进行通配符搜索?

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

我在MySQL数据库中使用INET_ATON: https://*.com/a/5133610/4491952，找到了存储IP地址的方法。

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

由于IPv4地址是4字节长，所以您可以使用一个只有4个字节的INT(未签名):

`ipv4` INT UNSIGNED

And INET_ATON and INET_NTOA to convert them:

以及INET_ATON和INET_NTOA转换它们:

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

For IPv6 addresses you could use a BINARY instead:

对于IPv6地址，您可以使用二进制文件:

`ipv6` BINARY(16)

And use PHP’s inet_pton and inet_ntop for conversion:

并使用PHP的inet_pton和inet_ntop进行转换:

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

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

但是我如何做一个通配符搜索，例如192.168。%，使用INET_ATON和PHP的ip2long函数?

2 个解决方案

#1

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

通配符搜索是在字符串上操作的，因为它通常不能从索引中获益，所以它的速度非常慢。

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:

如果您将IP地址存储在针对机器的规范化表示中(与人类可读的dot符号相比)，您可以像对待数字一样对待它们，使用许多标准操作符，并充分利用索引。一个例子:

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

... can be rewritten as:

…可以写成:

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

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:

即使是这些INET_ATON()实例仅仅是为了可读性，您也可以输入最终的整数。如果你使用PHP，这很简单，因为你可以把它外包给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')
];

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

我现在还不能测试它，但是我知道这也应该与IPv6合作。

#2

One neat trick MySQL offers is bit shifting. You can use it to see if an ip is contained within an address block written in cidr notation. You can use this method treating your addresses as X.X.X.X/16 cidr block.

MySQL提供的一个简单的技巧是位转换。您可以使用它来查看一个ip是否包含在cidr标记的地址块中。您可以使用此方法将您的地址视为X.X.X. x。X / 16 cidr块。

set @cidr_block:='10.20.30.40/16';

select inet_ntoa(inet_aton(substring_index(@cidr_block,'/',1))>>(32-substring_index(@cidr_block,'/',-1))<<(32-substring_index(@cidr_block,'/',-1))) as first_ip,
                 inet_aton(substring_index(@cidr_block,'/',1))>>(32-substring_index(@cidr_block,'/',-1))<<(32-substring_index(@cidr_block,'/',-1))  as first_ip_num,
        inet_ntoa((((inet_aton(substring_index(@cidr_block,'/',1))>>(32-substring_index(@cidr_block,'/',-1)))+1)<<(32-substring_index(@cidr_block,'/',-1)))-1) as last_ip,
                 (((inet_aton(substring_index(@cidr_block,'/',1))>>(32-substring_index(@cidr_block,'/',-1)))+1)<<(32-substring_index(@cidr_block,'/',-1)))-1  as last_ip_num
;
+-----------+--------------+---------------+-------------+
| first_ip  | first_ip_num | last_ip       | last_ip_num |
+-----------+--------------+---------------+-------------+
| 10.20.0.0 |    169082880 | 10.20.255.255 |   169148415 |
+-----------+--------------+---------------+-------------+
1 row in set (0.00 sec)

Shortcut to seeing if an ip is in the address block - simply sift both cidr address and ip to see if they are the same. Of course, this will be a table scan if applied to stored values.

在地址块中查看ip的快捷方式——简单地筛选cidr地址和ip，看看它们是否相同。当然，如果应用到存储值，这将是一个表扫描。

select inet_aton('127.0.0.1')>>16 = inet_aton('127.0.10.20')>>16 as `1 = true`;
+----------+
| 1 = true |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

select inet_aton('127.0.0.1')>>16 = inet_aton('127.10.10.20')>>16 as `0 =  false`;
 +-----------+
 | 0 = false |
 +-----------+
 |         0 |
 +-----------+
 1 row in set (0.00 sec)

#1