Ipv4 and/or IPV6 record in the database

Question

The Internet works through protocols like Ipv4 and Ipv6, possessing 32 bits and 128bits, which are numerical combinations that establish connections between computers. I have a device table, tbl_device, in which I need to send status of the device time or other. The status includes the location of the IP device, however I was in doubt in what format to use, if TEXT, VARCHAR, CHAR etc. in the database.

What would be the ideal type to save Ipv4 and/or Ipv6 in Mysql?

Answer 1

It seems to me to be a clear case for VARCHAR since it needs a variable size but it will never be too big. CHAR would only be useful if it did not have this variation. I could even use the largest size, but I see no advantage in this waste. The TEXT stores outside the normal table and creates an indirect that is not only advantageous when the data is accessed sporadically and is very large.

Eventually it might be useful (it doesn’t seem to be) to have a column for each, there the CHAR may be more useful since it will always be the same size. I see no advantages, but it always depends on the case.

You can read more about at What types of data exist in Mysql for text?.

If space is important do not discard encode IP binarially and then play on VARCHR, so the maximum occupied will be 16 bytes (plus the overhead of VARCHAR) for the 128 bits and not the space occupied for the hexadecimal representation and worse if contain the separators.