Size for storing IPv4, IPv6 addresses as a string

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

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.

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.

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.

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/

the ipv6 address maybe 46 characters.

reference: IPv4-mapped IPv6 addresses Hybrid dual-stack IPv6/IPv4 implementations recognize a special class of addresses, the IPv4-mapped IPv6 addresses. In these addresses, the first 80 bits are zero, the next 16 bits are one, and the remaining 32 bits are the IPv4 address. One may see these addresses with the first 96 bits written in the standard IPv6 format, and the remaining 32 bits written in the customary dot-decimal notation of IPv4. For example, ::ffff:192.0.2.128 represents the IPv4 address 192.0.2.128. A deprecated format for IPv4-compatible IPv6 addresses was ::192.0.2.128.[61]

Assuming you don't have any network information (such as LL identifier, class, or CIDR mask) attached, an IPv4 address is up to fifteen characters (4x3 numbers+3 periods) and an IPv6 address may be up to 39 characters.

Besides what was already said there is Link-Local IPv6 address. If you want to keep the address so that you can use the string to create connections you will need also to keep scope id. On Windows it is a 16-bits number, on Linux it can be a string - interface name, I did not find the maximum length of an interface name.