我有maxmind的ipv6数据。这是我当前的表(带有示例数据):
+---------------+------------+
| network | geoname_id |
+---------------+------------+
| 2001:208::/32 | 123 |
| 2001:218::/32 | 4312 |
+---------------+------------+
Using 他们的转换器 https://github.com/maxmind/geoip2-csv-converter,我可以创建一个network_start_ip
and network_last_ip
列:
+------------------+----------------------------------------+------------+
| network_start_ip | network_last_ip | geoname_id |
+------------------+----------------------------------------+------------+
| 2001:200:: | 2001:200:ffff:ffff:ffff:ffff:ffff:ffff | 123 |
| 2001:208:: | 2001:208:ffff:ffff:ffff:ffff:ffff:ffff | 4312 |
+------------------+----------------------------------------+------------+
我期待这样的事情会起作用(尽管它可能比其他方法慢):
SELECT b.geoname_id FROM blocks b
WHERE HEX(INET6_ATON('2001:201:ffff:ffff:ffff:ffff:ffff:ffff')) BETWEEN HEX(b.network_start_ip) AND HEX(b.network_last_ip)
那么,我错过了什么?另外,存储 ipv6 地址(范围)的最佳方式是什么
谢谢
这是我的工作方式:
- 创建了另一个具有相同列的表,但是
network_start_ip
and network_last_ip
are VARBINARY(16)
- 使用以下语句填充该表:
INSERT INTO blocks
SELECT INET6_ATON(b2.network_start_ip), INET6_ATON(b2.network_last_ip), b2.geoname_id FROM blocks_copy b2;
- 然后,要检查 IPv6 地址是否在范围内,我只需运行以下查询:
SELECT geoname_id FROM blocks b
WHERE INET6_ATON('2a01:4ff:ffff:ffff::ffff') BETWEEN b.network_start_ip AND b.network_last_ip
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)