Storing IPv4 & IPv6 in an RDBMS (MySQL)

1
votes

I've seen a number of questions floating around here (and have read them) regarding storing both IPv4 and IPv6 addresses in an RDBMS (typically MySQL, not unlike my case)

Anyways, if (and I've read differently too, so advise if this is incorrect or officially deprecated) IPv4 address are zero-padded and stored in the ::/96 block in translation, would it make sense to use two columns, perhaps:

`ip96` BINARY(12) NULL ,     /* first 3 bytes of ipv6 */
`ip32` BINARY(4) NOT NULL ,  /* whole ipv4 or last byte of ipv6 */

This makes sense in my head as far as data normalization goes, and testing whether an address is in the IPv4 or IPv6 range is as easy as IS NULL.

However, I've seen VARBINARY(16) thrown around in a number of solutions.

Are there any foreseeable performance gains/losses from implementing this solution over VARBINARY(16) or 2 unsigned BIGINT columns? What about indexing, or any further considerations?

1
mysqlipv6binary-dataipv4

1 Answers

1
votes

IMHO splitting data to two columns is worst design, I would either

  • use two separate fields, each long enough to hold whole value, fill the appropriate field and set the other one to NULL;
  • use one field for the data (long enough to hold longest of possible values) and other field to store the type of the data (ipv4 or ipv6);

Which one is "better" depends on how you use the data. Splitting the value between two columns only makes sense when youre really tight on space, which usually isn't the case nowadays.