How to Efficiently Store IPv6 Addresses in MySQL: DECIMAL(39,0) vs. VARBINARY(16)?

Efficient Storage of IPv6 Addresses in MySQL

Storing IPv6 addresses efficiently in MySQL can be a challenge. Two commonly used methods include using two BIGINT fields or a DECIMAL(39,0) field.

Advantages and Disadvantages of DECIMAL(39,0)

Using DECIMAL(39,0) over 2*BIGINT offers several advantages:

• Compact Size: A DECIMAL(39,0) field stores the IPv6 address as a single value, which is more compact than using two BIGINT fields.
• Faster Queries: Queries on a DECIMAL(39,0) field are generally faster than on two BIGINT fields, especially for range queries.

However, DECIMAL(39,0) also has some disadvantages:

• Precision Limitations: DECIMAL(39,0) cannot handle IPv6 addresses larger than 39 digits.
• Lack of Standard: The use of DECIMAL(39,0) for IPv6 storage is not a standardized approach and may not be supported by all MySQL versions.

Conversion from Binary to Decimal and Vice Versa

To convert from the binary format as returned by inet_pton() to a decimal string format usable by MySQL, you can use the following PHP function:

<code class="php">function binaryToDecimal($binary) {
  $hex = bin2hex($binary);
  return gmp_strval(gmp_init($hex, 16), 10);
}</code>

To convert back from a decimal string to binary, use the following function:

<code class="php">function decimalToBinary($decimal) {
  $hex = gmp_strval(gmp_init($decimal), 16);
  return hex2bin($hex);
}</code>

Optimizing Storage for IPv6 Addresses

Instead of using DECIMAL(39,0), a more efficient option is to use a VARBINARY(16) column and leverage the inet_pton() and inet_ntop() functions for conversion. This approach is supported in MySQL 5.6 and later and provides both compactness and performance benefits.

The above is the detailed content of How to Efficiently Store IPv6 Addresses in MySQL: DECIMAL(39,0) vs. VARBINARY(16)?. For more information, please follow other related articles on the PHP Chinese website!