检查IPv6是否在范围内

Question

我有maxmind的IPv6数据。这是我的当前表格（带有示例数据）：

+---------------+------------+
|    network    | geoname_id |
+---------------+------------+
| 2001:208::/32 |        123 |
| 2001:218::/32 |       4312 |
+---------------+------------+

使用他们的转换器，我可以创建network_start_ip和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地址（范围）的最佳方法是什么？

谢谢

- Cornwell

---

@PaulSpiegel 它不起作用，而且我更喜欢避免字符串样式比较，因为它们速度较慢。 - Cornwell

INET6_ATON('2001:201:ffff:ffff:ffff:ffff:ffff:ffff') BETWEEN INET6_ATON(b.network_start_ip) AND INET6_ATON(b.network_last_ip)怎么样？ - Paul Spiegel

比较索引字符串比将字符串转换为数字/二进制更快。 - Paul Spiegel

@PaulSpiegel 这个也不行。这个也不行： WHERE HEX(INET6_ATON('2001:201:ffff:ffff:ffff:ffff:ffff:ffff')) BETWEEN HEX(INET6_ATON(b.network_start_ip)) AND HEX(INET6_ATON(b.network_last_ip)) - Cornwell

您正在检查的IP不在您样本数据的任何两个范围内。 - Paul Spiegel

Answer 1

以下是我让它正常工作的方法：

1. 创建另一个具有相同列的表，但network_start_ip和network_last_ip都是VARBINARY(16)
2. 使用此语句填充该表：INSERT INTO blocks SELECT INET6_ATON(b2.network_start_ip), INET6_ATON(b2.network_last_ip), b2.geoname_id FROM blocks_copy b2;
3. 然后，要检查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

- Cornwell

---

这个解决方案听起来不错，我也在处理这个数据集，你用这种方法处理IPv4的时候也有效吗？ - alistaircol

最后一个查询语句(SELECT geoname_id ......)是否确保只返回一个结果？我刚测试了大约15个IPv6，每个测试都只返回了一个结果，但如果您能确认一下，我会很感激。谢谢。 - Ian Y.

Answer 2

HEX(...) 的原本存在是不必要的，因为您似乎已经在自己的回答中观察到了。也就是说，BINARY(16) 可以正确比较 IPv6 值。

如果您的表中有无数行数据，则会发现查询非常缓慢。这是因为没有索引可以始终帮助优化器。可以进行快速查询，但需要摆脱最后一列（last_ip）并填充缺失的任何范围。我在这里讨论了如何做到这一点，并提供了代码：here。

- Rick James