mysql ipv6转换数字,如何从二进制转换IPv6以存储在MySQL中

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.

What are the advantages and disadvantages of using DECIMAL(39,0) over the other methods such as 2*BIGINT?

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()?

解决方案

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;

}