你可以这样转换
询问
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 []>
