針對新版 Mysql 的資料庫查詢修正

[Aiwa]

/class/database/adodb/drivers/adodb-mysql.inc.php 中，建議做如下修改，比較可以在新版的  mysql 中，正常的在資料庫中存取中文。

程式碼:

	function _connect($argHostname, $argUsername, $argPassword, $argDatabasename)
	{
		if (ADODB_PHPVER >= 0x4300)
			$this->_connectionID = mysql_connect($argHostname,$argUsername,$argPassword,
												$this->forceNewConnect,$this->clientFlags);
		else if (ADODB_PHPVER >= 0x4200)
			$this->_connectionID = mysql_connect($argHostname,$argUsername,$argPassword,
												$this->forceNewConnect);
		else
			$this->_connectionID = mysql_connect($argHostname,$argUsername,$argPassword);

		if ($this->_connectionID === false) return false;
		
+		@mysql_query("SET NAMES 'utf8'", $this->_connectionID);

		if ($argDatabasename) return $this->SelectDB($argDatabasename);
		return true;	
	}

程式碼:

	function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
	{
		if (ADODB_PHPVER >= 0x4300)
			$this->_connectionID = mysql_pconnect($argHostname,$argUsername,$argPassword,$this->clientFlags);
		else
			$this->_connectionID = mysql_pconnect($argHostname,$argUsername,$argPassword);
		if ($this->_connectionID === false) return false;
+		@mysql_query("SET NAMES 'utf8'", $this->_connectionID);
		if ($this->autoRollback) $this->RollbackTrans();
		if ($argDatabasename) return $this->SelectDB($argDatabasename);
		return true;	
	}

[markwu]

Hi Aiwa:

瞭解！可是我有一個疑問。因為對DB不是很瞭解。如果 User 的 MySql 設定上不是 UTF-8, 而是 Big5 或 GB2312 或是 ISO8859-1，加這一句會不會有影響？

還是加這一句是專給 Mysql 是 UTF-8 編碼所使用？

Mark

[Aiwa]

Hi Aiwa:

瞭解！可是我有一個疑問。因為對DB不是很瞭解。如果 User 的 MySql 設定上不是 UTF-8, 而是 Big5 或 GB2312 或是 ISO8859-1，加這一句會不會有影響？

還是加這一句是專給 Mysql 是 UTF-8 編碼所使用？

Mark

這個應該是專給 MySQL 是 UTF-8 編碼所使用的。

新版的 MySql ，在安裝的時候，就可以選擇要用多國語言(Unicode)還是要用 localized 的語系。如果選擇了多國語言的時候，就會建立成 Unicode 的資料庫。

如果沒有設定這一行的話，在做資料庫存取的時候，還是會以 unicode 的方式存，可是在某些中文字會有錯亂的情形。ex “小”->“尿”

不過這種情形似乎又只會出現在 PHP5 + 新版 MySQL 才會發生。

相關資料，可以參考 http://dev.mysql.com/doc/mysql/en/charset-unicode.html

謝謝您。

[markwu]

Hi Aiwa:

瞭解！我可能要跟 Oscar 與 lss 討論一下怎麼加到 Core 中。因為 pLog 是多語系的系統，我如果加了這個到核心裡面，可能會對其他語系有影響。

所以我們得想一下怎麼加！

謝謝這麼有用的資訊。

 Mark

[Aiwa]

Dear Mark,

不客氣，這部份我也可以幫忙做做在不同語系的 mysql 下的測試。
我對 plog 的發展也挺有興趣的，希望也能出點力...

[lss]

hi Aiwa & Mark:
我看了Aiwa提供的網址，又查了一些MySQL網站上的資料，這裡說到：

Note that the syntax for SET NAMES differs from that for setting most other options. This statement is available as of MySQL 4.1.0.

我在我的MySQL 4.0.13-nt上試了set names命令，會有錯誤訊息：

#1193 - Unknown system variable 'names'

雖然MySQL 4.1已經是官方網站上的recommand download，我想，短期內，4.0甚至3.x版應該還是佔大部份。
我目前還沒有安裝4.1以上的版本可測試，不過，這應該列入未來版本的考量。如果使用utf8 的table好處多多，那應該列入未來版本的requirement.
謝謝Aiwa

[Aiwa]

Dear lss and Mark,

