MySQL 如何在关系数据库中存储兼容 IPv6 的地址
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/420680/
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
How to store IPv6-compatible address in a relational database
提问by azerole
How do I do that?
我怎么做？
Right now, IPv6 will not be used, but I need to design the application to make it IPv6-ready. It is necessary to store IP addresses and CIDR blocks (also BGP NLRI, but this is another story) in a MySQL database. I've alway used an INT for IPv4 + a TINYINT for masklen, but IPv6 is 128 bit.
现在，不会使用 IPv6，但我需要设计应用程序以使其支持 IPv6。有必要在 MySQL 数据库中存储 IP 地址和 CIDR 块（也是 BGP NLRI，但这是另一回事）。我一直对 IPv4 使用 INT + 对 masklen 使用 TINYINT，但 IPv6 是 128 位。
What approach will be best for that? 2xBIGINT? CHAR(16)for binary storage? CHAR(39)for text storage? 8xSMALLINTin a dedicated table?
什么方法最适合这种情况？2xBIGINT? CHAR(16)用于二进制存储？CHAR(39)用于文本存储？8xSMALLINT在专用表中？
What would you recommend?
你会推荐什么？
采纳答案by Alnitak
I'm not sure which is the rightanswer for MySQL given that it doesn't yet support IPv6 address formats natively (although whilst "WL#798: MySQL IPv6 support" suggests that it was going to be in MySQL v6.0, current documentation doesn't back that up).
我不确定哪个是MySQL的正确答案，因为它本身还不支持 IPv6 地址格式（尽管“ WL#798：MySQL IPv6 支持”表明它将在 MySQL v6.0，当前文档没有支持这一点）。
However of those you've proposed I'd suggest going for 2 * BIGINT, but make sure they're UNSIGNED. There's a sort of a natural split at the /64 address boundary in IPv6 (since a /64 is the smallest netblock size) which would align nicely with that.
但是，在您建议的那些中，我建议使用 2 * BIGINT，但请确保它们是未签名的。在 IPv6 中的 /64 地址边界处有一种自然分割（因为 /64 是最小的网络块大小），这将很好地与此对齐。
回答by ???u
If you're leaning towards char(16), definitely use binary(16) instead. binary(n) does not have a concept of collation or character set (or rather, it is a char(n) with a charset/collation of 'binary'). The default for char in mysql is latin1_swedish_ci, which means that it will attempt case-insensitive sorting and comparisons for byte values that are valid code points in latin1, which will cause you all manner of unexpected problems.
如果您倾向于使用 char(16)，请务必改用 binary(16)。binary(n) 没有排序规则或字符集的概念（或者更确切地说，它是一个带有“二进制”字符集/排序规则的 char(n)）。mysql 中 char 的默认值是 latin1_swedish_ci，这意味着它将尝试对 latin1 中有效代码点的字节值进行不区分大小写的排序和比较，这将导致您出现各种意外问题。
Another option is to use decimal (39, 0) zerofill unsigned, not quite as efficient as two bigints (decimal will use 4 bytes per nine digits in current versions of mysql), but will allow you to keep it all in one column and print out nicely.
另一种选择是使用十进制 (39, 0) 无符号零填充，不如两个 bigint 有效（在当前版本的 mysql 中，十进制将每九位使用 4 个字节），但允许您将其全部保留在一个列中并打印很好地出去。
回答by Steve-o
Note that the maximum length of a IPv6 address, including scope identifier, is 46 bytes as defined by INET6_ADDRSTRLEN in standard C headers. For Internet usage you should be able to ignore the zone identifier(%10, #eth0, etc), but just be aware when getaddrinforeturns a longer result than expected.
请注意，IPv6 地址的最大长度（包括作用域标识符）为 46 字节，如标准 C 标头中的 INET6_ADDRSTRLEN 所定义。对于 Internet 使用，您应该能够忽略区域标识符（%10、#eth0 等），但请注意何时getaddrinfo返回的结果比预期的要长。
回答by James Anderson
I would go for the full 39 character "standard" printed format:--
我会选择完整的 39 个字符“标准”打印格式：--
"2001:0db8:85a3:0000:0000:8a2e:0370:7334"
40 with a null terminator.
40 带有空终止符。
This is the format used by the *nix command line tools, and, the format an IPV6 address is normaly(?) reported in.
这是 *nix 命令行工具使用的格式，并且 IPV6 地址的格式通常是（？）报告的。
回答by hgl
I am working with a project of longest prefix matching, so I separate the address into 4 integers for IPv4 addresses. It works well. I'd extend that to IPv6 addresses.
我正在处理最长前缀匹配的项目，因此我将地址分成 4 个整数用于 IPv4 地址。它运作良好。我会将其扩展到 IPv6 地址。
回答by Jonathan Leffler
Is the IP address going to used by a program for which binary makes sense? Or would you be better off storing a text representation? Also, with IPv6, you are less likely to use the address in general and more likely to use host names. Whether that's relevant depends on the application, in part. CHAR(16) would be a bad choice; char is for character data and won't like big streams of zero bytes which are prevalent in IPv6 addresses. 2 x BIGINT would be uncomfortable - two fields that are really one (plus is the value stored big-endian or little-endian?). I'd used a fixed size BINARY type, or if that's not available, a blob type.
IP地址是否会被二进制有意义的程序使用？或者你会更好地存储文本表示？此外，对于 IPv6，您一般不太可能使用地址，而更有可能使用主机名。这是否相关部分取决于应用程序。CHAR(16) 将是一个糟糕的选择；char 用于字符数据，不会喜欢 IPv6 地址中普遍存在的大的零字节流。2 x BIGINT 会不舒服 - 两个字段实际上是一个（加上存储的值是大端还是小端？）。我使用了固定大小的 BINARY 类型，或者如果不可用，则使用 blob 类型。
