ip范围和cidr相互转换

 工具站 IP to CIDR online converter

CREATE TABLE `ip` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',

  `ip_cidr` varchar(64) default null,

  `ip_start` varchar(64) DEFAULT NULL,
  `ip_end` varchar(64) DEFAULT NULL,
		
  `iplong_start` int unsigned DEFAULT NULL,
  `iplong_end` int unsigned DEFAULT NULL,
 
  PRIMARY KEY (`id`) USING BTREE
) COMMENT='ip表';


-- ip to iplong
update ip
set iplong_start = INET_ATON(ip_start),
iplong_end = INET_ATON(ip_end)



-- 猜cidr偏移值
set @off = 23;
update ip
set ip_cidr = CONCAT(ip_start , "/" , @off)
where INET_NTOA( INET_ATON(ip_start)	| ((0x100000000 >> @off ) -1 )) = ip_end



-- 根据 ip范围 计算 cidr
delimiter // 
drop procedure if exists test; 
create procedure test()     
begin
		declare i int;      
		set i = 0;         
		while i <= 32 do 
				
			update ip
			set ip_cidr = CONCAT(ip_start , "/" , i)
			where INET_NTOA( INET_ATON(ip_start)	| ((0x100000000 >> i ) -1 )) = ip_end;

			set i = i + 1;   			
		end while;
		
end
//                                     
call test(); 





-- 根据 cidr 计算 ip范围
SELECT
	INET_NTOA(
		INET_ATON(SUBSTRING_INDEX(ip_cidr, '/', 1)) & 0xffffffff ^ ((0x1 << ( 32 - SUBSTRING_INDEX(ip_cidr, '/', -1)) ) -1 )
	) start_ip,
	INET_NTOA(
		INET_ATON( SUBSTRING_INDEX(ip_cidr, '/', 1)) | ((0x100000000 >> SUBSTRING_INDEX(ip_cidr, '/', -1) ) -1 )
	) end_ip
FROM ip;