Sqlserver IPV6处理函数或数值转换
我正在寻找在IPv6中处理SqlServer CIDR地址范围的方法。
我获得的数据被格式化为下面的格式，并有以下列：
Int_IP_Start, Int_IP_End, CIDR_Range, ASN, Name对于IPv4
"2868826112","2868826623","170.254.208.0/23","265116","Wave Net"
"2868827136","2868828159","170.254.212.0/22","265381","Furtado & Furtado Provedores LTDA"
"2868828928","2868829183","170.254.219.0/24","264770","Leonir Remussi (KDMNET)"
"2868829184","2868829439","170.254.220.0/24","265373","NET TELECOMUNICACOES LTDA - ME"
"2868829440","2868829695","170.254.221.0/24","265373","NET TELECOMUNICACOES LTDA - ME"
"2868830208","2868831231","170.254.224.0/22","265382","TELECOM LTDA ME"
"2868831232","2868831487","170.254.228.0/24","27951","Media Commerce Partners S.A"这也是一样的，但是对于IPv6
"58568835385568506466387976054061924352","58568835464796668980652313647605874687","2c0f:f288::/32","328039","JSDAAV-ZA-Telecoms-AS"
"58568842991472107835764385034281156608","58568842991473316761583999663455862783","2c0f:f2e8::/48","37182","TERNET"
"58568844892948008178108487279335964672","58568844892949217103928101908510670847","2c0f:f300::/48","37153","Hetzner"
"58568847428249208634567290272742375424","58568847507477371148831627866286325759","2c0f:f320::/32","37126","BELL-TZ"
"58568849329725108976911392517797183488","58568849408953271491175730111341133823","2c0f:f338::/32","327983","Interworks-Wireless-Solutions"单个IPv6的数值表示是相当大的，因为地址空间相当于128位整数。
这里的目标是有一种方法让数据库查询返回IP是否是CSV存储范围的一部分。
对于IPv4来说，这很简单，您可以使用IP并将其转换为INT32等效的IP。
由于在INT128中没有SqlServer数据类型，所以我有以下问题：
-Is有一种方法来创建一个自定义类型来存储128位的bigint？(只需要执行大于或小于操作的操作)
-Is有办法正确处理SqlServer中的IP地址范围吗？
编辑：
以下是IPv6地址可以表示的最大数字：
string LongestIp = "ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff";
var SerializedIp = IPNetwork.ToBigInteger(IPAddress.Parse(LongestIp));
Console.WriteLine(SerializedIp.ToString());此c#代码输出340282366920938463463374607431768211455。
试图插入这个
declare @ipv6Decimals table (Ip decimal(38,0));
insert into @ipv6Decimals (Ip) values (58568844892949217103928101908510670847); --this is okay,
--When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1
insert into @ipv6Decimals (Ip) values (99999999999999999999999999999999999999); --This is the largest numeric(38,0) will fit
insert into @ipv6Decimals (Ip) values (340282366920938463463374607431768211455);-- This is too largeMsg 1007，15级，状态1，第3行号码‘3402823669209384634633746074368211455’超出数值表示范围(最高精度38)。
回答 1
Database Administration用户
发布于 2018-08-21 13:28:05
是否有一种方法可以创建一个自定义类型来存储128位的bigint？(只需要执行大于或小于操作的操作)
您可能不需要自定义类型- numeric(38,0)在这里可能是合适的(虽然我不知道有效的128位整数的全部范围，或者IPv6是否被限制在适合的值范围内)：
CREATE TABLE #ips
(
iplow numeric(38,0),
iphigh numeric(38,0),
c varchar(64),
d int,
e nvarchar(128)
);
INSERT #ips VALUES
(58568835385568506466387976054061924352,58568835464796668980652313647605874687,
'2c0f:f288::/32',328039,N'JSDAAV-ZA-Telecoms-AS'),
(58568842991472107835764385034281156608,58568842991473316761583999663455862783,
'2c0f:f2e8::/48',37182 ,N'TERNET'),
(58568844892948008178108487279335964672,58568844892949217103928101908510670847,
'2c0f:f300::/48',37153 ,N'Hetzner'),
(58568847428249208634567290272742375424,58568847507477371148831627866286325759,
'2c0f:f320::/32',37126 ,N'BELL-TZ'),
(58568849329725108976911392517797183488,58568849408953271491175730111341133823,
'2c0f:f338::/32',327983,N'Interworks-Wireless-Solutions');
DECLARE @ip numeric(38,0) = 58568842991472107835764385034281156617;
SELECT iplow,iphigh,c,d,e
FROM #ips
WHERE @ip BETWEEN iplow AND iphigh;
DROP TABLE #ips;如果这对您需要支持的地址不起作用，那么您可能需要将网络前缀(48位)、子网ID (16位)和接口ID (64位)分离成三个单独的数字，然后再将它们传递到Server。where子句将变得更复杂，但这可能比尝试将IPv6表示为单个数字更简单。
是否有适当处理SqlServer中IP地址范围的方法？
Server没有内置的能力来理解IP地址是什么.
https://dba.stackexchange.com/questions/215477
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号