sql - 寻找一种更有效的方法将 IP 地址分解为八位字节

Question

我有一个存储 IP 地址掩码的表，用于验证是否允许用户根据其 IP 地址访问资源。为了验证他们的 IP 地址，我将他们的地址和掩码分成八位字节并进行比较。我有一个可行的方法，但它不太适合我，因为它似乎对字符串操作过于繁重，这在 T-SQL 中很慢。这让我觉得这是一种“蛮力”方法，一点也不优雅。我希望有人可以提供一些改进，同时保持相同的输出。

部分示例掩码如下:

• *.*.*.* - 这是最常见的，它是“对于所有可能的 IP地址”
• 172.16.*.* - 指定以 172.16 开头的 IP 地址
• 172.16.0-10.* - 这指定了以 172.16 开头但第三个八位位组介于 0 和 10 之间的 IP 地址
• 172.16.99.10 - 这指定了一个单独的地址。

为了解析这些，我声明了一个临时表:

DECLARE @Rights TABLE(
IPAddr char(15)
, octet1 char(7)
, octet1max char(3)
, octet2 char(7)
, octet2max char(3)
, octet3 char(7)
, octet3max char(3)
, octet4 char(7)
, octet4max char(3)
, dot int
, tlen int
, tempaddr char(15)
)

我首先将数据库表中的记录插入临时表:

INSERT @Rights(IPAddress, dot, tlen, tempaddr)
SELECT IPAddress, CHARINDEX('.',IPAddress), LEN(IPAddress), IPAddress
FROM IPAccessRights_Info

然后，我针对这个临时表运行 4 个 UPDATE 语句:

UPDATE @Rights
SET octet1 = SUBSTRING(tempaddr, 0, dot)
    , tempaddr = SUBSTRING(tempaddr, dot+1, LEN(tempaddr)-dot)
    , dot = CHARINDEX('.', SUBSTRING(tempaddr, dot+1, LEN(tempaddr)-dot))
    , tlen = LEN(SUBSTRING(tempaddr, dot+1, LEN(tempaddr)-dot))
UPDATE @Rights
SET octet2 = SUBSTRING(tempaddr, 0, dot)
    , tempaddr = SUBSTRING(tempaddr, dot+1, LEN(tempaddr)-dot)
    , dot = CHARINDEX('.', SUBSTRING(tempaddr, dot+1, LEN(tempaddr)-dot))
    , tlen = LEN(SUBSTRING(tempaddr, dot+1, LEN(tempaddr)-dot))
UPDATE @Rights
SET octet3 = SUBSTRING(tempaddr, 0, dot)
    , octet4 = SUBSTRING(tempaddr, dot+1, LEN(tempaddr)-dot)
    , dot = NULL
    , tlen = LEN(IPAddr)
    , tempaddr = NULL

-- Parse out any ranges
UPDATE @Rights
    SET octet1 = CASE WHEN CHARINDEX('-', octet1) > 0 THEN SUBSTRING(octet1, 0, CHARINDEX('-', octet1)) ELSE octet1 END
        , octet1max = CASE WHEN CHARINDEX('-', octet1) > 0 THEN SUBSTRING(octet1, CHARINDEX('-', octet1)+1, LEN(octet1)) ELSE NULL END
        , octet2 = CASE WHEN CHARINDEX('-', octet2) > 0 THEN SUBSTRING(octet2, 0, CHARINDEX('-', octet2)) ELSE octet2 END
        , octet2max = CASE WHEN CHARINDEX('-', octet2) > 0 THEN SUBSTRING(octet2, CHARINDEX('-', octet2)+1, LEN(octet2)) ELSE NULL END
        , octet3 = CASE WHEN CHARINDEX('-', octet3) > 0 THEN SUBSTRING(octet3, 0, CHARINDEX('-', octet3)) ELSE octet3 END
        , octet3max = CASE WHEN CHARINDEX('-', octet3) > 0 THEN SUBSTRING(octet3, CHARINDEX('-', octet3)+1, LEN(octet3)) ELSE NULL END
        , octet4 = CASE WHEN CHARINDEX('-', octet4) > 0 THEN SUBSTRING(octet4, 0, CHARINDEX('-', octet4)) ELSE octet4 END
        , octet4max = CASE WHEN CHARINDEX('-', octet4) > 0 THEN SUBSTRING(octet4, CHARINDEX('-', octet4)+1, LEN(octet4)) ELSE NULL END

完成后，我可以使用 octet1-octet4 和 octet1max-octet4max 中的值来匹配我的 IP 地址。

当然，当我们都使用 IPv6 时，这将是一个完全不同的蠕虫 jar ...

Answer 1

这Tip-of-the-day ( and this one ) 描述了如何使用 T-SQL PARSENAME function分隔 IP 地址以及由 4 部分组成的数据库对象名称。

DECLARE @IPAddresses TABLE ( [IPAddress] VARCHAR(20))

INSERT INTO @IPAddresses VALUES ('10.0.0.1')
INSERT INTO @IPAddresses VALUES ('255.255.255.255')
INSERT INTO @IPAddresses VALUES ('192.123.545.12')
INSERT INTO @IPAddresses VALUES ('1.2.3.4')

SELECT * FROM @IPAddresses
ORDER BY CAST(PARSENAME([IPAddress], 4) AS INT),
         CAST(PARSENAME([IPAddress], 3) AS INT),
         CAST(PARSENAME([IPAddress], 2) AS INT),
         CAST(PARSENAME([IPAddress], 1) AS INT)

IPAddress
----------------
1.2.3.4
10.0.0.1
192.123.545.12
255.255.255.255