使用mysql根据IP和子网掩码广播地址

Question

我有以下两列：

SELECT  b.ip_address AS IP ,b.mask AS MASK FROM interfaces b WHERE b.ip_address = 167804290;
+-----------+------------+
| IP        | MASK       |
+-----------+------------+
| 167804290 | 4294967168 | 
+-----------+------------+

Where is an actual IP address an its subnet mask

SELECT INET_NTOA(b.ip_address) AS IP,INET_NTOA(b.mask) AS MASK FROM interfaces b WHERE b.ip_address = 167804290;

+--------------+-----------------+
| IP           | MASK            |
+--------------+-----------------+
| 10.0.125.130 | 255.255.255.128 | 
+--------------+-----------------+
1 row in set (0.00 sec)

I am trying to find a way with mysql to grab the actual broadcast range which in this case is 10.0.125.255 or 167804415, but I can't see to find it. closest I gotten is

SELECT INET_NTOA(b.ip_address+(POWER(2,32)- b.mask - 1)) FROM interfaces b WHERE b.ip_address = 167804290; 
+---------------------------------------------------+
| INET_NTOA(b.ip_address+(POWER(2,32)- b.mask - 1)) |
+---------------------------------------------------+
| 10.0.126.1                                        | 
+---------------------------------------------------+

唯一的问题是，这是假设列 ip_address 是子网 10.0.125.128 的开头。

任何帮助将不胜感激。

Answer 1

广播地址是子网掩码的补码，与 IP 地址进行或运算。

SELECT INET_NTOA( ~b.mask & 0xffffffff | b.ip_address) 
FROM interfaces b 
WHERE b.ip_address = 167804290; 

（您必须使用“& 0xffffffff”进行掩码，因为在 MySQL 中，按位补运算符返回 64 位值。）