在 C# 中将 IPv6 格式化为 int 并将其存储在 SQL Server 中

Formatting IPv6 as an int in C# and storing it in SQL Server

提问by Guy

Under IPv4I have been parsing the string representation of IP addresses to Int32and storing them as INTin the SQL Server.

在下，IPv4我一直在解析 IP 地址的字符串表示Int32并将它们存储INT在SQL Server.

Now, with IPv6I'm trying to find out if there's a standard or accepted way to parse the string representation of IPv6to two Int64using C#?

现在，IPv6我试图找出是否有一种标准或可接受的方法来解析IPv6to 2的字符串表示，Int64使用C#?

Also how are people storing those values in the SQL Server- as two fields of BIGINT?

另外人们如何将这些值存储在SQL Server- 作为 的两个字段中BIGINT？

采纳答案by Guffa

Just as an IPv4 address is really a 32 bit number, an IPv6 address is really a 128 bit number. There are different string representations of the addresses, but the actual address is the number, not the string.

正如 IPv4 地址实际上是一个 32 位数字一样，IPv6 地址实际上是一个 128 位数字。地址有不同的字符串表示形式，但实际地址是数字，而不是字符串。

So, you don't convert an IP address to a number, you parse a string representation of the address into the actual address.

因此，您不会将 IP 地址转换为数字，而是将地址的字符串表示形式解析为实际地址。

Not even a decimalcan hold a 128 bit number, so that leaves three obvious alternatives:

甚至 adecimal都不能容纳 128 位数字，因此剩下三个明显的选择：

• store the numeric value split into two bigintfields
• store a string representation of the address in a varcharfield
• store the numeric value in a 16 byte binaryfield

• 存储拆分为两个bigint字段的数值
• 在varchar字段中存储地址的字符串表示
• 将数值存储在 16 字节的binary字段中

Neither is as convenient as storing an IPv4 address in an int, so you have to consider their limitations against what you need to do with the addresses.

两者都不如将 IPv4 地址存储在int.

回答by Richard

The simplest route is to get the framework to do this for you. Use IPAddress.Parseto parse the address, then IPAddress.GetAddressBytesto get the "number" as byte[].

最简单的方法是让框架为你做这件事。使用IPAddress.Parse解析地址，然后IPAddress.GetAddressBytes获得“数”为字节[]。

Finally, divide the array into the first and second 8 bytes for conversion to two Int64s, e.g. by creating a MemoryStreamover the byte array and then reading via a BinaryReader.

最后，将数组分成第一个和第二个 8 个字节以转换为两个 Int64，例如通过MemoryStream在字节数组上创建 a然后通过 a 读取BinaryReader。

This avoids needing to understand all the available short cut representations for IPv6 addresses.

这避免了需要了解 IPv6 地址的所有可用快捷方式表示。

回答by Billy Jo

I use the following method for converting an IP address to two UInt64s (C# 3.0).

我使用以下方法将 IP 地址转换为两个UInt64s (C# 3.0)。

/// <summary>
/// Converts an IP address to its UInt64[2] equivalent.
/// For an IPv4 address, the first element will be 0,
/// and the second will be a UInt32 representation of the four bytes.
/// For an IPv6 address, the first element will be a UInt64
/// representation of the first eight bytes, and the second will be the
/// last eight bytes.
/// </summary>
/// <param name="ipAddress">The IP address to convert.</param>
/// <returns></returns>
private static ulong[] ConvertIPAddressToUInt64Array(string ipAddress)
{
    byte[] addrBytes = System.Net.IPAddress.Parse(ipAddress).GetAddressBytes();
    if (System.BitConverter.IsLittleEndian)
    {
        //little-endian machines store multi-byte integers with the
        //least significant byte first. this is a problem, as integer
        //values are sent over the network in big-endian mode. reversing
        //the order of the bytes is a quick way to get the BitConverter
        //methods to convert the byte arrays in big-endian mode.
        System.Collections.Generic.List<byte> byteList = new System.Collections.Generic.List<byte>(addrBytes);
        byteList.Reverse();
        addrBytes = byteList.ToArray();
    }
    ulong[] addrWords = new ulong[2];
    if (addrBytes.Length > 8)
    {
        addrWords[0] = System.BitConverter.ToUInt64(addrBytes, 8);
        addrWords[1] = System.BitConverter.ToUInt64(addrBytes, 0);
    }
    else
    {
        addrWords[0] = 0;
        addrWords[1] = System.BitConverter.ToUInt32(addrBytes, 0);
    }
    return addrWords;
}

Make sure you cast your UInt64s to Int64s before you put them into the database, or you'll get an ArgumentException. When you get your values back out, you can cast them back to UInt64to get the unsigned value.

在将它们放入数据库之前，请确保将UInt64s 强制转换为Int64s，否则您将得到一个ArgumentException. 当您取回您的值时，您可以将它们转换回UInt64以获取无符号值。

I don't have a need to do the reverse (i.e. convert a UInt64[2]to an IP string) so I never built a method for it.

我不需要做相反的事情（UInt64[2]即将a 转换为 IP 字符串），所以我从未为它构建过方法。

回答by anvilis

If you are using SQL Server 2005, you can use the uniqueidentifiertype. This type stores 16 bytes, which is perfect for an IPv6 ip address. You can convert between IPAddressand Guidby using the constructors and ToByteArray.

如果您使用的是 SQL Server 2005，则可以使用该uniqueidentifier类型。这种类型存储 16 个字节，非常适合 IPv6 ip 地址。您可以使用构造函数和在IPAddress和之间进行转换。GuidToByteArray

回答by Pandora

function encode_ip ($ip)
{
    return bin2hex(inet_pton($ip));
}

function decode_ip($ip)
{
    function hex2bin($temp) {
       $data="";
       for ($i=0; $i < strlen($temp); $i+=2) $data.=chr(hexdec(substr($temp,$i,2)));
       return $data;
    }
    return inet_ntop(hex2bin($ip));
}

---

---

-- max len row db
echo strlen(inet_pton('2001:db8:85a3::8a2e:370:7334'));

-- db row info
ip varchar(16)

-- sql binary save and read
save base
$bin_ip='0x'.bin2hex(inet_pton($data['ip_address']));

-- db read
select ip_address from users;

-- encode binary from db
echo inet_ntop($row['ip_address']);