How do I do that? Right now IPv6 will not be used, but I need to design the application in such a way as 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. What kind of approach will be best for that? 2xBIGINT? CHAR(16) for binary storage? CHAR(39) for text storage? 8xSMALLINT in a dedicated table? What would you recommend?
+6
A:
I'm not sure which is the right answer for MySQL given that it doesn't yet support IPv6 address formats natively (although whilst this suggests that it was going to be in MySQL v6.0 current documentation doesn't back that up).
However of those you've proposed I'd suggest going for 2 * BIGINT. 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.
Alnitak
2009-01-07 15:17:20
Final decision taken: 2xBIGINTif the second bigint is NULL, then it means IPv4
azerole
2009-01-13 10:29:42
actually - if I was doing this I'd leave the _first_ bigint as NULL for IPv4, or use a separate field. That way the IPv4 part appears in the least significant word.
Alnitak
2009-01-13 10:38:13
And if I were doing this, I'd store IPv4 addresses in the V4COMPAT format of IPv6 addresses, i.e. in the ::/96 range.
james woodyatt
2009-10-21 05:14:01
fair call James - I bow to your greater knowledge. BTW, will you be @76 next month?
Alnitak
2009-10-21 17:43:43
You can't just use two BIGINTs, you have to use two BIGINT UNSIGNEDs. If you take FFFF:FFFF:FFFF:FFFF:FFFF:FFFF:FFFF:FFFF for example, splitting it in half and converting each side to its integer representation would resolve to two values of 18,446,744,073,709,551,615. This is the max value of an unsigned 64-bit integer.
Bill Ayakatubby
2009-10-29 20:11:25
A:
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.
Jonathan Leffler
2009-01-07 15:22:49
If you store it in a BINARY then there's never going to be any chance of doing bitwise operators within the DB itself to find matching addresses (i.e. all those addresses that match a particular subnet)
Alnitak
2009-01-07 15:32:09
That's why I suggested text format - on which you can do regex matching (though I didn't mention that). Storing it in any binary format is going to be tricky, unless you upgrade it to a full user defined type (does MySQL support that?) and provide appropriate operators.
Jonathan Leffler
2009-01-08 06:24:13
+1
A:
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'). Te 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.
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.
ʞɔıu
2009-01-07 15:38:21
A:
I would go for the full 37 character "standard" printed format:--
"2001:0db8:85a3:0000:0000:8a2e:0370:7334"
This is the format used by the *nix command line tools, and, the format an IPV6 address is normaly(?) reported in.
James Anderson
2009-01-07 15:53:28
I wouldn't. IPv6 addresses are often shown in abbreviated format, and it would also be pretty inefficient to search the table for any address that's in a particular subnet, particularly if the subnet spans a nybble boundary.
Alnitak
2009-01-07 15:55:41
A:
I am working with a project of longest prefix matching, so I separate the address into 4 ints.It works well.
hgl
2009-10-18 05:27:45
A:
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.
Steve-o
2010-02-06 04:25:01