在Microsoft SQL Server中存储IP地址 [英] Storing IP addresses in Microsoft SQL Server
问题描述
我知道有关此主题的问题已经很多，但我可能还有一些其他问题.
I know there's already a lot of questions on this topic but I might have a few extra questions.
我想将IP地址存储在数据库中(如果可能，请单列显示).我希望能够存储IPv4或IPv6地址.我希望能够在检索时区分IPv4和IPv6地址.
I want to store IP addresses in a database (single column if possible). I want to be able to store either IPv4 or IPv6 addresses. I want to be able to differentiate between IPv4 and IPv6 addresses upon retrieval.
我考虑过的方法:
-
使用VARCHAR(45)以文本格式存储IP地址. IPv4地址的文本表示形式的最大长度为 15 个字符. IPv6地址的文本表示形式的最大长度为 39 个字符，尽管我读到它们在代理IPv4地址时实际上最多可以为 45 (不是)我熟悉的东西，所以我可能不在这里)-一些软件中的头文件似乎反映了这一点，将 46 指定为IPv6地址的最大长度(45 + \ 0).格式之间的区别很小.
Use VARCHAR(45) to store the IP addresses in textual format. The maximum length of the textual representation of an IPv4 address is 15 characters. The maximum length of the textual representation of an IPv6 address is 39 characters, although I read that they can actually be up to 45 when they're proxying an IPv4 address (not something I'm familiar with so I might be off here) - header files in some software seem to reflect this, specifying 46 as the maximum length of an IPv6 address (45 + \0). Differentiation between formats is trivial.
将IP地址存储在两个bigint列中.与文本格式相比，它使用的空间更少.格式之间的区别-如果第一列为0或NULL(尽管对性能不利)，则为IPv4地址，否则为IPv6?
Store the IP addresses in two bigint columns. This uses less space than text format. Differentiation between formats - if the first column is 0 or NULL (though bad for performance), it's an IPv4 address, otherwise IPv6?
将IP地址存储在uniqueidentifier列中. 128位可以以最紧凑的方式存储两种地址格式(我们在IPv4地址上浪费了96位，但是无论我们做什么都是不可避免的).这种方法似乎是最可取的方法，但是在给定任何128位整数的情况下，是否有可能知道该128位整数代表的是IPv4还是IPv6地址?
Store the IP addresses in a uniqueidentifier column. 128 bits, can store both address formats in the most compact way (we waste 96 bits on IPv4 addresses but this is unavoidable no matter what we do). This way would seem most preferable, but given any 128 bit integer, is it possible to know whether that 128 bit integer represents an IPv4 or IPv6 address?
如果可以将以单个128位整数表示的IPv4和IPv6地址区分开来，我宁愿使用第三种方法来限制该解决方案的任何重大问题.我想我的主要问题是这是否可能，但是听取有关不同方法(包括我未在此处列出的方法)的优缺点的任何想法也会有所帮助.
If differentiation between IPv4 and IPv6 addresses represented as a single 128 bit integer is possible, I'd prefer to use the third approach barring any major issues with that solution. I guess my main question is whether that's possible, but hearing any thoughts on the advantages and disadvantages of different methods (including ones I have not listed here) would be helpful also.
还...如果有必要，将位列与第三种方法(区分IPv4和IPv6)结合起来是一个好主意吗?位列实际上是1位，还是8位?
Also... if it were necessary, would adding a bit column in combination with the third approach (to differentiate between IPv4 and IPv6) be a good idea? Is a bit column actually physically 1 bit, or is it 8?
推荐答案
我可以将其存储在二进制文件中. IPv6地址的长度为128位-> 16个字节.如果存储IPv4地址，则应将其存储在最后4个字节中.在应用程序中使用此字段时，有许多内置选项可以将IPv6转换为文本，并将文本转换回IPv6二进制.
I may store it in binary. An IPv6 address is 128bit -> 16 bytes long. If you store IPv4 address, you should store it at the last 4 bytes. When you use this field in application there are many built in options to convert from IPv6 to text and text back to IPv6 binary.
在IPv4情况下:此地址类型的前80位(10字节)设置为零，后16位(2字节)设置为1(0xFF)，而后32位(4字节)填充为IPv4地址.堆栈的实现通常会有所不同，因此您应该在客户端(从IPv6到IPv4)进行转换.该地址空间是安全的，因此保留给IPv4寻址.
In IPv4 case: this address type has its first 80 bits (10 bytes) set to zero and the next 16 bits (2 bytes) set to one (0xFF), while its last 32 bits (4 bytes) are filled with the IPv4 address. The stack implementation usually differs, so you should make the conversions at client side (from IPv6 to IPv4). This address space is safe, so it is reserved for IPv4 addressing.
