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

Question

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

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

`ipv4` INT UNSIGNED

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

For IPv6 addresses you could use a BINARY instead:

`ipv6` BINARY(16)

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

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

Answer 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:

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:

$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.