PHP’s libmysql (Again)

總歸來說,只有兩種解法可以解決一PHP的libmysql每次都用utf8和MySQL伺服器連線的問題。這個問題只會發生在MySQL4.1以上,只要發生就會讓PHP從資料庫拿到的中文字通通變成問號。

這篇文章修正之前那篇提到的解法。

第一個解法是在my.cnf[mysqld]加上下面的設定。不管是Win32或是Linux上的MySQL都可以用。

default-character-set = utf8
default-collation = utf8_unicode_ci
skip-character-set-client-handshake

前兩行將MySQL伺服器的預設邊碼放在UTF-8,第三行叫MySQL忽略libmysql傳過來的字碼連線要求;libmysql總是試圖用utf8連線是造成這個問題的主因。

建議所有管理自己機器的人用這個方法解決亂碼問題。這也是MySQL5 CJK FAQ建議的方法。當然重新編譯libmysql也是可以的,但是那個太複雜了我也不知道怎麼做orz。

如果您沒有權限修改自己的MySQL-PHP環境,還有第二個方法;有點煩,要在所有的PHP程式碼裡面mysql_connect()出現的地方之下加上以下兩行:

mysql_query("SET NAMES 'UTF8'");
mysql_query("SET CHARACTER SET UTF8");

即便是下載的程式也要自己修改。對於WordPress,要修改的位置在wp-db.php

To sum up, there are two solutions for php to connect MySQL server using the correct charset. When the connected in wrong charsets, all non-utf8 characters becomes question marks.

This is the correction to the previous post.

The first solution is to put the following lines in my.cnf under [mysqld] session. Works on both MySQL on Win32 and Linux.

default-character-set = utf8
default-collation = utf8_unicode_ci
skip-character-set-client-handshake

The first two lines changes MySQL default to what the charest needed, and the third one ask MySQL to ignore what libmysql said about the charset – which always attempts to connect with utf8 charset.

This is the solution in MySQL5 CJK FAQ and it’s recommends for everyone with their own machines.

It’s can also be done by re-compile libmysql which is technical advanced and the I-don’t-know-how-to-do-it-so-don’-ask method.

For people did not have control over their MySQL-PHP environment but facing wrong charset connection all the time, you must add following line after every mysql_connect() in PHP scripts.

mysql_query("SET NAMES 'UTF8'");
mysql_query("SET CHARACTER SET UTF8");

This is kind of annoying and is required even in the programs you downloaded. WordPress is included and should be added in wp-db.php.

One thought on “PHP’s libmysql (Again)

  1. Pingback: -TMA-1- » links for 2006-07-26

Leave a Reply

Your email address will not be published. Required fields are marked *