如何将 IPv6 从二进制转换为 MySQL 中的存储 [英] How to convert IPv6 from binary for storage in MySQL

问题描述

我正在尝试以一种有效的方式在 MySQL 5.0 中存储 IPv6 地址.我已经阅读了与此相关的其他问题，例如这个.该问题的作者最终选择了两个 BIGINT 字段.我的搜索还发现了另一种常用的机制:使用 DECIMAL(39,0) 来存储 IPv6 地址.我有两个问题.

I am trying to store IPv6 addresses in MySQL 5.0 in an efficient way. I have read the other questions related to this, such as this one. The author of that question eventually chose for two BIGINT fields. My searches have also turned up another often used mechanism: Using a DECIMAL(39,0) to store the IPv6 address. I have two questions about that.

1. 与其他方法(例如 2*BIGINT)相比，使用 DECIMAL(39,0) 的优缺点是什么?
2. 如何将(在 PHP 中)从 inet_pton() 返回的二进制格式转换为MySQL 可用的十进制字符串格式，以及如何转换回来以便我可以使用 inet_ntop() 进行漂亮打印?

1. What are the advantages and disadvantages of using DECIMAL(39,0) over the other methods such as 2*BIGINT?
2. How do I convert (in PHP) from the binary format as returned by inet_pton() to a decimal string format usable by MySQL, and how do I convert back so I can pretty-print with inet_ntop()?

推荐答案

以下是我现在用来将 IP 地址从 DECIMAL(39,0) 格式转换为 DECIMAL(39,0) 格式的函数.它们被命名为 inet_ptod 和 inet_dtop 用于表示到十进制"和十进制到表示".它需要 PHP 中的 IPv6 和 bcmath 支持.

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;
}

这篇关于如何将 IPv6 从二进制转换为 MySQL 中的存储的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！