How to convert IPv6 from binary for storage in MySQL

Here are the functions I now use to convert IP addresses from and to DECIMAL(39,0) format. They are named inet_ptod and inet_dtop for "presentation-to-decimal" and "decimal-to-presentation". It needs IPv6 and bcmath support in PHP.

/**
 * Convert an IP address from presentation to decimal(39,0) format suitable for storage in MySQL
 *
 * @param string $ip_address An IP address in IPv4, IPv6 or decimal notation
 * @return string The IP address in decimal notation
 */
function inet_ptod($ip_address)
{
    // IPv4 address
    if (strpos($ip_address, ':') === false && strpos($ip_address, '.') !== false) {
        $ip_address = '::' . $ip_address;
    }

    // IPv6 address
    if (strpos($ip_address, ':') !== false) {
        $network = inet_pton($ip_address);
        $parts = unpack('N*', $network);

        foreach ($parts as &$part) {
            if ($part < 0) {
                $part = bcadd((string) $part, '4294967296');
            }

            if (!is_string($part)) {
                $part = (string) $part;
            }
        }

        $decimal = $parts[4];
        $decimal = bcadd($decimal, bcmul($parts[3], '4294967296'));
        $decimal = bcadd($decimal, bcmul($parts[2], '18446744073709551616'));
        $decimal = bcadd($decimal, bcmul($parts[1], '79228162514264337593543950336'));

        return $decimal;
    }

    // Decimal address
    return $ip_address;
}

/**
 * Convert an IP address from decimal format to presentation format
 *
 * @param string $decimal An IP address in IPv4, IPv6 or decimal notation
 * @return string The IP address in presentation format
 */
function inet_dtop($decimal)
{
    // IPv4 or IPv6 format
    if (strpos($decimal, ':') !== false || strpos($decimal, '.') !== false) {
        return $decimal;
    }

    // Decimal format
    $parts = array();
    $parts[1] = bcdiv($decimal, '79228162514264337593543950336', 0);
    $decimal = bcsub($decimal, bcmul($parts[1], '79228162514264337593543950336'));
    $parts[2] = bcdiv($decimal, '18446744073709551616', 0);
    $decimal = bcsub($decimal, bcmul($parts[2], '18446744073709551616'));
    $parts[3] = bcdiv($decimal, '4294967296', 0);
    $decimal = bcsub($decimal, bcmul($parts[3], '4294967296'));
    $parts[4] = $decimal;

    foreach ($parts as &$part) {
        if (bccomp($part, '2147483647') == 1) {
            $part = bcsub($part, '4294967296');
        }

        $part = (int) $part;
    }

    $network = pack('N4', $parts[1], $parts[2], $parts[3], $parts[4]);
    $ip_address = inet_ntop($network);

    // Turn IPv6 to IPv4 if it's IPv4
    if (preg_match('/^::\d+.\d+.\d+.\d+$/', $ip_address)) {
        return substr($ip_address, 2);
    }

    return $ip_address;
}

We went for a VARBINARY(16) column instead and use inet_pton() and inet_ntop() to do the conversions:

https://github.com/skion/mysql-udf-ipv6

The functions can be loaded into a running MySQL server and will give you INET6_NTOP and INET6_PTON in SQL, just as the familiar INET_NTOA and INET_ATON functions for IPv4.

Edit: There are compatible functions in MySQL now, just with different names. Only use the above if you are on pre-5.6 MySQL and are looking for a convenient future upgrade path.