检查IP是否在子网中 [英] Check if IP is in subnet

问题描述

我有一个表A，IP地址（ipNumeric）存储为无符号整数，表B包含子网（subnetNumeric）：

I have a table A with IP addresses (ipNumeric) stored as unsigned ints and a table B with subnets (subnetNumeric):

INET_NTOA(ipNumeric) = 192.168.0.1
INET_NTOA(subnetNumeric) = 192.168.0.0

我想检查这个IP是否是子网的成员。

I'd like to check if this IP is a member of a subnet.

子网是A，B和C类。

这是否可以在MySQL中的合理时间内进行，或者子网范围是否可以预先计算？

Is this possible to do in reasonable time in MySQL on the fly or should the subnet ranges be precomputed?

推荐答案

当然，这是可行的。我们的想法是，我们通过将最高有效位设置为1来计算子网掩码，这与子网类所规定的一样多。对于C类，那将是

Sure, it's doable. The idea is that we calculate the subnet mask by setting the most significant bits to 1, as many as dictated by the subnet class. For a class C, that would be

SELECT -1 << 8;

然后，使用你拥有的IP地址和子网掩码;如果IP在子网内，结果应该等于子网地址 - 标准网络内容。所以我们最终得到：

Then, AND the subnet mask with the IP address you have; if the IP is inside the subnet, the result should be equal to the subnet address -- standard networking stuff. So we end up with:

SELECT (-1 << 8) & INET_ATON("192.168.0.1") = INET_ATON("192.168.0.0");

更新：是的，有必要了解网络类或子网掩码（这是等效信息）。如果您没有此信息，请考虑如何处理子网 X.Y.0.0 的情况。这是 XY0.0 / 16 或 XY0.0 / 8 第三个八位字节刚刚发生是0？无法知道。

Update: Yes, it is necessary to know the network class or the subnet mask (which is equivalent information). Consider how could you handle the case where the subnet is X.Y.0.0 if you did not have this information. Is this X.Y.0.0/16 or X.Y.0.0/8 where the third octet just happens to be 0? No way to know.

如果你知道子网掩码，那么查询可以写成

If you do know the subnet mask, then the query can be written as

SELECT (-1 << (33 - INSTR(BIN(INET_ATON("255.255.255.0")), "0"))) &
       INET_ATON("192.168.0.1") = INET_ATON("192.168.0.0");

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