Size for storing IPv4, IPv6 addresses as a string

Numerically, an IPv4 address is 32-bit long and IPv6 address is 128-bit long. So you need a storage of at least 16 bytes.

If the "string" you store is an encoding of the address in byte form, then 16 is enough.

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.

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.

You can use a VARBINARY(16) to store an IPv6 address in a binary format.

The applications that need to use this data can then use their inet_pton/ntop implementations to manipulate this data, or you can install a UDF like the one at http://labs.watchmouse.com/2009/10/extending-mysql-5-with-ipv6-functions/