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 a MySQL database. should varchar (32) be enough?

Answer 1

Assuming a textual representation in a string:

• 15 characters for IPv4 format ( xxx.xxx.xxx.xxx , 12 + 3 separators)
• 39 characters (32 + 7 delimiters) for IPv6

This is the maximum string length.

Alternatives to storing as a string:

• IPv4 is 32-bit, so the MySQL data type, which can contain 4 bytes, will use, using INT UNSIGNED , together with INT_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 of 16 bytes, however, you can set IPv6 in canonical form and then split them into 2 BIGINT (8 bytes), however this will use two fields.

Answer 2

Numerically, an IPv4 address is 32 bits long and an IPv6 address is 128 bits long. So you need to store at least 16 bytes.

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

Answer 3

If you store them as strings, not bit patterns:

IPv4 addresses consist of four three-digit decimal characters with three separators . , therefore only 15 characters, such as 255.255.255.255 .

IPv6 addresses are eight 4-digit hexadecimal characters with seven delimiters : so it accepts 39 characters, such as 0123:4567:89ab:cdef:0123:4567:89ab:cdef .

Answer 4

Assuming you do not have any network information (for example, LL identifier, class, or CIDR mask), an IPv4 address can contain up to fifteen characters (4x3 numbers + 3 periods), and an IPv6 address can be up to 39 characters.

Answer 5

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

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

Answer 6

ipv6 address can be 46 characters.

reference: IPv4 address IPv6 addresses Hybrid two-level IPv6 / IPv4 implementations recognize a special class of addresses, IPv4-mapped IPv6 addresses. At these addresses, the first 80 bits are zero, the next 16 bits are one, and the remaining 32 bits is an IPv4 address. These addresses can be seen using the first 96 bits recorded in the standard IPv6 format, and the remaining 32 bits recorded in the usual decimal IPv4 value. For example, :: ffff: 192.0.2.128 represents the IPv4 address of 192.0.2.128. The legacy format for IPv4-compatible IPv6 addresses is 192.0.2.128. [61]

Answer 7

In addition to what has already been said, there is a Link-Local IPv6 address. If you want to save the address so that you can use the string to create connections, you also need to save the area identifier. On Windows, this is a 16-bit number, on Linux it may be the name of the interface, I did not find the maximum length of the interface name.