在两个IP范围之间选择记录

15

我有一张表格,其中存储了IDNameCodeIPLowIPHigh

1, Lucas, 804645, 192.130.1.1, 192.130.1.254
2, Maria, 222255, 192.168.2.1, 192.168.2.254
3, Julia, 123456, 192.150.3.1, 192.150.3.254

现在,如果我有一个IP地址为192.168.2.50,我该如何检索相匹配的记录?

编辑:

根据Gordon的答案(我遇到了编译错误),这是我所拥有的:

select PersonnelPC.*
from (select PersonnelPC.*,
             (
              cast(parsename(iplow, 4)*1000000000 as decimal(12, 0)) +
              cast(parsename(iplow, 3)*1000000 as decimal(12, 0)) +
              cast(parsename(iplow, 2)*1000 as decimal(12, 0)) +
              (parsename(iplow, 1))
             ) as iplow_decimal,
            (
              cast(parsename(iphigh, 4)*1000000000 as decimal(12, 0)) +
              cast(parsename(iphigh, 3)*1000000 as decimal(12, 0)) +
              cast(parsename(iphigh, 2)*1000 as decimal(12, 0)) +
              (parsename(iphigh, 1))
             ) as iphigh_decimal
      from PersonnelPC
     ) PersonnelPC
where 192168002050 between iplow_decimal and iphigh_decimal;

但是这会给我一个错误:

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

有任何想法吗?


请描述您迄今为止的努力和这些努力的结果。 - Dan Bracuk
3
你将一个人类友好的文本表示方式存储地址,但该格式中没有内置理解IP范围语义的比较功能。相反,使用数字比较,例如:如何高效搜索IP地址范围? - Alex K.
3
请确保测试子网不仅仅为 /24 的情况。 - Kieveli
将较大的值转换为BIGINT以避免算术溢出错误。更好的做法是将其转换为decimal(12,0)以保持一致性。(cast(192168002050 as decimal(12,0))) - EduardoCMB
8个回答

8
痛苦的是,SQL Server的字符串操作函数很差。不过,它提供了 parsename() 函数。这种方法将IP地址转换为一个大的十进制值进行比较:
select t.*
from (select t.*,
             (cast(parsename(iplow, 4)*1000000000.0 as decimal(12, 0)) +
              cast(parsename(iplow, 3)*1000000.0 as decimal(12, 0)) +
              cast(parsename(iplow, 2)*1000.0 as decimal(12, 0)) +
              cast(parsename(iplow, 1) as decimal(12, 0))
             ) as iplow_decimal,
             (cast(parsename(iphigh, 4)*1000000000.0 as decimal(12, 0)) +
              cast(parsename(iphigh, 3)*1000000.0 as decimal(12, 0)) +
              cast(parsename(iphigh, 2)*1000.0 as decimal(12, 0)) +
              cast(parsename(iphigh, 1) as decimal(12, 0))
             ) as iphigh_decimal
      from t
     ) t
where 192168002050 between iplow_decimal and iphigh_decimal;

需要注意的是,IP地址通常以4个字节的无符号整数形式存储在数据库中。这使得比较更加容易……尽管您需要复杂的逻辑(通常包装在一个函数中)将这些值转换为可读格式。


2
有趣的使用基数转换使其可读(我使用4个4字节长整型存储IP)。强制转换会使其变慢 - 考虑将IP存储为非人类可读格式,或者添加触发器来更新第二个字段,存储与人类可读IP地址匹配的长整型值。 - Kieveli
1
@Gordon - 我做了一个改变,我认为可以解决隐式转换为“int”的OP问题。如果不正确,请恢复原状。 - Ed Harper
@EdHarper,你能否请发表一下你的建议? - alwaysVBNET
1
@alwaysVBNET - 我已经有了;当前版本的答案应该包含我的编辑(即将.0添加到标量乘数)。 - Ed Harper
@EdHarper 很棒的帮助 - alwaysVBNET
显示剩余2条评论

6

试试这种简单的检查范围的方法

DECLARE @IP NVARCHAR(30)='192.168.500.1'