我做過實驗了。這個指令與資料庫使用的編碼方法無關。
我的資料庫是以 UTF-8 編碼的，但是只要我下 SET NAME 'big5' ，
從資料庫 query 出來的文字都會變成 big5 編碼。

至於舊版本的相容問題，可以利用 mysql_get_server_info()  來取得版本號碼，再判斷要不要下這行 command ，可以參考這裡的說明。

Aiwa

[markwu]

瞭解。可是還是要想一個方式加上去才行。又不會影響到其他語系 User 的使用跟安裝。

你有 Check out SVN 的 Code 下來看嗎？能不能想想看怎麼加上才適合？

Mark

[Aiwa]

瞭解。可是還是要想一個方式加上去才行。又不會影響到其他語系 User 的使用跟安裝。

你有 Check out SVN 的 Code 下來看嗎？能不能想想看怎麼加上才適合？

Mark

唔~~這個嘛~~對於不同語系的使用者，應該可以用

$messages['encoding'] = 'utf-8';

來做判別，取這邊的值來帶到 SET NAMES 'utf-8' 的地方使用。

我最近這幾天才開始接觸 plog ，您的經驗比較豐富，不知這個方法是否可行？

[markwu]

用那邊是對的。只是呼叫的時機。

因為哪只是語系檔， pLog 會等到開始 執行時，才去看你用那個語系。因為同一個 Blog Hosting 允許各種不同語系。我正在想要不要把這個部分變成一個中文 patch，安裝的時候只要是安裝成 utf-8 救 patch 過去。

最近我們為了編碼的很傷腦筋。Oscar 有點後悔沒一開始決定只用 UTF-8。呵呵喝！

Mark

[pest]

Mark,

我這邊是跑 mysql 4.1.9, 其實這個問題跟 create database 時選擇的 encoding 也有關係, 一個 work around 的方式是在 create database 時不要選擇 utf-8 encoding, 這樣就可以避開這個問題, 等到將來大部份人都升級到 4.1 or later 再處理。另一個作法是判斷 mysql 4.1+ 則強制使用 utf8 database encoding。

講了一堆，這東西可以交給我來試看看嗎?

[markwu]

Hi Pest:

當然！ DB 是我最不熟的！有高手願意來作，我當然讓賢啦！嘻嘻！（其實是懶啦）

呵呵！！

另外，你看一下 http://bugs.lifetype.net/view.php?id=149&nbn=28#bugnotes 這跟你上次提到的 preview 問題一樣，但是 preview 問題是在 UTF-8 解了，其他編碼確有其他問題跑出。

呵呵！所以 Oscar 說很後悔沒有在一開始決定只用 UTF-8。

Mark

[pest]

完成了，同樣 patch 在 class/database/adodb/drivers/adodb-mysql.inc.php 上面, patch 檔也可以在 http://ccca.nctu.edu.tw/~cwyeh/patch.txt 下載。

基本原理如下:

* _isSupprtUtf8() 判斷 mysql 是否支援 utf8。若 4.0 or under 就 return false; 4.1+ 的則再檢查 compile 進來的 character set 是否包括 utf8，若是沒有的話就 return false, 有就 return true;
* _getDbDefaultEncoding() 若是支援 utf8, 則取得現在資料庫的 encoding, 不論是 utf8/latin1/big5 都可以取得。
* 然後再 apply SET NAMES $dbEncoding。

剛剛測試過應該是可以 work 的，不過如果 db 本身 default encoding 設為 utf8, 結果 table 卻是 big5/latin1, 用這個方法是不行的。

測試前，請確認 apache 沒有用到舊的 mysql connection, 最好的辦法就是把 mysql restart 一次。另外請記得刪掉 tmp/ 下面的 cache，不然會看到舊的內容。有問題歡迎提出來。

--- adodb-mysql.inc.php.orig    Thu Feb  3 15:30:35 2005
+++ adodb-mysql.inc.php Thu Feb  3 15:28:05 2005
@@ -344,6 +344,14 @@
                        $this->_connectionID = mysql_connect($argHostname,$argUsername,$argPassword);

                if ($this->_connectionID === false) return false;
+
+               if ($this->_supportUtf8() && $argDatabasename) {
+                       $dbEncoding = $this->_getDbDefaultEncoding($argDatabasename);
+                       if ($dbEncoding) {
+                               mysql_query("SET NAMES $dbEncoding", $this->_connectionID);
+                       }
+               }
+
                if ($argDatabasename) return $this->SelectDB($argDatabasename);
                return true;
        }
