检查IP是否在CIDR网络掩码(范围)内

Question

我有两张桌子ip和cidr。

在第一个示例中，我存储了ip。(两列table，id，ip)，下面是一个示例(值是虚构的)：

id | ip
---+-------------
 1 | 172.922.2.10
---+-------------
 2 | 194.22.10.13

在第二个示例中，我存储了CIDR网络掩码(2列表，id，cidr)，下面是一个示例(值是虚构的)：

id | cidr
---+-------------
 1 | 26.232.49.0/20
---+---------------
 2 | 14.44.182.0/24

有没有办法进行mysql查询，检查第一个表中的ip是否在我的任何cidr网络掩码范围内？

注意:要将cidr网络掩码转换为ip范围，请单击here

Answer 1

我个人推荐使用postgres，因为它具有CIDR数据类型和强大的函数，但在MySQL中也有一个关于做类似事情的有趣讨论。

http://planet.mysql.com/entry/?id=29283

Answer 2

这是在我的一个相关项目中出现的，这似乎是谷歌对这个问题的最高搜索结果，所以，你得到了答案！

create function get_lowest_ipv4(cidr char(18)) returns bigint deterministic return INET_ATON(SUBSTRING_INDEX(cidr, '/', 1));
create function get_highest_ipv4(cidr char(18)) returns bigint deterministic return get_lowest_ipv4(cidr) + (0x100000000 >> SUBSTRING_INDEX(cidr,'/', -1)) - 1;

然后你可以做... from ip_map where INET_ATON("ip.add.re.ss") between get_lowest_ipv4(ip) AND get_highest_ipv4(ip)

因为您将函数声明为确定性的，所以它将被缓存在mysql中，并且计算只需要运行一次。然后它就是‘是大于y小于x的整数’，这将是有效的瞬间。

MySQL [astpp]> set @cidr="10.11.0.0/16";
Query OK, 0 rows affected (0.00 sec)

MySQL [astpp]> select get_lowest_ipv4(@cidr), get_highest_ipv4(@cidr), INET_NTOA(get_lowest_ipv4(@cidr)), INET_NTOA(get_highest_ipv4(@cidr));
+------------------------+-------------------------+-----------------------------------+------------------------------------+
| get_lowest_ipv4(@cidr) | get_highest_ipv4(@cidr) | INET_NTOA(get_lowest_ipv4(@cidr)) | INET_NTOA(get_highest_ipv4(@cidr)) |
+------------------------+-------------------------+-----------------------------------+------------------------------------+
|              168493056 |               168558591 | 10.11.0.0                         | 10.11.255.255                      |
+------------------------+-------------------------+-----------------------------------+------------------------------------+
1 row in set (0.00 sec)

MySQL [astpp]> set @cidr="10.11.12.1/32";
Query OK, 0 rows affected (0.00 sec)

MySQL [astpp]> select get_lowest_ipv4(@cidr), get_highest_ipv4(@cidr), INET_NTOA(get_lowest_ipv4(@cidr)), INET_NTOA(get_highest_ipv4(@cidr));
+------------------------+-------------------------+-----------------------------------+------------------------------------+
| get_lowest_ipv4(@cidr) | get_highest_ipv4(@cidr) | INET_NTOA(get_lowest_ipv4(@cidr)) | INET_NTOA(get_highest_ipv4(@cidr)) |
+------------------------+-------------------------+-----------------------------------+------------------------------------+
|              168496129 |               168496129 | 10.11.12.1                        | 10.11.12.1                         |
+------------------------+-------------------------+-----------------------------------+------------------------------------+
1 row in set (0.01 sec)

MySQL [astpp]>

唯一重要的警告是您要插入有效的CIDR。例如，10.11.12.13/24无效。这是10.11.12.0/24网络内部的IP地址。

如果您无法在插入CIDR之前验证它们(由于某种疯狂的原因)，您可以更改get_lowest_ipv4以对源文件进行逐位比较，但这样做就不那么优雅了。

INET_ATON(SUBSTRING_INDEX(`ip`, '/', 1)) & 0xffffffff ^((0x1 <<(32 -  SUBSTRING_INDEX(`ip`, '/', -1))) -1 )

是一个(未经测试的)尝试匹配无效的CIDR。