MySQL 将 IPv4、IPv6 地址存储为字符串的大小
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3455320/
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
Size for storing IPv4, IPv6 addresses as a string
提问by user339108
what should be the ideal size for storing IPv4, IPv6 addresses as a string in the MySQL database. should varchar(32) be sufficient?
将 IPv4、IPv6 地址作为字符串存储在 MySQL 数据库中的理想大小应该是多少。varchar(32) 应该足够了吗？
回答by bakkal
Assuming textual representation in a string :
假设字符串中的文本表示：
- 15 characters for IPv4 (
xxx.xxx.xxx.xxxformat, 12+3 separators) - 45 charactersfor IPv6
- IPv4 15 个字符（
xxx.xxx.xxx.xxx格式，12+3 个分隔符） - IPv6 45 个字符
Those are the maximum length of the string.
这些是字符串的最大长度。
Alternatives to storing as string:
存储为字符串的替代方法：
- IPv4 is 32-bits, so a MySQL data type that can hold 4 bytes will do, using
INT UNSIGNEDis common along withINET_ATONandINET_NTOAto handle the conversion from address to number, and from number to address
- IPv4 是 32 位的，因此可以容纳 4 个字节的 MySQL 数据类型就可以，使用
INT UNSIGNED很常见INET_ATON并INET_NTOA处理从地址到数字以及从数字到地址的转换
SELECT INET_ATON('209.207.224.40'); -> 3520061480 SELECT INET_NTOA(3520061480); -> '209.207.224.40'
SELECT INET_ATON('209.207.224.40'); -> 3520061480 SELECT INET_NTOA(3520061480); -> '209.207.224.40'
- For IPv6, unfortunately MySQL does not have a data type that is 16 bytes, however one can put the IPv6 into a canonical form, then separate them into 2
BIGINT(8 bytes), this however will use two fields.
- 对于 IPv6，不幸的是 MySQL 没有 16 字节的数据类型，但是可以将 IPv6 转换为规范形式，然后将它们分成 2
BIGINT（8 个字节），但是这将使用两个字段。
回答by Santa
Numerically, an IPv4 address is 32-bit long and IPv6 address is 128-bit long. So you need a storage of at least 16bytes.
从数字上讲，IPv4 地址为 32 位长，IPv6 地址为 128 位长。所以你需要至少16个字节的存储空间。
If the "string" you store is an encoding of the address in byte form, then 16 is enough.
如果您存储的“字符串”是以字节形式对地址进行编码，那么 16 就足够了。
回答by paxdiablo
If you're storing them as strings rather than bit patterns:
如果您将它们存储为字符串而不是位模式：
IPv4 addresses consist of four 3-digit decimal characters with three .separators, so that only takes 15 characters such as 255.255.255.255.
IPv4 地址由四个 3 位十进制字符和三个.分隔符组成，因此只需要 15 个字符，例如255.255.255.255.
IPv6 addresses consist of eight 4-digit hex characters with seven :separators, so that takes 39 characters such as 0123:4567:89ab:cdef:0123:4567:89ab:cdef.
IPv6 地址由八个 4 位十六进制字符和七个:分隔符组成，因此需要 39 个字符，例如0123:4567:89ab:cdef:0123:4567:89ab:cdef.
回答by benofbrown
You can use a VARBINARY(16) to store an IPv6 address in a binary format.
您可以使用 VARBINARY(16) 以二进制格式存储 IPv6 地址。
The applications that need to use this data can then use their inet_pton/ntop implementations to manipulate this data, or you can install a UDF like the one at http://labs.watchmouse.com/2009/10/extending-mysql-5-with-ipv6-functions/
需要使用这些数据的应用程序可以使用它们的 inet_pton/ntop 实现来操作这些数据，或者您可以安装一个像http://labs.watchmouse.com/2009/10/extending-mysql-5 上的 UDF -with-ipv6-functions/
回答by hnrain
the ipv6 address maybe 46 characters.
ipv6 地址可能有 46 个字符。
reference: IPv4-mapped IPv6 addresses Hybrid dual-stack IPv6/IPv4 implementations recognize a special class of addresses, the IPv4-mapped IPv6 addresses. In these addresses, the first 80 bits are zero, the next 16 bits are one, and the remaining 32 bits are the IPv4 address. One may see these addresses with the first 96 bits written in the standard IPv6 format, and the remaining 32 bits written in the customary dot-decimal notation of IPv4. For example, ::ffff:192.0.2.128 represents the IPv4 address 192.0.2.128. A deprecated format for IPv4-compatible IPv6 addresses was ::192.0.2.128.[61]
参考：IPv4 映射的 IPv6 地址 混合双栈 IPv6/IPv4 实现识别一类特殊的地址，即 IPv4 映射的 IPv6 地址。在这些地址中，前 80 位是 0，接下来的 16 位是 1，其余 32 位是 IPv4 地址。人们可能会看到这些地址的前 96 位以标准 IPv6 格式编写，其余 32 位以 IPv4 的习惯点十进制表示法编写。例如，::ffff:192.0.2.128 表示 IPv4 地址 192.0.2.128。与 IPv4 兼容的 IPv6 地址的弃用格式是 ::192.0.2.128.[61]
回答by Demetrius
Besides what was already said there is Link-Local IPv6 address. If you want to keep the address so that you can use the string to create connections you will need also to keep scope id. On Windows it is a 16-bits number, on Linux it can be a string - interface name, I did not find the maximum length of an interface name.
除了已经说过的还有链路本地 IPv6 地址。如果要保留地址以便可以使用字符串创建连接，则还需要保留范围 ID。在 Windows 上它是一个 16 位数字，在 Linux 上它可以是一个字符串 - 接口名称，我没有找到接口名称的最大长度。
回答by Borealid
Assuming you don't have any network information (such as LL identifier, class, or CIDR mask) attached, an IPv4 address is up to fifteen characters (4x3 numbers+3 periods) and an IPv6 address may be up to 39 characters.
假设您没有附加任何网络信息（例如 LL 标识符、类别或 CIDR 掩码），IPv4 地址最多可包含 15 个字符（4x3 数字 + 3 个句点），而 IPv6 地址最多可包含 39 个字符。
