使用SQL确定子网掩码的cidr值 [英] Using SQL to determine cidr value of a subnet mask

问题描述

我想找到一种方法来执行SQL查询，以计算存储在数据库中的子网掩码的cidr（位表示）。例如，我将255.255.255.0或其十进制值（4294967040）存储在数据库中。我想进行选择并通过查询获取/ 24表示形式。

I'd like to find a way to do a SQL query that will calculate the cidr (bit representation) of a subnet mask stored in the database. So for example, I've got either 255.255.255.0 or its decimal value (4294967040) stored in the database. I'd like to do a select and get back /24 representation via the query.

我已经完成了如下操作来确定子网的最后一个IP，因此我希望做类似的事情来确定蒙版的cidr表示形式。

I've done things like the following to determine the last IP of a subnet so I'm hoping to do something similar to determine the cidr representation of a mask.

select concat(inet_ntoa(ip_addr),'-',
    inet_ntoa(ip_addr+(POWER(2,32)-ip_mask-1))) range 
from subnets 
order by ip_addr

最好是一条可在mysql，postgres，oracle等环境下工作的SQL语句。

Preferably this would be a SQL statement that would work under mysql, postgres, oracle etc.

推荐答案

我想我已经找到解决我问题的方法。这是我所做的事情：

I think I have found the solution to my issue. Here is what I have done:

select CONCAT(INET_NTOA(ip_addr),'/',32-log2((4294967296-ip_mask))) net 
from subnets 
order by ip_addr

我的dedecial蒙版，然后从最大十进制值中减去它。然后，我对该值进行log2以获得对数值。然后只需从32（可用的最大位）中减去该值即可。

Basically I take my decmial mask and subtract it from the maximum decimal value. I then to a log2 on that value to get the logarithm value. Then simply subtract that from 32 (the maximum bit available).

希望对他人有帮助。

谢谢

这篇关于使用SQL确定子网掩码的cidr值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！