它没有转换回IPv4人类可读格式,而是转换为IPv6,因为
INET6_NTOA
的参数(二进制值)是16个字节。该函数将其视为IPv6地址的表示形式,而不是IPv4地址,后者仅为四个字节。
我认为问题可以追溯到问题中SQL的第一行,即对
BINARY(16)
的强制转换,它返回一个固定长度的16个字节。从返回的IPv4地址的四个字节开始,然后在右侧用零填充到16个字节的长度。
如果我们删除固定长度的转换,并允许
INET6_ATON
函数的结果只有四个字节会发生什么?
当数据库中存储的值只有四个字节时会发生什么?
如果我们更正
stats
表的内容,将16个字节的二进制值(IPv6地址的表示形式)更改为IPv4地址的四个字节的二进制表示形式,会发生什么?
UPDATE `stats`
SET ip_binary = INET6_ATON('66.249.64.90')
WHERE ip_binary = CAST(INET6_ATON('66.249.64.90') AS BINARY(16))
--或者--
UPDATE `stats`
SET ip_binary = X'42f9405a' + 0
WHERE ip_binary = X'42f9405a000000000000000000000000' + 0
跟进
问题描述... "使用 [expression] 将 IP 地址存储在数据库 [column] 中,如下所示:
cast(INET6_ATON(trim(:ipbinary)) as binary(16)))
我们不需要使用CAST
。也不需要使用CONVERT
,HEX
/UNHEX
或SUBSTR
。可以使用相同的表达式转换IPv4和IPv6地址:
INSERT ... ip_binary ... VALUES ( ... , INET6_ATON( :ip_string ) , ...
将它们转换回字符串,可以像这样:
SELECT ... , INET6_NTOA( ip_binary ) AS ip_string , ...
使用CAST
、CONVERT
、SUBSTR
、HEX
/UNHEX
很令人困惑,也导致一些东西无法正常工作。
为了更正已经存储在数据库中的值,我们需要找到一种区分16字节二进制表示中哪些是实际上应该被存储为4字节的IPv4地址的方法。
如果ip_delete
包含字符串表示,我们可以重新转换成二进制表示。
UPDATE `stats`
SET ip_binary = INET6_ATON( ip_delete )
演示
CREATE TABLE `addr` (ip_string VARCHAR(45), ip_binary VARBINARY(16)) ;
INSERT INTO `addr` VALUES ( '66.249.64.90' , INET6_ATON( '66.249.64.90' ));
INSERT INTO `addr` VALUES ( '127.0.0.1' , INET6_ATON( '127.0.0.1' ));
INSERT INTO `addr` VALUES ( '192.168.1.1' , INET6_ATON( '192.168.0.1' ));
INSERT INTO `addr` VALUES ( '2001:4860:4860::8888' , INET6_ATON( '2001:4860:4860::8888' ));
SELECT ip_string, HEX(ip_binary), INET6_NTOA(ip_binary) FROM `addr` ;
ip_string HEX(ip_binary) INET6_NTOA(ip_binary)
66.249.64.90 42F9405A 66.249.64.90
127.0.0.1 7F000001 127.0.0.1
192.168.1.1 C0A80001 192.168.0.1
2001:4860:4860::8888 20014860486000000000000000008888 2001:4860:4860::8888
ip_binary
应该保存页面访问的 IP 地址的二进制表示,无论是 IPV4 还是 IPV6。手册 https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html 中使用了 IPV4 和 IPV6。这是否不正确? - user3783243