如何在SQL中将1.0.0.0/24理解为一个范围(1.0.0.0至1.0.0.24)？

Question

假设我有IP 1.0.88.12。我想查询一个IPv4数据库并返回正确的国家。

数据库(geodata)表(IP)以下列格式列出is：

network       country
---------------------
1.0.0.0/24     1212
1.0.1.0/24     1212
...            ...
1.0.88.0/22    3000

在这里，我假设1.0.88.0/22代表1.0.88.0、1.0.88.1、1.0.88.2 to 1.0.88.22。

我如何查询这个表，使它了解每个network实际上是一个范围？

因此，我的假设是错误的，感谢所有的投入！尽管如此，仍有一个问题悬而未决。

以下面的例子(我现在拥有的)为例：

/**
 * Convert IP 4-octed set into integer
 */
create function [dbo].[IPAddressToInteger] (@IP as varchar(15))
returns bigint
as
begin
 return (convert(bigint, parsename(@IP,1)) +
         convert(bigint, parsename(@IP,2)) * 256 +
         convert(bigint, parsename(@IP,3)) * 256 * 256 +
         convert(bigint, parsename(@IP,4)) * 256 * 256 * 256)
end


/**
 * Calculate CIDR from mask
 */
create function [dbo].[CIDRFromMask] (@mask as bigint)
returns varchar(5)
as
begin
     declare @maskCalc bigint
     select  @maskCalc = dbo.IPAddressToInteger('255.255.255.255') - @mask + 1

     declare @logCalc int
     select  @logCalc = (32 - log(@maskCalc, 2))

     return '/' + cast(@logCalc as varchar(5))
end

select dbo.IPAddressToInteger('195.65.254.11'); -- 3275881995
select select dbo.CIDRFromMask('3275881995');   -- /2

select '195.65.254.11/2' from geodata.IP        -- 0 results
select '195.65.254.0/2'  from geodata.IP        -- 1 result (seems correct)

要点是:如果我连接IP + CIDR，在geo数据库( afaik是一个完整的数据库)中找不到相应的IP。如果我手动将195.65.254.11/2转换为195.65.254.0/2，它确实会找到一个结果，但我不认为这样做是可行的。因此：，我还缺少哪一步？

Answer 1

您可以尝试将IP范围转换为Int32。这样，可以更容易地查询这些间隔。您可以参考以下链接以获得一般想法：

https://www.periscopedata.com/blog/comparing-ip-addresses-in-sql.html

Answer 2

如果它实际上是CIDR表示法，您可以按照建议的@Doğukan Tun apply掩码转换为int，并与CIDR的地址部分进行比较。使用两个参数-- IP和CIDR返回位值(0或1) --这只是做作业的插图代码，而不是函数。错误不被处理，假设的IP地址和CIDR是有效的。

declare @IP nvarchar(20)='192.168.1.111';
declare @CIDRADDR nvarchar(20)='192.168.0.0/22';


--convert to int
declare @IP_as_int bigint;
select @IP_as_int=convert(bigint, parsename(@ip, 1)) +
    convert(bigint, parsename(@ip, 2)) * 256 +
    convert(bigint, parsename(@ip, 3)) * 65536 +
    convert(bigint, parsename(@ip, 4)) * 16777216;

--calculate mask from CIDR: can specify table, or use CASE or use cycle
--mst performance will have CASE, but am to lazy to write CASE with 32 cases  
--the result should be N filled bits from left side of 32-bit integer, where N is number after / in CIDR notation
declare @mask bigint=0;
declare @m1 bigint
declare @m2 bigint=0x80000000;
select @m1=CHARINDEX('/',@CIDRADDR);
select @m1=cast(SUBSTRING(@CIDRADDR,@m1+1,2)  as int);

while(@m1>0)
begin
    set @mask = @mask | @m2;
    set @m2=@m2 /2;
    set @m1=@m1-1;
end



--calculate back
declare @IP_as_bin binary(4)=cast(@IP_as_int & @mask as binary(4));
declare @maskedIP nvarchar(20)= cast(cast((substring(@IP_as_bin,1,1)) as int) as nvarchar)+'.'  +
    cast(cast((substring(@IP_as_bin,2,1)) as int) as nvarchar)+'.'  +
    cast(cast((substring(@IP_as_bin,3,1)) as int) as nvarchar)+'.' + 
    cast(cast((substring(@IP_as_bin,4,1)) as int) as nvarchar);

