我发现此方法使用INET_ATON将IP地址存储在MySQL数据库中为整数: stackoverflow/a/5133610/4491952
I found this method to store IP addresses in MySQL database as integer using INET_ATON: stackoverflow/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 和 inet_ntop 进行转换:
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
发布评论