如何在SQL中计算IPv6 CIDR路由前缀? [英] How to calculate a IPv6 CIDR route prefix in SQL?

问题描述

我一直在使用在SQL Server 2012实例上运行的T-SQL从IPv4和IPv6地址范围生成CIDR.通常，我们的应用程序(在数据库外部)负责计算CIDR，但是我目前需要在数据库中进行CIDR计算.由于IPv6太大而无法存储在bigint数据类型中，因此我们将IP地址存储为binary(4)或binary(16).

I've been working on generating a CIDR from ranges of IPv4 and IPv6 addresses using T-SQL running on a SQL Server 2012 instance. Generally our application (outside of the db) is responsible for calculating a CIDR, but I presently have need to do the CIDR calculation in the database. Because IPv6 is too large to store in the bigint datatype we store IP addresses as either binary(4) or binary(16).

如果有点难看，则计算IPv4范围的路由前缀相对简单:

Calculating the route prefix for an IPv4 range is relatively simple if a little ugly:

declare @ipv4_begin binary(4)
       ,@ipv4_end binary(4)

set @ipv4_begin = 0xC0A80000 -- '192.168.000.000'
set @ipv4_end = 0xC0A8FFFF   -- '192.168.255.255'

select 32 - LOG(
                Cast(@ipv4_end As bigint)
                - Cast(@ipv4_begin As bigint) + 1, 2
               ) as ipv4_route_prefix

遗憾的是，为IPv6修改的同一查询不起作用.之所以不起作用，是因为IPv6地址收件人大于bigint数据类型中可以存储的地址(我们使用binary(4)和binary(16)进行存储的原因):

Sadly the same query modified for IPv6 does not work. The reason it does not work is because IPv6 addressees are larger than what can be stored in the bigint datatype (the reason we use binary(4) and binary(16) for storage):

declare @ipv6_begin binary(16)
       ,@ipv6_end binary(16)

set @ipv6_begin = 0xFC000000000000000000000000000000 -- fc00:: 
set @ipv6_end = 0xFC00000000000000FFFFFFFFFFFFFFFF   -- fc00::ffff:ffff:ffff:ffff

-- This will cause error: 'An invalid floating point operation occurred.'
select 128 - LOG(
                 Cast(@ipv6_end As bigint)
                 - Cast(@ipv6_begin As bigint) + 1, 2
                ) as ipv6_route_prefix

除了笨拙的按位运算(最终没有用)之外，我还没有提出任何可以在数据库中进行此计算的东西.

