使用 SQL 确定子网掩码的 cidr 值

Question

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

我已经执行了类似以下操作来确定子网的最后一个 IP，因此我希望执行类似的操作来确定掩码的 cidr 表示形式。

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 语句。

Answer 1

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

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

基本上我采用十进制掩码并从最大十进制值中减去它。 然后我对该值进行 log2 以获得对数值。 然后只需从 32（可用的最大位）中减去该值即可。

希望对其他人有帮助。

谢谢

Answer 2

SQL 查询没有过程循环构造（尽管有过程语言），但您可以将一组行与另一组行进行比较，这有点像循环。

您只有 32 个可能的子网掩码。 在这种情况下，创建一个小表来存储这 32 个掩码和关联的 CIDR 编号是有意义的。

CREATE TABLE cidr (
  bits INT UNSIGNED PRIMARY KEY,
  mask INT UNSIGNED NOT NULL
);

INSERT INTO cidr (bits) VALUES
  ( 1), ( 2), ( 3), ( 4), ( 5), ( 6), ( 7), ( 8), ( 9), (10),
  (11), (12), (13), (14), (15), (16), (17), (18), (19), (20),
  (21), (22), (23), (24), (25), (26), (27), (28), (29), (30),
  (31), (32);

UPDATE cidr SET mask = ((POWER(2,32)-1)<<(32-bits)) & (POWER(2,32)-1);

SELECT CONCAT(s.ip_addr, '/', c.bits)
FROM cidr c JOIN subnets s ON (c.mask = inet_aton(s.ip_mask));

Answer 3

例如，您需要将 255.255.255.252 网络掩码转换为位掩码。

我总是使用这个简单的查询（PostgreSQL）：

SELECT 32-length(trim(((split_part('255.255.255.252','.',1)::bigint*(256^3)::bigint +
                        split_part('255.255.255.252','.',2)::bigint*(256^2)::bigint +
                        split_part('255.255.255.252','.',3)::bigint*256 +
                        split_part('255.255.255.252','.',4)::bigint)::bit(32))::text,'1'));

虽然不是那么好，但它很短并且工作起来很有魅力。

Answer 4

--
-- Dumping routines for database
--
/*!50003 DROP FUNCTION IF EXISTS `INET_ATOC` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ALLOW_INVALID_DATES' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `INET_ATOC`(`paramNETMASK` varchar(15)) RETURNS int(2) unsigned
    DETERMINISTIC
    COMMENT 'Converts an IPv4 netmask in dotted decimal notation to a CIDR integer between 0 and 32'
BEGIN
    DECLARE `netmask` int unsigned;
    DECLARE `cidr` int unsigned;
    SET `netmask` = INET_ATON(`paramNETMASK`);
    IF (`netmask` IS NULL)
    THEN
        RETURN NULL;
    ELSE
        SET `cidr` = 0;
        countNetBits: WHILE (`cidr` < 32)
        DO
            IF ( (0x80000000 & `netmask`) = 0x80000000 )
            THEN
                SET `netmask` = 0xFFFFFFFF & (`netmask` << 1);
                SET `cidr` = `cidr` + 1;
            ELSE
                LEAVE countNetBits;
            END IF;
        END WHILE;
        IF (`netmask` != 0)
        THEN
            RETURN NULL;
        END IF;
        RETURN `cidr`;
    END IF;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP FUNCTION IF EXISTS `INET_CTOA` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ALLOW_INVALID_DATES' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `INET_CTOA`(`paramCIDR` int) RETURNS varchar(15) CHARSET utf8
    DETERMINISTIC
    COMMENT 'Converts a CIDR suffix (integer between 0 and 32) to an IPv4 netmask in dotted decimal notation'
BEGIN
    DECLARE `netmask` int unsigned;
    IF  ( (`paramCIDR` < 0) OR (`paramCIDR` > 32) )
    THEN
        RETURN NULL;
    ELSE
        SET `netmask` = 0xFFFFFFFF - (pow( 2, (32-`paramCIDR`) ) - 1);
        RETURN INET_NTOA(`netmask`);
    END IF;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `getSubnet` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `getSubnet`(INOUT `paramADDR` VARCHAR(15), INOUT `paramCIDR` INT, OUT `paramMASK` VARCHAR(15), OUT `paramNETWORK` VARCHAR(15), OUT `paramBROADCAST` VARCHAR(15), OUT `paramNUMHOSTS` INT) CHARSET utf8
    DETERMINISTIC
BEGIN
    DECLARE `numaddrs`  int unsigned;
    DECLARE `ipaddr`    int unsigned;
    DECLARE `netmask`   int unsigned;
    DECLARE `wildcard`  int unsigned;
    DECLARE `network`   int unsigned;
    DECLARE `broadcast` int unsigned;
    DECLARE `numhosts`  int unsigned;

    SET `ipaddr` = INET_ATON(`paramADDR`);

    IF (`ipaddr` IS NULL) OR (`paramCIDR` < 1) OR (`paramCIDR` > 30)
    THEN
        SELECT
            NULL, NULL, NULL, NULL, NULL, NULL
        INTO
            `paramADDR`, `paramCIDR`, `paramMASK`, `paramNETWORK`, `paramBROADCAST`, `paramNUMHOSTS`;
    ELSE
        SET `numaddrs`  = pow( 2, (32-`paramCIDR`) );
        SET `numhosts`  = `numaddrs` - 2;
        SET `netmask`   = 0xFFFFFFFF - (`numaddrs` - 1);
        SET `wildcard`  = 0xFFFFFFFF & (~`netmask`);
        SET `network`   = `ipaddr` & `netmask`;
        SET `broadcast` = `ipaddr` | `wildcard`;

        SELECT
            INET_NTOA(`ipaddr`), `paramCIDR`, INET_NTOA(`netmask`), INET_NTOA(`network`), INET_NTOA(`broadcast`), `numhosts`
        INTO
            `paramADDR`, `paramCIDR`, `paramMASK`, `paramNETWORK`, `paramBROADCAST`, `paramNUMHOSTS`;
    END IF;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;