检查ipv6是否在范围内 [英] Check if ipv6 is inside range

问题描述

我有maxmind的ipv6数据.这是我当前的表格(带有示例数据):

I have the maxmind's ipv6 data. Here's my current table (with sample data):

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

使用其转换器，我可以创建 network_start_ip 和 network_last_ip 列:

Using their converter, I can create a network_start_ipand network_last_ip columns:

+------------------+----------------------------------------+------------+
| 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 |
+------------------+----------------------------------------+------------+

我期望这样的事情会起作用(即使它可能比其他方法要慢):

I was expecting that something like this would work (even though it would probably be slower than other methods):

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地址(范围)的最佳方法?

So, what am I missing? Also, what is the best way to store ipv6 addresses (ranges)

谢谢

推荐答案

这是我的工作方式:

1. 创建了另一个具有相同列的表，但是 network_start_ip 和 network_last_ip 是 VARBINARY(16)
2. 使用以下语句填充该表: INSERT INTO块选择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 bINET6_ATON('2a01:4ff:ffff:ffff :: ffff')在b.network_start_ip和b.network_last_ip之间

这篇关于检查ipv6是否在范围内的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！