SELECT  * FROM 
Branches
WHERE
CAST (PARSENAME(@IP,4) AS INT)>=CAST(PARSENAME(IPLow,4) AS INT) AND CAST(PARSENAME(@IP,3) AS INT)>=CAST(PARSENAME(IPLow,3) AS INT) AND CAST(PARSENAME(@IP,2) AS INT)>=CAST(PARSENAME(IPLow,2) AS INT) AND CAST(PARSENAME(@IP,1) AS INT)>=CAST(PARSENAME(IPLow,1) AS INT)
AND
CAST(PARSENAME( @IP,4) AS INT) <= CAST(PARSENAME(IPHigh ,4) AS INT) AND CAST(PARSENAME(@IP ,3) AS INT) <=CAST(PARSENAME(IPHigh ,3) AS INT) AND CAST(PARSENAME(@IP ,2) AS INT) <=CAST(PARSENAME(IPHigh ,2) AS INT) AND CAST(PARSENAME(@IP ,1) AS INT)<=CAST(PARSENAME(IPHigh ,1) AS INT)

根据 @Ed Harper 的评论,需要进行投掷操作。


它不会获取任何结果 - alwaysVBNET
1
以目前的形式,这是行不通的,因为PARSENAME返回字符串,在字符串比较中,50>254。您必须将所有对PARSENAME的调用的输出强制转换为数字类型(可能是int),才能使比较正常工作。另外 - 您确定您的比较方向是正确的吗? - Ed Harper
@JaydipJ 抱歉,它不起作用。请在范围“192.168.130.1”-“192.168.130.254”内尝试使用“192.168.130.2”。 - alwaysVBNET
@alwaysVBNET 我已根据EdHarper的评论进行了更新。 - Jaydip Jadhav
@JaydipJ正在修复一些括号。好答案+1 - alwaysVBNET

3

使用此函数可以将任何IP地址转换为每个部分具有3位数字的形式。这样,您可以进行普通的字母数字比较。如果需要,也可以返回BIGINT ...

CREATE FUNCTION dbo.IPWidth3(@IP VARCHAR(100))
RETURNS VARCHAR(15)
BEGIN
DECLARE @RetVal VARCHAR(15);
WITH Splitted AS
(
    SELECT CAST('<x>' + REPLACE(@IP,'.','</x><x>') + '</x>' AS XML) AS IPSplitted 
)
SELECT @RetVal = STUFF(
        (
        SELECT '.' + REPLACE(STR(Part.value('.','int'),3),' ','0')
        FROM Splitted.IPSplitted.nodes('/x') AS One(Part)
        FOR XML PATH('')
        ),1,1,'') 
FROM Splitted;

RETURN @RetVal;
END
GO

DECLARE @IP VARCHAR(100)='192.43.2.50';
SELECT dbo.IPWidth3(@IP);

结果

192.043.002.050
为了响应Ed Harper在这里的评论,同样的函数返回一个DECIMAL(12,0)
CREATE FUNCTION dbo.IP_as_Number(@IP VARCHAR(100))
RETURNS DECIMAL(12,0)
BEGIN
DECLARE @RetVal DECIMAL(12,0);
WITH Splitted AS
(
    SELECT CAST('<x>' + REPLACE(@IP,'.','</x><x>') + '</x>' AS XML) AS IPSplitted 
)
SELECT @RetVal = 
        CAST((
        SELECT REPLACE(STR(Part.value('.','int'),3),' ','0')
        FROM Splitted.IPSplitted.nodes('/x') AS One(Part)
        FOR XML PATH('')
        ) AS DECIMAL(12,0))
FROM Splitted;

RETURN @RetVal;
END
GO

DECLARE @IP VARCHAR(100)='192.43.2.50';
SELECT dbo.IP_as_Number(@IP);

如果您使用此方法,值得注意的是,.Net IPAddress.Parse 方法将解释IPv4地址中前导零填充的部分作为八进制值 - 因此,您需要小心如何将这些值暴露给消费代码。这可能会影响其他语言。 - Ed Harper
@EdHarper, 谢谢,我不知道这个... 我编辑了我的答案,并加上了一个返回DECIMAL(12,0)版本。 - Shnugo

1
使用以下代码获取4列中的ipLow / IPHigh。您可以使用这些列来比较IP地址。
DECLARE@ip VARCHAR(50)='192.168.0.81' 
SELECT (SUBSTRING((@ip), 0,
patindex('%.%',
(@ip))))

