尘烟染暮雪  发表于 2020-8-13 11:11 显示全部楼层 楼主
类似IP形式“111.195.130.0/24”求ip的范围值

春风吹又生  发表于 2020-8-13 11:16 显示全部楼层 沙发
可以编写自定义函数f_iptorange来实现

DELIMITER |
CREATE DEFINER="gbase"@"%" FUNCTION "f_iptorange"(ip_address VARCHAR ,prefix VARCHAR) RETURNS varchar(100) CHARSET utf8
begin
declare v_return VARCHAR;
declare v_comma1 int;
declare v_comma2 int;
declare v_comma3 int;
declare v_length int;
declare v_ipaddr1 int; declare  v_ipaddr2 int; declare v_ipaddr3 int; declare v_ipaddr4 int;declare v_prefix_tmp    VARCHAR(32); declare v_prefix_tmp1    VARCHAR(32); declare v_prefix_tmp2    VARCHAR(32);declare v_prefix_b1 VARCHAR(18); declare v_prefix_b2 VARCHAR(18); declare v_prefix_b3 VARCHAR(18); declare v_prefix_b4 VARCHAR(18);declare v_netmask1 int;  declare v_netmask2 int;  declare v_netmask3 int;  declare v_netmask4 int;declare v_broadcast1 int;  declare v_broadcast2 int;  declare v_broadcast3 int;  declare v_broadcast4 int;declare v_startip VARCHAR(50); declare v_endip   VARCHAR(50);set v_prefix_tmp ='00000000000000000000000000000000' ;set v_length=LENGTH;
set v_comma1=length(substring_index(ip_address,'.',1))+1;
set v_comma2=length(substring_index(ip_address,'.',2))+1;
set v_comma3=length(substring_index(ip_address,'.',3))+1;
set v_ipaddr1=TO_NUMBER(SUBSTRing(ip_address,1,v_comma1-1));
set v_ipaddr2=TO_NUMBER(SUBSTRing(ip_address,v_comma1+1,v_comma2-v_comma1-1));
set v_ipaddr3=TO_NUMBER(SUBSTRing(ip_address,v_comma2+1,v_comma3-v_comma2-1));
set v_ipaddr4=TO_NUMBER(SUBSTRing(ip_address,v_comma3+1,v_length-v_comma3));

set v_prefix_tmp1=SUBSTRing(v_prefix_tmp,1,prefix);
set v_prefix_tmp2=SUBSTRing(v_prefix_tmp,prefix+1,32-length(v_prefix_tmp1));
set v_prefix_tmp=concat(replace(v_prefix_tmp1,0,1),v_prefix_tmp2);

set v_prefix_b1=SUBSTRing(v_prefix_tmp,1,8);
set v_prefix_b2=SUBSTRing(v_prefix_tmp,9,8);
set v_prefix_b3=SUBSTRing(v_prefix_tmp,17,8);
set v_prefix_b4=SUBSTRing(v_prefix_tmp,25,8);

set @v_prefix1=f_bin_to_dec(v_prefix_b1);
set @v_prefix2=f_bin_to_dec(v_prefix_b2);
set @v_prefix3=f_bin_to_dec(v_prefix_b3);
set @v_prefix4=f_bin_to_dec(v_prefix_b4);

set v_netmask1=v_ipaddr1 & @v_prefix1;
set v_netmask2=v_ipaddr2 & @v_prefix2;
set v_netmask3=v_ipaddr3 & @v_prefix3;
set v_netmask4=v_ipaddr4 & @v_prefix4;

set v_broadcast1=255-((v_netmask1+@v_prefix1)-2*(v_netmask1 & @v_prefix1));
set v_broadcast2=255-((v_netmask2+@v_prefix2)-2*(v_netmask2 & @v_prefix2));
set v_broadcast3=255-((v_netmask3+@v_prefix3)-2*(v_netmask3 & @v_prefix3));
set v_broadcast4=255-((v_netmask4+@v_prefix4)-2*(v_netmask4 & @v_prefix4));

set v_startip=concat(to_char(v_netmask1),'.',to_char(v_netmask2),'.',to_char(v_netmask3),'.',to_char(v_netmask4+1));
set v_endip=concat(to_char(v_broadcast1),'.',to_char(v_broadcast2),'.',to_char(v_broadcast3),'.',to_char(v_broadcast4-1));

set v_return=concat(v_startip,'-',v_endip);
RETURN v_return;
END |

您需要登录后才可以回帖 登录 | 立即注册