--compare with CIDR address part
select @m1=CHARINDEX('/',@CIDRADDR);
if(SUBSTRING(@CIDRADDR,1,@m1-1)=@maskedIP)
    print @IP + ' belongs to ' +@CIDRADDR;
else
    print @IP + ' doesn`t belong to ' +@CIDRADDR;

Answer 3

@Dogucan对思想的拓展

首先，我将IP字符串转换为整数(忽略"/24")。我使用"/“部分来确定网络路由使用了多少比特，在本例中是24位。我用这个来找出IP地址使用了多少位。在这种情况下，它是32-24 =8.8位，等于2^8 = 256 (因为IP是基于零的，所以我取1作为IP范围0.255)。

然后，我将其添加到原来的IP地址中，以获得最终IP并将其转换回字符串。

declare @ip varchar(50) = '1.0.0.0/24'


;WITH CTE
AS
(
    SELECT
        convert(bigint, SUBSTRING(parsename(@ip, 1),0, CHARINDEX('/',parsename(@ip, 1)))) +
      convert(bigint, parsename(@ip, 2)) * 256 +
      convert(bigint, parsename(@ip, 3)) * 65536 +
      convert(bigint, parsename(@ip, 4)) * 16777216 as StartIPInt,
      convert(bigint, SUBSTRING(parsename(@ip, 1),0, CHARINDEX('/',parsename(@ip, 1)))) +
      convert(bigint, parsename(@ip, 2)) * 256 +
      convert(bigint, parsename(@ip, 3)) * 65536 +
      convert(bigint, parsename(@ip, 4)) * 16777216 + 
      POWER(2,32- convert(bigint, SUBSTRING(parsename(@ip, 1),CHARINDEX('/',parsename(@ip, 1))+1,20)))-1 AS EndIpInt
)
,
IPRange
As
(
    SELECT 
        StartIPInt / 16777216 As StartOctet1, 
        (StartIPInt - (StartIPInt / 16777216) * 16777216) /65536 As StartOctet2,
        (StartIPInt - ((StartIPInt / 65536) * 65536))/256 As StartOctet3,
        (StartIPInt - ((StartIPInt / 256) * 256)) As StartOctet4,
        EndIpInt / 16777216 As EndOctet1, 
        (EndIpInt - (EndIpInt / 16777216) * 16777216) /65536 As EndOctet2,
        (EndIpInt - ((EndIpInt / 65536) * 65536))/256 As EndOctet3,
        (EndIpInt - ((EndIpInt / 256) * 256)) As EndOctet4
    FROM CTE
)
SELECT  CAST(StartOctet1 AS VARCHAR(3)) + '.' + CAST(StartOctet2 AS VARCHAR(3)) + '.' + CAST(StartOctet3 AS VARCHAR(3)) + '.' + CAST(StartOctet4 AS VARCHAR(3))  StartIPAddress,
        CAST(EndOctet1 AS VARCHAR(3)) + '.' + CAST(EndOctet2 AS VARCHAR(3)) + '.' + CAST(EndOctet3 AS VARCHAR(3)) + '.' + CAST(EndOctet4 AS VARCHAR(3))  EndIPAddress
FROM IPRange

这样做的结果是：

StartIPAddress  EndIPAddress
1.0.0.0         1.0.0.255

要获得更清晰的代码，请将IPRange CTE替换为：

IPRange
As
(
-- 1.0.0.0 0x0000000001000000
-- 0.1.0.0 0x0000000000010000
-- 0.0.1.0 0x0000000000000100
-- 0.0.0.255 0x00000000000000FF
    SELECT 
        CAST(SUBSTRING(CAST(StartIPInt AS varbinary),0, 6) AS tinyInt) AS StartOctet1,
        CAST(SUBSTRING(CAST(StartIPInt AS varbinary),5, 2) AS tinyInt) AS StartOctet2,
        CAST(SUBSTRING(CAST(StartIPInt AS varbinary),6, 2) AS tinyInt) AS StartOctet3,
        CAST(SUBSTRING(CAST(StartIPInt AS varbinary),7, 2) AS tinyInt) AS StartOctet4,
        CAST(SUBSTRING(CAST(EndIPInt AS varbinary),0, 6) AS tinyInt) AS EndOctet1,
        CAST(SUBSTRING(CAST(EndIPInt AS varbinary),5, 2) AS tinyInt) AS EndOctet2,
        CAST(SUBSTRING(CAST(EndIPInt AS varbinary),6, 2) AS tinyInt) AS EndOctet3,
        CAST(SUBSTRING(CAST(EndIPInt AS varbinary),7, 2) AS tinyInt) AS EndOctet4
    FROM CTE
)