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

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?

解决方案

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.