34

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.

What 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?

the Tin Man
  • 158,662
  • 42
  • 215
  • 303
azerole
  • 1,262
  • 3
  • 16
  • 23

6 Answers6

20

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).

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.

Mike
  • 23,542
  • 14
  • 76
  • 87
Alnitak
  • 334,560
  • 70
  • 407
  • 495
  • 5
    Final decision taken: 2xBIGINT if the second bigint is NULL, then it means IPv4 – azerole Jan 13 '09 at 10:29
  • 9
    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 Jan 13 '09 at 10:38
  • 8
    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 Oct 21 '09 at 05:14
  • 5
    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. – Billy Jo Oct 29 '09 at 20:11
  • 1
    @james: now IPv4 mapped addresses [require](http://en.wikipedia.org/wiki/IPv6#IPv4-mapped_IPv6_addresses) ::ffff:/96 range – NeDark Aug 24 '11 at 00:21
7

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
  • 12,678
  • 2
  • 41
  • 60
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.

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.

dlamblin
  • 43,965
  • 20
  • 101
  • 140
ʞɔıu
  • 47,148
  • 35
  • 106
  • 149
  • In a project that I'am working now we are using the decimal(39,0) approach and it works well, with few exceptions. Some times you have to CAST the values for comparisons. i.e. WHERE ipv6 = CAST('string containing numeric ipv6' AS decimal(39,0)). Specially on languages that not support 128 bit numeric types, and the parameter binding of the mysql library is not clever enough. – Diego Jan 30 '14 at 21:03
4

I would go for the full 39 character "standard" printed format:--

"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.

dlamblin
  • 43,965
  • 20
  • 101
  • 140
James Anderson
  • 27,109
  • 7
  • 50
  • 78
  • 26
    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 Jan 07 '09 at 15:55
  • @James Anderson: And what's worse is that you couldn't compare if a given IPv6 lies in a specified range, (geolocating, IP range-banning). – Stefan Steiger Feb 08 '12 at 11:26
  • @Quandary That was true with IPv4 dotted format. However, IPv6 printed format in the expanded form (39 chars) can be used for comparisons. – oxygen Jun 08 '12 at 23:03
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.

dlamblin
  • 43,965
  • 20
  • 101
  • 140
hgl
  • 11
  • 1
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.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
  • 1
    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 Jan 07 '09 at 15:32
  • 3
    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 Jan 08 '09 at 06:24