MySQL查询将CIDR转换为IP范围

你可以这样转换

询问

SELECt  INET_NTOA(INET_ATON( SUBSTRING_INDEX(val, '/', 1))    & 0xffffffff ^ ((0x1 << ( 32 - SUBSTRING_INDEX(val, '/', -1))  ) -1 )) from_ip,  INET_NTOA(INET_ATON( SUBSTRING_INDEX(val, '/', 1))    | ((0x100000000 >> SUBSTRING_INDEX(val, '/', -1) ) -1 )) to_ipFROM cidr;

样品表

CREATE TABLE `cidr` (  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,  `val` varchar(32) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `cidr` (`id`, `val`)VALUES    (1, '192.168.2.12/24'),    (2, '192.168.2.12/25'),    (3, '1.0.85.128/25'),    (4, '192.168.2.12/32');

或者，您可以在虚拟列中使用它。因此，您可以直接获得正确的值。

虚拟领域

CREATE TABLE `cidr1` (  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,  `val` VARCHAr(32) DEFAULT NULL,  `from_ip` VARCHAr(15) AS ( INET_NTOA(INET_ATON( SUBSTRING_INDEX(val, '/', 1)) & 0xffffffff ^ ((0x1 << ( 32 - SUBSTRING_INDEX(val, '/', -1))  ) -1 ))) PERSISTENT ,  `to_ip` VARCHAr(15)  AS ( INET_NTOA(INET_ATON( SUBSTRING_INDEX(val, '/', 1)) | ((0x100000000 >> SUBSTRING_INDEX(val, '/', -1) ) -1 ))) PERSISTENT ,  PRIMARY KEY (`id`)) ENGINE=INNODB DEFAULT CHARSET=utf8;INSERT INTO `cidr1` (`id`, `val`)VALUES    (1, '192.168.2.12/24'),    (2, '192.168.2.12/25'),    (3, '1.0.85.128/25'),    (4, '192.168.2.12/32');MariaDB []> select * from cidr1;+----+-----------------+--------------+---------------+| id | val  | from_ip      | to_ip         |+----+-----------------+--------------+---------------+|  1 | 192.168.2.12/24 | 192.168.2.0  | 192.168.2.255 ||  2 | 192.168.2.12/25 | 192.168.2.0  | 192.168.2.127 ||  3 | 1.0.85.128/25   | 1.0.85.128   | 1.0.85.255    ||  4 | 192.168.2.12/32 | 192.168.2.12 | 192.168.2.12  |+----+-----------------+--------------+---------------+4 rows in set (0.00 sec)MariaDB []>