Storing IPv6 Addresses in MySQL: DECIMAL(39,0) vs. VARBINARY(16)

When faced with the challenge of storing IPv6 addresses in MySQL, developers often consider two options: DECIMAL(39,0) and 2*BIGINT. While both have their merits, a newer solution has emerged that offers advantages over both previous methods.

DECIMAL(39,0) vs. 2*BIGINT

Advantages of DECIMAL(39,0):

• Handles both IPv4 and IPv6 addresses.
• Can be compared and sorted using SQL functions.
• Easy to convert to and from binary representation using PHP functions.

Disadvantages of DECIMAL(39,0):

• Less space efficient than VARBINARY(16).
• Slower indexing performance than VARBINARY(16).
• May overflow when storing certain IPv6 addresses.

VARBINARY(16)

In recent versions of MySQL, VARBINARY(16) has become the preferred method for storing IPv6 addresses. It offers several advantages over both DECIMAL(39,0) and 2*BIGINT:

• Compact representation: VARBINARY(16) takes up exactly 16 bytes, the same size as an IPv6 address in binary form. This makes it more space efficient than DECIMAL(39,0) or 2*BIGINT.
• Fast indexing: MySQL's indexing performance is optimized for VARBINARY data types, making it faster to search for IPv6 addresses in a VARBINARY column than in a DECIMAL(39,0) or 2*BIGINT column.
• No overflow issues: VARBINARY(16) can store any IPv6 address without fear of overflow.

Conversion Functions

To convert between binary and decimal representations of IPv6 addresses, you can use the following PHP functions:

• inet_pton() converts from binary to dotted-quad notation (IPv4) or colon-hexadecimal notation (IPv6).
• inet_ntop() converts from dotted-quad or colon-hexadecimal notation to binary.

Conclusion

For storing IPv6 addresses in MySQL, VARBINARY(16) has become the preferred solution due to its space efficiency, fast indexing, and lack of overflow issues. While DECIMAL(39,0) and 2*BIGINT were once popular methods, VARBINARY(16) now offers the best balance of performance and functionality.