,
substring((REPLACE(@ip, (SUBSTRING((@ip), 0,
patindex('%.%',
(@ip)) + 1)),
'')),
0,
patindex('%.%',
((REPLACE(@ip, (SUBSTRING((@ip), 0,
patindex('%.%',
(@ip)) + 1)),
''))))),
SUBSTRING((SUBSTRING(@ip, LEN((SUBSTRING((@ip), 0,
patindex('%.%',
(@ip))))) + 2 + LEN(substring((REPLACE(@ip, (SUBSTRING((@ip), 0,
patindex('%.%',
(@ip)) + 1)),
'')),
0,
patindex('%.%',
((REPLACE(@ip, (SUBSTRING((@ip), 0,
patindex('%.%',
(@ip)) + 1)),
'')))))) + 1,
LEN(@IP) - 1 - LEN(reverse(SUBSTRING(reverse(@ip), 0,
patindex('%.%',
reverse(@ip))))))), 0,
PATINDEX('%.%',
(SUBSTRING(@ip, LEN((SUBSTRING((@ip), 0,
patindex('%.%',
(@ip))))) + 2 + LEN(substring((REPLACE(@ip, (SUBSTRING((@ip), 0,
patindex('%.%',
(@ip)) + 1)),
'')),
0,
patindex('%.%',
((REPLACE(@ip, (SUBSTRING((@ip), 0,
patindex('%.%',
(@ip)) + 1)),
'')))))) + 1,
LEN(@IP) - 1 - LEN(reverse(SUBSTRING(reverse(@ip), 0,
patindex('%.%',
reverse(@ip))))))

))),
reverse(SUBSTRING(reverse(@ip), 0,
patindex('%.%',
reverse(@ip))))

1
考虑以下示例将地址转换为数字。
CREATE FUNCTION dbo.IPAddressAsNumber (@IPAddress AS varchar(15))
RETURNS bigint
BEGIN
RETURN
 CONVERT (bigint,
  CONVERT(varbinary(1), CONVERT(int, PARSENAME(@IPAddress, 4))) +
  CONVERT(varbinary(1), CONVERT(int, PARSENAME(@IPAddress, 3))) +
  CONVERT(varbinary(1), CONVERT(int, PARSENAME(@IPAddress, 2))) +
  CONVERT(varbinary(1), CONVERT(int, PARSENAME(@IPAddress, 1))) )
END

这样,您就可以使用标准运算符(如BETWEEN)来查找表中范围内的行。

DECLARE @t table (ID int, Name varchar(50), Code int, IPLow varchar(15), IPHigh varchar(15))
INSERT INTO @t VALUES 
 (1, 'Lucas', 804645, '192.130.1.1', '192.130.1.254'),
 (2, 'Maria', 222255, '192.168.2.1', '192.168.2.254'),
 (3, 'Julia', 123456, '192.150.3.1', '192.150.3.254')

SELECT * FROM @t
WHERE dbo.IPAddressAsNumber('192.168.2.50')
 BETWEEN dbo.IPAddressAsNumber(IPLow) AND dbo.IPAddressAsNumber(IPHigh)
该方案基本上使用PARSENAME来分离地址的每个部分,将每个部分转换为SQL二进制字符串,将这些字符串连接在一起以获得表示地址的单个SQL二进制字符串,并将结果显示为bigint。 在十六进制值的文本表示中,可以将其视为将4个部分合并在一起,即192(0xC0)+ 168(0xA8)+ 2(0x02)+ 50(0x32),得到0xC0A80232。当您将该组合字符串转换为其二进制数字(0和1)时,您将得到可被认为是网络堆栈中用于地址路由和子网掩码表的二进制形式的地址。当您将其转换为一个无符号整数(或在此情况下为bigint)的数字形式时,您会得到3232236082。 有趣的是,该方案提供了一个“数字”,可以在许多方面替代原始地址。例如,您可以ping数字2130706433而不是地址127.0.0.1-- Windows中的名称解析器将类似于DNS用于查找主机名的地址转换。 为了完整起见,以下是另一个函数,可用于将数字形式转换回标准字符串形式。
CREATE FUNCTION dbo.IPAddressFromNumber (@IPNumber AS bigint)
RETURNS varchar(15)
BEGIN
RETURN
 CONVERT (varchar(15),
  CONVERT(varchar(3), CONVERT(int, SUBSTRING(CONVERT(varbinary(4), @IPNumber), 1,1))) + '.' +
  CONVERT(varchar(3), CONVERT(int, SUBSTRING(CONVERT(varbinary(4), @IPNumber), 2,1))) + '.' +
  CONVERT(varchar(3), CONVERT(int, SUBSTRING(CONVERT(varbinary(4), @IPNumber), 3,1))) + '.' +
  CONVERT(varchar(3), CONVERT(int, SUBSTRING(CONVERT(varbinary(4), @IPNumber), 4,1))) )