@@ -357,6 +365,13 @@
                        $this->_connectionID = mysql_pconnect($argHostname,$argUsername,$argPassword);
                if ($this->_connectionID === false) return false;
                if ($this->autoRollback) $this->RollbackTrans();
+
+               if ($this->_supportUtf8() && $argDatabasename) {
+                       $dbEncoding = $this->_getDbDefaultEncoding($argDatabasename);
+                       if ($dbEncoding) {
+                               mysql_query("SET NAMES $dbEncoding", $this->_connectionID);
+                       }
+               }
                if ($argDatabasename) return $this->SelectDB($argDatabasename);
                return true;
        }
@@ -365,6 +380,44 @@
        {
                $this->forceNewConnect = true;
                return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename);
+       }
+
+       function _supportUtf8() {
+
+               // check mysql version first. Version lower than 4.1 doesn't support utf8
+               $serverVersion = mysql_get_server_info($this->_connectionID);
+               $version = explode('.', $serverVersion);
+               if ($version[0] < 4) return false;
+               if ( ($version[0] == 4) && ($version[1] == 0) ) return false;
+
+               // check if utf8 support was compiled in
+               $result = mysql_query("SHOW CHARACTER SET like 'utf8'", $this->_connectionID);
+               if (mysql_num_rows($result) > 0) {
+                       return true;
+               }
+               return false;
+       }
+
+       function _getDbDefaultEncoding($argDatabasename)
+       {
+
+               if (!$argDatabasename) {
+                       return false;
+               }
+
+               // We use a SHOW CREATE DATABASE command to show the original
+               // SQL character set when DB was created.
+               $result = mysql_query("SHOW CREATE DATABASE $argDatabasename", $this->_connectionID);
+               if (mysql_num_rows($result) < 0 ) {
+                       // The specified db name is wrong!
+                       return false;
+               }
+               $dbInfo = mysql_fetch_row($result);
+               $pattern = '/40100 DEFAULT CHARACTER SET (\w+) /';
+               if ( (preg_match($pattern, $dbInfo[1], $match) > 0) ) {
+                       return $match[1];
+               }
+               return false;
        }

        function &MetaColumns($table)

[pest]

我把它 report 成 http://bugs.lifetype.net/view.php?id=201 了。

[Aiwa]

基本原理如下:

* _isSupprtUtf8() 判斷 mysql 是否支援 utf8。若 4.0 or under 就 return false; 4.1+ 的則再檢查 compile 進來的 character set 是否包括 utf8，若是沒有的話就 return false, 有就 return true;
* _getDbDefaultEncoding() 若是支援 utf8, 則取得現在資料庫的 encoding, 不論是 utf8/latin1/big5 都可以取得。
* 然後再 apply SET NAMES $dbEncoding。

唔~~好像怪怪的.....

SET NAMES 這個指令應該是不必管 server 的 character set 是什麼的，這個指令是用來指定 client 端的 character set 的，也就是說，如果資料庫是以 big5 的編碼儲存的，在經過 SET NAMES 'utf-8' 之後， query 出來的資料都會被轉為 utf-8 的格式。同樣的， input 進 database 的資料，也會由 utf-8 轉換成 big5。

在這邊有個 example 如下：

Example: Suppose that column1 is defined as CHAR(5) CHARACTER SET latin2. If you do not say SET NAMES or SET CHARACTER SET, then for SELECT column1 FROM t, the server will send back all the values for column1 using the character set that the client specified when it connected. On the other hand, if you say SET NAMES 'latin1' or SET CHARACTER SET latin1, then just before sending results back, the server will convert the latin2 values to latin1. Conversion may be lossy if there are characters that are not in both character sets.

看起來，我們似乎不需要去 check server 端的編碼是採用哪一種的，只要設定 SET NAMES 'xxxx' 到相對 blog 所使用的編碼方式就可以了。

(不過如果用了某一邊沒有的文字，就會有些文字轉換失敗，而造成文字不見。ex：database 用  utf-8，且有日文字。但是 client 端用的是 big5 編碼。ex2: database 用的是 big5 編碼，client 端用的是  utf-8 ，且 submit 了日文的內容。)