In the article "Evaluation of IP Address->Geolocation Conversion", it is mentioned that using the ip2addr function to directly read the IP database file is the most efficient. Compared with using the MySQL database to store IP data, using SQL query is the least efficient. . But the IP database file QQWry.dat is GB2312 encoded. Now I need UTF-8 encoded geolocation results. If you use the MySQL method, you can convert the data to UTF-8 encoding when it is stored in the database, once and for all. However, the QQWry.dat file cannot be modified, and the output result of the ip2addr function can only be dynamically converted.
There are at least four ways to dynamically convert GB->UTF-8 encoding:
Convert using PHP's iconv extension
Use PHP's mb_string Extended conversion
Convert using a swap table, the swap table is stored in the MySQL database
Convert using a swap table, the swap table is stored in a text file
The first two This method can only be used if the server has been configured accordingly (corresponding extensions have been compiled and installed). My virtual host does not have these two extensions, so I have to consider the latter two methods. The first two methods are not evaluated in this article.
测评程序如下(func_ip.php参见《IP地址->地理位置转换的测评》一文):
Two evaluation results (accurate to 3 decimal places, unit is second):
MySQL query conversion: 0.112
Text query conversion: 10.590
MySQL query conversion :0.099
Text query conversion: 10.623
It can be seen that this time the MySQL method is far ahead of the file query method. But there is no rush to use the MySQL method now, because the text file method is so time-consuming mainly because it has to read the entire gb_unicode.txt into the memory for each conversion, and gb_unicode.txt is a text file with the following format:
Text file The efficiency is low, so I considered converting the text file into a binary file, and then using the half-way method to find the file without reading the entire file into memory. The file format is: the file header is 2 bytes, storing the number of records; then records are stored in the file one by one, each record is 4 bytes, the first 2 bytes correspond to the GB code, and the last 2 bytes correspond to the Unicode code. The conversion procedure is as follows:
After executing the program The binary GB->Unicode comparison table gbu.dat was obtained, and the data records were sorted by GB code to facilitate searching using the half-way method. The function for transcoding using gbu.dat is as follows:
There are at least four ways to dynamically convert GB->UTF-8 encoding:
Convert using PHP's iconv extension
Use PHP's mb_string Extended conversion
Convert using a swap table, the swap table is stored in the MySQL database
Convert using a swap table, the swap table is stored in a text file
The first two This method can only be used if the server has been configured accordingly (corresponding extensions have been compiled and installed). My virtual host does not have these two extensions, so I have to consider the latter two methods. The first two methods are not evaluated in this article.
测评程序如下(func_ip.php参见《IP地址->地理位置转换的测评》一文):
require_once ("func_ip.php");
function u2utf8($c) {
$str = "";
if ($c < 0x80) {
$str .= $c;
} elseif ($c < 0x800) {
$str .= chr(0xC0 | $c >> 6);
$str .= chr(0x80 | $c & 0x3F);
} elseif ($c < 0x10000) {
$str .= chr(0xE0 | $c >> 12);
$str .= chr(0x80 | $c >> 6 & 0x3F);
$str .= chr(0x80 | $c & 0x3F);
} elseif ($c < 0x200000) {
$str .= chr(0xF0 | $c >> 18);
$str .= chr(0x80 | $c >> 12 & 0x3F);
$str .= chr(0x80 | $c >> 6 & 0x3F);
$str .= chr(0x80 | $c & 0x3F);
}
return $str;
}
function GB2UTF8_SQL($strGB) {
if (!trim($strGB)) return $strGB;
$strRet = "";
$intLen = strlen($strGB);
for ($i = 0; $i < $intLen; $i++) {
if (ord($strGB{$i}) > 127) {
$strCurr = substr($strGB, $i, 2);
$intGB = hexdec(bin2hex($strCurr)) - 0x8080;
$strSql = "SELECT code_unicode FROM nnstats_gb_unicode
WHERE code_gb = ".$intGB." LIMIT 1"
;
$resResult = mysql_query($strSql);
if ($arrCode = mysql_fetch_array($resResult)) $strRet .= u2utf8($arrCode["code_unicode"]);
else $strRet .= "??";
$i++;
} else {
$strRet .= $strGB{$i};
}
}
return $strRet;
}
function GB2UTF8_FILE($strGB) {
if (!trim($strGB)) return $strGB;
$arrLines = file("gb_unicode.txt");
foreach ($arrLines as $strLine) {
$arrCodeTable[hexdec(substr($strLine, 0, 6))] = hexdec(substr($strLine, 7, 6));
}
$strRet = "";
$intLen = strlen($strGB);
for ($i = 0; $i < $intLen; $i++) {
if (ord($strGB{$i}) > 127) {
$strCurr = substr($strGB, $i, 2);
$intGB = hexdec(bin2hex($strCurr)) - 0x8080;
if ($arrCodeTable[$intGB]) $strRet .= u2utf8($arrCodeTable[$intGB]);
else $strRet .= "??";
$i++;
} else {
$strRet .= $strGB{$i};
}
}
return $strRet;
}
function EncodeIp($strDotquadIp) {
$arrIpSep = explode(., $strDotquadIp);
if (count($arrIpSep) != 4) return 0;
$intIp = 0;
foreach ($arrIpSep as $k => $v) $intIp += (int)$v * pow(256, 3 - $k);
return $intIp;
//return sprintf(\%02x%02x%02x%02x, $arrIpSep[0], $arrIpSep[1], $arrIpSep[2], $arrIpSep[3]);
}
function GetMicroTime() {
list($msec, $sec) = explode(" ", microtime());
return ((double)$msec + (double)$sec);
}
for ($i = 0; $i < 100; $i++) { // 随机产生100个ip地址
$strIp = mt_rand(0, 255).".".mt_rand(0, 255).".".mt_rand(0, 255).".".mt_rand(0, 255);
$arrAddr[$i] = ip2addr(EncodeIp($strIp));
}
$resConn = mysql_connect("localhost", "netnest", "netnest");
mysql_select_db("test");
// 测评MySQL查询的编码转换
$dblTimeStart = GetMicroTime();
for ($i = 0; $i < 100; $i++) {
$strUTF8Region = GB2UTF8_SQL($arrAddr[$i]["region"]);
$strUTF8Address = GB2UTF8_SQL($arrAddr[$i]["address"]);
}
$dblTimeDuration = GetMicroTime() - $dblTimeStart;
// 测评结束并输出结果
echo $dblTimeDuration; echo " ";
// 测评文本文件查询的编码转换
$dblTimeStart = GetMicroTime();
for ($i = 0; $i < 100; $i++) {
$strUTF8Region = GB2UTF8_FILE($arrAddr[$i]["region"]);
$strUTF8Address = GB2UTF8_FILE($arrAddr[$i]["address"]);
}
$dblTimeDuration = GetMicroTime() - $dblTimeStart;
// 测评结束并输出结果
echo $dblTimeDuration; echo " ";
?>
function u2utf8($c) {
$str = "";
if ($c < 0x80) {
$str .= $c;
} elseif ($c < 0x800) {
$str .= chr(0xC0 | $c >> 6);
$str .= chr(0x80 | $c & 0x3F);
} elseif ($c < 0x10000) {
$str .= chr(0xE0 | $c >> 12);
$str .= chr(0x80 | $c >> 6 & 0x3F);
$str .= chr(0x80 | $c & 0x3F);
} elseif ($c < 0x200000) {
$str .= chr(0xF0 | $c >> 18);
$str .= chr(0x80 | $c >> 12 & 0x3F);
$str .= chr(0x80 | $c >> 6 & 0x3F);
$str .= chr(0x80 | $c & 0x3F);
}
return $str;
}
function GB2UTF8_SQL($strGB) {
if (!trim($strGB)) return $strGB;
$strRet = "";
$intLen = strlen($strGB);
for ($i = 0; $i < $intLen; $i++) {
if (ord($strGB{$i}) > 127) {
$strCurr = substr($strGB, $i, 2);
$intGB = hexdec(bin2hex($strCurr)) - 0x8080;
$strSql = "SELECT code_unicode FROM nnstats_gb_unicode
WHERE code_gb = ".$intGB." LIMIT 1"
;
$resResult = mysql_query($strSql);
if ($arrCode = mysql_fetch_array($resResult)) $strRet .= u2utf8($arrCode["code_unicode"]);
else $strRet .= "??";
$i++;
} else {
$strRet .= $strGB{$i};
}
}
return $strRet;
}
function GB2UTF8_FILE($strGB) {
if (!trim($strGB)) return $strGB;
$arrLines = file("gb_unicode.txt");
foreach ($arrLines as $strLine) {
$arrCodeTable[hexdec(substr($strLine, 0, 6))] = hexdec(substr($strLine, 7, 6));
}
$strRet = "";
$intLen = strlen($strGB);
for ($i = 0; $i < $intLen; $i++) {
if (ord($strGB{$i}) > 127) {
$strCurr = substr($strGB, $i, 2);
$intGB = hexdec(bin2hex($strCurr)) - 0x8080;
if ($arrCodeTable[$intGB]) $strRet .= u2utf8($arrCodeTable[$intGB]);
else $strRet .= "??";
$i++;
} else {
$strRet .= $strGB{$i};
}
}
return $strRet;
}
function EncodeIp($strDotquadIp) {
$arrIpSep = explode(., $strDotquadIp);
if (count($arrIpSep) != 4) return 0;
$intIp = 0;
foreach ($arrIpSep as $k => $v) $intIp += (int)$v * pow(256, 3 - $k);
return $intIp;
//return sprintf(\%02x%02x%02x%02x, $arrIpSep[0], $arrIpSep[1], $arrIpSep[2], $arrIpSep[3]);
}
function GetMicroTime() {
list($msec, $sec) = explode(" ", microtime());
return ((double)$msec + (double)$sec);
}
for ($i = 0; $i < 100; $i++) { // 随机产生100个ip地址
$strIp = mt_rand(0, 255).".".mt_rand(0, 255).".".mt_rand(0, 255).".".mt_rand(0, 255);
$arrAddr[$i] = ip2addr(EncodeIp($strIp));
}
$resConn = mysql_connect("localhost", "netnest", "netnest");
mysql_select_db("test");
// 测评MySQL查询的编码转换
$dblTimeStart = GetMicroTime();
for ($i = 0; $i < 100; $i++) {
$strUTF8Region = GB2UTF8_SQL($arrAddr[$i]["region"]);
$strUTF8Address = GB2UTF8_SQL($arrAddr[$i]["address"]);
}
$dblTimeDuration = GetMicroTime() - $dblTimeStart;
// 测评结束并输出结果
echo $dblTimeDuration; echo " ";
// 测评文本文件查询的编码转换
$dblTimeStart = GetMicroTime();
for ($i = 0; $i < 100; $i++) {
$strUTF8Region = GB2UTF8_FILE($arrAddr[$i]["region"]);
$strUTF8Address = GB2UTF8_FILE($arrAddr[$i]["address"]);
}
$dblTimeDuration = GetMicroTime() - $dblTimeStart;
// 测评结束并输出结果
echo $dblTimeDuration; echo " ";
?>
Two evaluation results (accurate to 3 decimal places, unit is second):
MySQL query conversion: 0.112
Text query conversion: 10.590
MySQL query conversion :0.099
Text query conversion: 10.623
It can be seen that this time the MySQL method is far ahead of the file query method. But there is no rush to use the MySQL method now, because the text file method is so time-consuming mainly because it has to read the entire gb_unicode.txt into the memory for each conversion, and gb_unicode.txt is a text file with the following format:
0x2121 0x3000 # IDEOGRAPHIC SPACE
0x2122 0x3001 # IDEOGRAPHIC COMMA
0x2123 0x3002 # IDEOGRAPHIC FULL STOP
0x2124 0x30FB # K ATAKANA MIDDLE DOT
0x2125 0x02C9 # MODIFIER LETTER MACRON (Mandarin Chinese first tone)
……
0x552A 0x6458 #
0x552B 0x658B #
0x552C 0x5B85 #
0x552D 0x7A84 #
……
0x777B 0x9F37 #
0x777C 0x9F3D #
0x777D 0x9F3E #
0x777E 0x9F44 #
0x2122 0x3001 # IDEOGRAPHIC COMMA
0x2123 0x3002 # IDEOGRAPHIC FULL STOP
0x2124 0x30FB # K ATAKANA MIDDLE DOT
0x2125 0x02C9 # MODIFIER LETTER MACRON (Mandarin Chinese first tone)
……
0x552A 0x6458 #
0x552B 0x658B #
0x552C 0x5B85 #
0x552D 0x7A84 #
……
0x777B 0x9F37 #
0x777C 0x9F3D #
0x777D 0x9F3E #
0x777E 0x9F44 #
Text file The efficiency is low, so I considered converting the text file into a binary file, and then using the half-way method to find the file without reading the entire file into memory. The file format is: the file header is 2 bytes, storing the number of records; then records are stored in the file one by one, each record is 4 bytes, the first 2 bytes correspond to the GB code, and the last 2 bytes correspond to the Unicode code. The conversion procedure is as follows:
$arrLines = file("gb_unicode.txt");
foreach ($arrLines as $strLine) {
$arrCodeTable[hexdec(substr($strLine, 0, 6 ))] = hexdec(substr($strLine, 7, 6));
}
ksort($arrCodeTable);
$intCount = count($arrCodeTable);
$strCount = chr( $intCount % 256) . chr(floor($intCount / 256));
$fileGBU = fopen("gbu.dat", "wb");
fwrite($fileGBU, $strCount);
foreach ($arrCodeTable as $k => $v) {
$strData = chr($k % 256) . chr(floor($k / 256)) . chr($v % 256) . chr( floor($v / 256));
fwrite($fileGBU, $strData);
}
fclose($fileGBU);
?>
foreach ($arrLines as $strLine) {
$arrCodeTable[hexdec(substr($strLine, 0, 6 ))] = hexdec(substr($strLine, 7, 6));
}
ksort($arrCodeTable);
$intCount = count($arrCodeTable);
$strCount = chr( $intCount % 256) . chr(floor($intCount / 256));
$fileGBU = fopen("gbu.dat", "wb");
fwrite($fileGBU, $strCount);
foreach ($arrCodeTable as $k => $v) {
$strData = chr($k % 256) . chr(floor($k / 256)) . chr($v % 256) . chr( floor($v / 256));
fwrite($fileGBU, $strData);
}
fclose($fileGBU);
?>
After executing the program The binary GB->Unicode comparison table gbu.dat was obtained, and the data records were sorted by GB code to facilitate searching using the half-way method. The function for transcoding using gbu.dat is as follows:
function GB2UTF8_FILE1($strGB) {
if (!trim($strGB)) return $strGB;
$fileGBU = fopen("gbu.dat", "rb");
$strBuf = fread($fileGBU, 2);
$intCount = ord($strBuf{0}) + 256 * ord($strBuf{1});
$strRet = "";
$intLen = strlen($strGB);
for ($i = 0; $i < $intLen; $i++) {
if (ord($strGB{$i}) > 127) {
$ strCurr = substr($strGB, $i, 2);
$intGB = hexdec(bin2hex($strCurr)) - 0x8080;
$intStart = 1;
$intEnd = $intCount;
while ($intStart < $intEnd - 1) { // Search by half method
$intMid = floor(($intStart + $intEnd
if (!trim($strGB)) return $strGB;
$fileGBU = fopen("gbu.dat", "rb");
$strBuf = fread($fileGBU, 2);
$intCount = ord($strBuf{0}) + 256 * ord($strBuf{1});
$strRet = "";
$intLen = strlen($strGB);
for ($i = 0; $i < $intLen; $i++) {
if (ord($strGB{$i}) > 127) {
$ strCurr = substr($strGB, $i, 2);
$intGB = hexdec(bin2hex($strCurr)) - 0x8080;
$intStart = 1;
$intEnd = $intCount;
while ($intStart < $intEnd - 1) { // Search by half method
$intMid = floor(($intStart + $intEnd