END

0

我一开始也是像Gordon的回答那样想,但后来意识到你实际上不需要处理数字。如果你在地址的每个部分都加上零填充,那么字符串比较就可以工作:

DECLARE @search varchar(50) = '192.168.2.50';
WITH DATA AS (
    SELECT * FROM ( values 
            (1, 'Lucas', '192.130.1.1', '192.130.1.254'),
            (2, 'Maria', '192.168.2.1', '192.168.2.254'),
            (3, 'Julia', '192.150.3.1', '192.150.3.254')
    ) AS tbl (ID,Name,IPLow,IPHigh)
)
SELECT *
FROM DATA
WHERE REPLACE(STR(PARSENAME( @search, 4 ), 3, 0), ' ', '0')
    + REPLACE(STR(PARSENAME( @search, 3 ), 3, 0), ' ', '0')
    + REPLACE(STR(PARSENAME( @search, 2 ), 3, 0), ' ', '0')
    + REPLACE(STR(PARSENAME( @search, 1 ), 3, 0), ' ', '0')

    BETWEEN

      REPLACE(STR(PARSENAME( IPLow, 4 ), 3, 0), ' ', '0')
    + REPLACE(STR(PARSENAME( IPLow, 3 ), 3, 0), ' ', '0')
    + REPLACE(STR(PARSENAME( IPLow, 2 ), 3, 0), ' ', '0')
    + REPLACE(STR(PARSENAME( IPLow, 1 ), 3, 0), ' ', '0')

    AND

      REPLACE(STR(PARSENAME( IPHigh, 4 ), 3, 0), ' ', '0')
    + REPLACE(STR(PARSENAME( IPHigh, 3 ), 3, 0), ' ', '0')
    + REPLACE(STR(PARSENAME( IPHigh, 2 ), 3, 0), ' ', '0')
    + REPLACE(STR(PARSENAME( IPHigh, 1 ), 3, 0), ' ', '0')
当然,你可以将其放在UDF中以简化操作,但要注意在大查询中可能会影响性能。
CREATE FUNCTION dbo.IP_Comparable(@IP varchar(50))
RETURNS varchar(50)
WITH SCHEMABINDING
BEGIN
    RETURN REPLACE(STR(PARSENAME( @IP, 4 ), 3, 0), ' ', '0')
         + REPLACE(STR(PARSENAME( @IP, 3 ), 3, 0), ' ', '0')
         + REPLACE(STR(PARSENAME( @IP, 2 ), 3, 0), ' ', '0')
         + REPLACE(STR(PARSENAME( @IP, 1 ), 3, 0), ' ', '0')
END
GO

DECLARE @search varchar(50) = '192.168.2.50';
WITH DATA AS (
    SELECT * FROM ( values 
        (1, 'Lucas', '192.130.1.1', '192.130.1.254'),
        (2, 'Maria', '192.168.2.1', '192.168.2.254'),
        (3, 'Julia', '192.150.3.1', '192.150.3.254')
    ) AS tbl (ID,Name,IPLow,IPHigh)
)
SELECT *
FROM DATA
WHERE dbo.IP_Comparable(@search) BETWEEN dbo.IP_Comparable(IPLow) AND dbo.IP_Comparable(IPHigh)

这将避免您所遇到的整数溢出问题。


0
select *
from ip a
join ip_details b
on a.ip_address >= b.ip_start
and a.ip_address <= b.ip_end;

在这里,表“a”包含IP地址列表,表“b”包含IP范围。

我们可以直接比较字符串而不是将IP地址转换为数字,它会进行逐字节比较。

这对我来说有效(PostgreSQL)。


-3

这取决于您要查找高还是低的记录。

select * from table where IPlow like '192.168.2.50' or IPHigh like '192.168.2.50'

显然,范围是未知的,应该从数据中可用的内容中进行搜索。否则,这将会太容易了。 - alwaysVBNET
2
因为这种方法无法解决问题,所以被 Downvoted。 - Dan Bracuk

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文,