How to store IPv6-compatible address in a relational database
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
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.