我正在寻找在 SqlServer 中处理 IPv6 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。
由于 SqlServer 中没有 INT128 数据类型，我有以下问题：
- 有没有办法创建一个自定义类型来存储 128 位的 bigint？（只需要进行大于或小于运算）
- 有没有办法正确处理 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 large
消息 1007，级别 15，状态 1，第 3 行数字“340282366920938463463374607431768211455”超出了数字表示的范围（最大精度 38）。