主页 / dba / 问题 / 230787
Accepted
J Weezy
Asked: 2019-02-27 09:46:32 +0800 CST

将字符串 IP 地址转换为 VARBINARY(16) 时无法在 IPv6 范围之间搜索

  • 772

我正在尝试搜索 IPGeolocation 数据，该数据由数据供应商提供为 IPv4 和 IPv6 的开始和结束范围。但是，当我转换提供的 IP 时，我得到的结果不正确。

为了按范围搜索，我将VARCHAR(39)IPv6 的表示形式转换为VARBINARY（即十六进制），然后使用BETWEEN运算符查看搜索到的十六进制值是否在特定范围内。

查询逻辑：

SELECT CONVERT(VARBINARY(16), '2600:8800:6a06:2000:1d29:3b7:8c7c:271b')

搜索：

IP: 2600:8800:6a06:2000:1d29:3b7:8c7c:271b

IpHex: 0x323630303A383830303A366130363A32

结果 1

启动：2600:8800:490:0:0:0:0:0

IpStartHex: 0x323630303A383830303A3439303A303A

IpEnd: 2600:8800:77f:ffff:ffff:ffff:ffff:ffff

IpEndHex: 0x323630303A383830303A3737663A6666

结果 2

启动：2600:8800:6a06:1d01:0:0:0:0

IpStartHex: 0x323630303A383830303A366130363A31

IpEnd: 2600:8800:6a06:24ff:ffff:ffff:ffff:ffff

IpEndHex: 0x323630303A383830303A366130363A32

sql-server t-sql

1 个回答

  1. Best Answer

    最初您的转换有两个问题：

    1. 这是错误的转换“类型”。您正在将字符串转换为字符本身的十六进制/二进制表示形式。例如，“26”在十六进制中将变为 0x3236，因为 0x32 与 doing 相同CHAR(0x32)，0x36 与 相同CHAR(0x36)，或者它可以称为数字“2”的 ASCII 值。你想要的是简单地将字符串“26”（代表一个字节）的形式更改为0x26. 为此，您需要为CONVERT函数使用样式编号“2”。例如：

      SELECT CONVERT(VARBINARY(2), '26'), CONVERT(VARBINARY(2), '26', 2);
-- 0x3236    0x26

      这就是你0x323630303A383830303A366130363A32从2600:8800:6a06:2000:1d29:3b7:8c7c:271b. 您实际上只从该字符串中获取前 16 个字符，它们是：2600:8800:6a06:2。左边的两个字我上面直接解释了。右侧的 3 个字符6:2是0x363A32十六进制值的右侧，因为0x36=“6”、0x3A=“:”和0x32=“2”。

    2. 您需要去掉冒号，因为它们不是有效的二进制数字。但是冒号作为分隔符存在是有原因的：有 8 个段，如果删除前导“0”（这是有效的），那么 0 确实需要是0x0000，而不是0x00or 0x0。因此，您需要确保每个段返回为 4 个十六进制数字。

    您可以通过以下方式完成所有这些：

    1. 在冒号上分裂
    2. 用“0”左填充
    3. 转换为BINARY(2)
    4. 将这些片段连接在一起

    如果您使用的是 SQL Server 2016 或更新版本，则可以使用内置STRING_SPLIT函数。或者，如果您使用的是 SQL Server 2016 之前的版本，则可以使用已有的任何拆分器，也可以使用我的SQL# SQLCLR 库的免费版本（免费版中有多个字符串拆分器）。

    例如：

    SELECT RIGHT('0000' + prt.[SplitVal], 4) AS [string],
       CONVERT(BINARY(2), RIGHT('0000' + prt.[SplitVal], 4), 2) AS [hex]
FROM   SQL#.String_Split4k('2600:100:400:0:0:0:0:0', ':', 1) prt;

    回报：

    string    hex
2600      0x2600
0100      0x0100
0400      0x0400
0000      0x0000
0000      0x0000
0000      0x0000
0000      0x0000
0000      0x0000

    那只是一种查看碎片是什么的方法。要将它们组合成一个十六进制值，您可以执行以下操作：

    DECLARE @IPv6 VARBINARY(16) = 0x;

SELECT @IPv6 += CONVERT(BINARY(2), RIGHT('0000' + prt.[SplitVal], 4), 2)
FROM SQL#.String_Split4k('2600:100:400:0:0:0:0:0', ':', 1) prt;

SELECT @IPv6;
-- 0x26000100040000000000000000000000

    您可以创建一个函数来处理此转换。然后您可以存储这些VARBINARY(16)值并为它们创建索引，然后转换传入的字符串并将其存储在变量中以在查询中使用。

    这是相同的方法，但使用内置STRING_SPLIT函数：

    DECLARE @IPv6 VARBINARY(16) = 0x;

SELECT @IPv6 += CONVERT(BINARY(2), RIGHT('0000' + prt.[value], 4), 2)
FROM STRING_SPLIT('2600:100:400:0:0:0:0:0', ':') prt;

SELECT @IPv6;
-- 0x26000100040000000000000000000000

    请记住，有些人STRING_SPLIT在这种情况下犹豫不决，因为这里返回拆分部分的顺序很重要，因为如果它们以不同的顺序返回，它可以更改值，而内置函数不会' 返回行号。我个人看不出返回值是如何乱序返回的，但这只是一个有根据的猜测，并不能保证。如果你想要保证，那就不要使用STRING_SPLIT内置函数。

    PS上面显示的解决方案（即在冒号上拆分）不处理“简化”符号/语法，其中“::”可以替换一个或多个连续范围的“0000”。例如：2600:100:400::0。因此，该解决方案要求所有 8 个段都至少包含“0”。

    PPS如果您在应用程序或 SQLCLR 或 SSIS 中使用 .NET，则可以结合使用IPAddress.TryParse和IPAddress.GetAddressBytes来处理简化的“::”表示法。或者，您总是可以编写一堆代码来用纯 T-SQL 处理它。

    PPPS INET_IPv6toBinary将在SQL#的下一个版本（4.3 版）中可用:-)

    • 4

相关问题