Register of IPv4 and / or IPV6 in the database [closed]

Question

Register of IPv4 and / or IPV6 in the database [closed]

Navigation

#1 by (3 votes)

3

The Internet works through protocols such as IPv4 and IPv6, having 32 bits and 128 bits, 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 at one time or another. The status includes the location of the IP of the 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?

mysql modelagem

asked by anonymous 17.04.2017 / 18:07

1 answer

How to create an index for two tables at the same time in SQL Server? How to get the value of a cell with dynamic reference

score 3 · Accepted Answer

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

Eventually it might be useful (it does not seem to be) to have a column for each, so the CHAR can be more useful since it will always be the same size. I do not see any advantages, but it always depends on the case.

You can read more about What MySQL data types exist for text? .

If the space is important, do not discard IP code binary and then play on VARCHR , so the maximum busy is 16 bytes (plus the overhead of the VARCHAR ) for the 128 bits and not the space occupied for the hexadecimal representation and worse still if it contains the separators.