How to store IPv6-compatible address in a relational database

Question

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?

Answer 1

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.

Answer 2

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.