Aside from wonky bitwise operations (that ultimately didn't work) I haven't come up anything that will do this calculation in the database.

能否从T-SQL中的IPv6地址范围计算出IPv6 CIDR的路由前缀?

Can a route prefix for an IPv6 CIDR be calculated from an IPv6 address range in T-SQL?

推荐答案

好了，您已经有了一个针对IPv4的灵巧技巧-只需将值切成我们可以处理的最大块，然后重复该技巧即可.

Well, you already have a neat trick for IPv4 -- just chop the value up into the biggest chunk we can handle and repeat the trick.

SELECT ISNULL(MIN(32 - B + N), 128) 
FROM (VALUES
    (LOG(
        CONVERT(BIGINT, SUBSTRING(@ip_end,    1, 4)) - 
        CONVERT(BIGINT, SUBSTRING(@ip_begin,  1, 4)
        ) + 1, 2),  0),
    (LOG(
        CONVERT(BIGINT, SUBSTRING(@ip_end,    5, 4)) - 
        CONVERT(BIGINT, SUBSTRING(@ip_begin,  5, 4)
        ) + 1, 2), 32),
    (LOG(
        CONVERT(BIGINT, SUBSTRING(@ip_end,    9, 4)) - 
        CONVERT(BIGINT, SUBSTRING(@ip_begin,  9, 4)
        ) + 1, 2), 64),
    (LOG(
        CONVERT(BIGINT, SUBSTRING(@ip_end,   13, 4)) - 
        CONVERT(BIGINT, SUBSTRING(@ip_begin, 13, 4)
        ) + 1, 2), 96)
) AS Bits(B, N)
WHERE B <> 0;

我们确定每个块中第一个置位的位置，然后选择最低的这样的位-如果没有这样的位，则所有位都匹配(ISNULL涵盖了这种情况).如果您将"128"替换为"32"，这也适用于IPv4，尽管显然您已经有了一个表达式.我们可以将其打包成对两个都适用的函数:

We determine the position of the first set bit in each chunk, then pick the lowest such bit -- if there is no such bit, all bits match (the ISNULL covers that case). This works for IPv4 too if you replace the "128" with "32", though obviously you already have an expression for that. We can pack it up in a function that will work for both:

CREATE FUNCTION dbo.CidrPrefixFromRange(@ip_begin VARBINARY(16), @ip_end VARBINARY(16)) 
RETURNS TABLE AS
RETURN
    SELECT ISNULL(MIN(32 - B + N), DATALENGTH(@ip_begin) * 8) AS Prefix
    FROM (VALUES
        (LOG(
            CONVERT(BIGINT, SUBSTRING(@ip_end,    1, 4)) - 
            CONVERT(BIGINT, SUBSTRING(@ip_begin,  1, 4)
            ) + 1, 2),  0),
        (LOG(
            CONVERT(BIGINT, SUBSTRING(@ip_end,    5, 4)) - 
            CONVERT(BIGINT, SUBSTRING(@ip_begin,  5, 4)
            ) + 1, 2), 32),
        (LOG(
            CONVERT(BIGINT, SUBSTRING(@ip_end,    9, 4)) - 
            CONVERT(BIGINT, SUBSTRING(@ip_begin,  9, 4)
            ) + 1, 2), 64),
        (LOG(
            CONVERT(BIGINT, SUBSTRING(@ip_end,   13, 4)) - 
            CONVERT(BIGINT, SUBSTRING(@ip_begin, 13, 4)
            ) + 1, 2), 96)
    ) AS Bits(B, N)
    WHERE B <> 0;

样品用途:

-- 192.168.100.0 - 192.168.103.255
SELECT * FROM dbo.CidrPrefixFromRange(0xc0a86400, 0xc0a867ff) -- /22

-- 192.168.0.0 - 192.168.255.255
SELECT * FROM dbo.CidrPrefixFromRange(0xC0A80000, 0xC0A8FFFF) -- /16

-- fc00:: - fc00::ffff:ffff:ffff:ffff
SELECT * FROM dbo.CidrPrefixFromRange(
    0xFC000000000000000000000000000000,
    0xFC00000000000000FFFFFFFFFFFFFFFF
) -- /64

-- 127.0.0.1 - 127.0.0.1
SELECT * FROM dbo.CidrPrefixFromRange(0x7f000001, 0x7f000001) -- /32

这不能保证效率.如果您想要效率，那不是您想在T-SQL中完成的事情. :-)

No promises as to how efficient this is... if you want efficiency, this is not the sort of thing you want to do in T-SQL. :-)

附录:之所以使用表值函数而不是简单的标量值函数(毕竟，我们只返回一个值)，是因为标量值函数在查询中的效果要差得多.内联表值函数可以有效地CROSS APPLY到表.因此，我习惯将每个函数都编写为内联TVF，即使我没有预料到这种用法，也没有什么比标量值函数更好.

Addendum: the reason I use a table-valued function and not a simpler scalar-valued function (after all, we're only returning one value) is that scalar-valued functions perform far worse inside a query. An inline table-valued function can be efficiently CROSS APPLY'd to a table. For that reason I write every function as an inline TVF as a matter of habit even if I don't foresee such a use -- anything is better than a scalar-valued function.

这篇关于如何在SQL中计算IPv6 CIDR路由前缀?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！