Size for storing IPv4, IPv6 addresses as a string

Question

what should be the ideal size for storing IPv4, IPv6 addresses as a string in the MySQL database. should varchar(32) be sufficient?

Answer 1

Assuming textual representation in a string :

• 15 characters for IPv4 (xxx.xxx.xxx.xxx format, 12+3 separators)
• 45 characters for IPv6

Those are the maximum length of the string.

Alternatives to storing as string:

• IPv4 is 32-bits, so a MySQL data type that can hold 4 bytes will do, using INT UNSIGNED is common along with INET_ATON and INET_NTOA to handle the conversion from address to number, and from number to address

SELECT INET_ATON('209.207.224.40');         -> 3520061480  SELECT INET_NTOA(3520061480);         -> '209.207.224.40' 

• For IPv6, unfortunately MySQL does not have a data type that is 16 bytes, however one can put the IPv6 into a canonical form, then separate them into 2 BIGINT (8 bytes), this however will use two fields.

Answer 2

If you're storing them as strings rather than bit patterns:

IPv4 addresses consist of four 3-digit decimal characters with three . separators, so that only takes 15 characters such as 255.255.255.255.

IPv6 addresses consist of eight 4-digit hex characters with seven : separators, so that takes 39 characters such as 0123:4567:89ab:cdef:0123:4567:89ab:cdef.