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，但这是另一回事)。我一直用INT表示IPv4 + TINYINT表示masklen，但IPv6是128位。
What approach will be best for that? 2xBIGINT? CHAR(16) for binary storage? CHAR(39) for text storage? 8xSMALLINT in a dedicated table?
哪种方法是最好的?2 xbigint ?CHAR(16)二进制存储?CHAR(39)文本存储?在专用表中的8xSMALLINT ?
What would you recommend?
你有什么建议?
6 个解决方案
#1
19
I'm not sure which is the right answer 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地址边界上有一种自然的分割(因为a /64是最小的netblock大小)，它会与之很好地对齐。
#2
6
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 getaddrinfo returns a longer result than expected.
注意，IPv6地址(包括范围标识符)的最大长度是标准C头中的INET6_ADDRSTRLEN定义的46字节。对于Internet使用，您应该能够忽略区域标识符(%10、#eth0等)，但是只要注意getaddrinfo返回的结果比预期的要长。
#3
6
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)，那么一定要使用二进制(16)。二进制(n)没有排序或字符集的概念(更确切地说，它是一个带“二进制”字符集/排序的字符(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)零填充无符号，不像两个bigints那样高效(十进制将在当前版本的mysql中使用4字节/ 9位数)，但允许您将其全部保存在一个列中，并打印得很好。
#4
4
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.
有一个零终止符。
This is the format used by the *nix command line tools, and, the format an IPV6 address is normaly(?) reported in.
这是*nix命令行工具使用的格式，并且，IPV6地址的格式被报告为normaly(?)。
#5
1
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会很不舒服——两个字段实际上是一个(加上存储的值是big-endian还是little-endian?)我使用了一个固定大小的二进制类型，或者如果没有的话，一个blob类型。
#6
1
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.
我正在处理一个最长前缀匹配的项目，因此我将IPv4地址分成4个整数。它的工作原理。我将扩展到IPv6地址。
#1
19
I'm not sure which is the right answer 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地址边界上有一种自然的分割(因为a /64是最小的netblock大小)，它会与之很好地对齐。
#2
6
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 getaddrinfo returns a longer result than expected.
注意，IPv6地址(包括范围标识符)的最大长度是标准C头中的INET6_ADDRSTRLEN定义的46字节。对于Internet使用，您应该能够忽略区域标识符(%10、#eth0等)，但是只要注意getaddrinfo返回的结果比预期的要长。
#3
6
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)，那么一定要使用二进制(16)。二进制(n)没有排序或字符集的概念(更确切地说，它是一个带“二进制”字符集/排序的字符(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)零填充无符号，不像两个bigints那样高效(十进制将在当前版本的mysql中使用4字节/ 9位数)，但允许您将其全部保存在一个列中，并打印得很好。
#4
4
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.
有一个零终止符。
This is the format used by the *nix command line tools, and, the format an IPV6 address is normaly(?) reported in.
这是*nix命令行工具使用的格式，并且，IPV6地址的格式被报告为normaly(?)。
#5
1
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会很不舒服——两个字段实际上是一个(加上存储的值是big-endian还是little-endian?)我使用了一个固定大小的二进制类型，或者如果没有的话，一个blob类型。
#6
1
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.
我正在处理一个最长前缀匹配的项目，因此我将IPv4地址分成4个整数。它的工作原理。我将扩展到IPv6地址。