SQL 客户端IP地址的最大长度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1076714/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Max length for client ip address
提问by Tony Eichelberger
Possible Duplicate:
Maximum length of the textual representation of an IPv6 address?
可能的重复：
IPv6 地址的文本表示的最大长度？
What would you recommend as the maximum size for a database column storing client ip addresses? I have it set to 16 right now, but could I get an ip address that is longer than that with IPv6, etc?
您建议存储客户端 IP 地址的数据库列的最大大小是多少？我现在将它设置为 16，但是我可以获得比 IPv6 等更长的 IP 地址吗？
采纳答案by Matt Bridges
For IPv4, you couldget away with storing the 4 raw bytes of the IP address (each of the numbers between the periods in an IP address are 0-255, i.e., one byte). But then you would have to translate going in and out of the DB and that's messy.
对于 IPv4，您可以避免存储 IP 地址的 4 个原始字节（IP 地址中句点之间的每个数字都是 0-255，即一个字节）。但是，您将不得不翻译进出数据库，这很麻烦。
IPv6 addresses are 128 bits (as opposed to 32 bits of IPv4 addresses). They are usually written as 8 groups of 4 hex digits separated by colons: 2001:0db8:85a3:0000:0000:8a2e:0370:7334. 39 characters is appropriate to store IPv6 addresses in this format.
IPv6 地址是 128 位（与 IPv4 地址的 32 位相反）。它们通常写成 8 组 4 个十六进制数字，以冒号分隔：2001:0db8:85a3:0000:0000:8a2e:0370:7334. 39 个字符适合以此格式存储 IPv6 地址。
Edit: However, there is a caveat, see @Deepak's answer for details about IPv4-mapped IPv6 addresses. (The correct maximum IPv6 string length is 45 characters.)
编辑：但是，有一个警告，有关 IPv4 映射的 IPv6 地址的详细信息，请参阅 @Deepak 的回答。（正确的最大 IPv6 字符串长度为45 个字符。）
回答by Deepak
There's a caveat with the general 39 character IPv6 structure.For IPv4 mapped IPv6 addresses, the string can be longer (than 39 characters). An example to show this:
通用 39 个字符的 IPv6 结构有一个警告。对于 IPv4 映射的 IPv6 地址，该字符串可以更长（超过 39 个字符）。一个例子来说明这一点：
IPv6 (39 characters) :
IPv6（39 个字符）：
ABCD:ABCD:ABCD:ABCD:ABCD:ABCD:ABCD:ABCD
IPv4-mapped IPv6 (45 characters) :
IPv4 映射 IPv6（45 个字符）：
ABCD:ABCD:ABCD:ABCD:ABCD:ABCD:192.168.158.190
Note: the last 32-bits (that correspond to IPv4 address) can need up to 15 characters (as IPv4 uses 4 groups of 1 byte and is formatted as 4 decimal numbers in the range 0-255 separated by dots (the .character), so the maximum is DDD.DDD.DDD.DDD).
注意：最后 32 位（对应于 IPv4 地址）最多需要 15 个字符（因为 IPv4 使用 4 组 1 字节，并被格式化为 0-255 范围内的 4 个十进制数字，由点（.字符）分隔，所以最大值是DDD.DDD.DDD.DDD）。
The correct maximum IPv6 string length, therefore, is 45.
因此，正确的最大 IPv6 字符串长度为 45。
This was actually a quiz question in an IPv6 training I attended. (We all answered 39!)
这实际上是我参加的 IPv6 培训中的一个测验问题。（我们都回答了39！）
回答by David J. Liszewski
If you want to handle IPV6 in standard notation there are 8 groups of 4 hex digits:
如果您想以标准表示法处理 IPV6，则有 8 组 4 个十六进制数字：
2001:0dc5:72a3:0000:0000:802e:3370:73E4
32 hex digits + 7 separators = 39 characters.
32 个十六进制数字 + 7 个分隔符 = 39 个字符。
CAUTION:If you also want to hold IPV4 addresses mapped as IPV6 addresses, use 45characters as @Deepak suggests.
注意：如果您还想将 IPV4 地址映射为 IPV6 地址，请使用@Deepak 建议的45 个字符。
回答by Neil N
Take it from someone who has tried it all three ways... just use a varchar(39)
从已经尝试过所有三种方式的人那里获取它......只需使用 varchar(39)
The slightly less efficient storage far outweighs any benefit of having to convert it on insert/update and format it when showing it anywhere.
效率稍低的存储远远超过了在插入/更新时转换它并在任何地方显示时格式化它的任何好处。
回答by Michael Petrotta
As described in the IPv6 Wikipediaarticle,
如IPv6 维基百科文章中所述，
IPv6 addresses are normally written as eight groups of four hexadecimal digits, where each group is separated by a colon (:)
IPv6 地址通常写为八组四位十六进制数字，其中每组由冒号 (:) 分隔
A typical IPv6 address:
典型的 IPv6 地址：
2001:0db8:85a3:0000:0000:8a2e:0370:7334
This is 39 characters long. IPv6 addresses are 128 bits long, so you could conceivably use a binary(16) column, but I think I'd stick with an alphanumeric representation.
这是 39 个字符长。IPv6 地址的长度为 128 位，因此您可以想象使用 binary(16) 列，但我想我会坚持使用字母数字表示。
回答by Chet
IPv4 uses 32 bits, in the form of:
IPv4 使用 32 位，形式为：
255.255.255.255
I suppose it depends on your datatype, whether you're just storing as a string with a CHAR type or if you're using a numerical type.
我想这取决于您的数据类型，无论您是将其存储为 CHAR 类型的字符串，还是使用数字类型。
IPv6 uses 128 bits. You won't have IPs longer than that unless you're including other information with them.
IPv6 使用 128 位。除非您在其中包含其他信息，否则您的 IP 不会超过此长度。
IPv6 is grouped into sets of 4 hex digits seperated by colons, like (from wikipedia):
IPv6 被分组为由冒号分隔的 4 个十六进制数字的集合，例如（来自维基百科）：
2001:0db8:85a3:0000:0000:8a2e:0370:7334
You're safe storing it as a 39-character long string, should you wish to do that. There are other shorthand ways to write addresses as well though. Sets of zeros can be truncated to a single 0, or sets of zeroes can be hidden completely by a double colon.
如果您希望这样做，您可以安全地将其存储为 39 个字符的长字符串。不过，还有其他写地址的速记方法。可以将零组截断为单个 0，或者可以通过双冒号完全隐藏零组。
回答by user410119
If you are just storing it for reference, you can store it as a string, but if you want to do a lookup, for example, to see if the IP address is in some table, you need a "canonical representation." Converting the entire thing to a (large) number is the right thing to do. IPv4 addresses can be stored as a long int (32 bits) but you need a 128 bit number to store an IPv6 address.
如果您只是将其存储以供参考，则可以将其存储为字符串，但如果您想进行查找，例如，查看 IP 地址是否在某个表中，则需要一个“规范表示”。将整个事物转换为（大）数字是正确的做法。IPv4 地址可以存储为 long int（32 位），但您需要一个 128 位的数字来存储 IPv6 地址。
For example, all these strings are really the same IP address: 127.0.0.1, 127.000.000.001, ::1, 0:0:0:0:0:0:0:1
例如，所有这些字符串实际上都是相同的 IP 地址：127.0.0.1, 127.000.000.001, ::1, 0:0:0:0:0:0:0:1
回答by Christian
People are talking about characters when one can compress an IP address into raw data.
当人们可以将 IP 地址压缩为原始数据时，人们谈论的是字符。
So in principle, since we only use IPv4 (32bit) or IPv6 (128bit), that means you need at most 128 bits of space, or 128/8 = 16 bytes!
所以原则上，由于我们只使用 IPv4（32 位）或 IPv6（128 位），这意味着您最多需要 128 位空间，或 128/8 = 16 字节！
Which is much less than the suggested 39 bytes (assuming charset is ascii).
这远小于建议的 39 个字节（假设字符集是 ascii）。
That said, you will have to decode and encode the IP address into/from the raw data, which in itself is a trivial thing to do (I've done it before, see PHP's ip2long()for 32-bit IPs).
也就是说，您必须对原始数据中的 IP 地址进行解码和编码，这本身就是一件微不足道的事情（我以前做过，请参阅 PHP 的ip2long()32 位 IP）。
Edit:inet_pton(and its opposite, inet_ntop()) does what you need, and works with both address types. But beware, on Windows it's available since PHP 5.3.
编辑：（inet_pton及其相反的inet_ntop()）满足您的需求，并适用于两种地址类型。但请注意，在 Windows 上，它从 PHP 5.3 开始可用。
