關於修正 PHP 與 MySQL 間的字集校對問題

以下以問與答的方式來討論 PHP 與 MySQL 間的字集校對問題:

Q1: 問題出在 php 時:
select Sn from rentdata where ZoneNo=’5′ and Addr=’竹東鎮中興路192號’ and Owner=’jjdai’
Illegal mix of collations (big5_chinese_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation ‘=’

A1.1: select Sn from rentdata where ZoneNo=’5′ and Addr=_big5′竹東鎮中興路192號’ and Owner=’jjdai’
即:
User Comments
Posted by brusselsshrek on September 30 2005 11:12am [Delete] [Edit]
I was having the following error and this page seems to be the key to solving it:
Illegal mix of collations (utf8_bin,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation
When I looked at my table, I had specified the collation sequence as:
utf8_bin
yet in my SQL (in PHP code) I was simply specifying a literal (it was more fancy than “xyz” – arabic or chinese, but you will get the point):
SELECT * from my_table WHERE my_col = “xyz”
Now, the solution proved to be stating:
SELECT * from my_table WHERE my_col = _utf8″xyz”

A1.2: <?php mysql_query(“SET NAMES utf8″); ?> before each query.
A1.3: (of course /ext/mysql/php_mysql.c can always be patched )

Q2: 問題出在 mysqld 時:
A2.1: select Sn from rentdata where ZoneNo=’5′ and Addr=’竹東鎮中興路192號’ and Owner=’jjdai’ COLLATE big5_chinese_ci
A2.2: mysqld –character-set-server=big5 –collation-server=big5_chinese_ci
A2.3: my.ini 改 default-charset

I.
MySQL設定值變成了 :
character_set_client = latin1
character_set_connection = latin1
character_set_database= big5
character_set_result= latin1
character_set_server=big5
character_set_system= latin1

colloation_connection= latin1_swedish_ci
colloation_database= big5_chinese_ci
colloation_server=big5_chinese_ci

II.
[mysqld]
basedir=C:/mysql

datadir=C:/mysql/data
default-character-set=big5
[WinMySQLadmin]
Server=C:/mysql/bin/mysqld-nt.exe
user=root
password=xxxxxxxxxx

[client]
default-character-set=big5

A2.4:
[client]
init-command=”SET NAMES utf8″

你喜歡這篇文章嗎? 馬上分享它:

相關文章:

  1. [MySQL] 解決MySQL server has gone away問題
  2. 將MySQL升級成4.1版遇到 ‘Warning: mysql_connect() [function.mysql-connect]: Client does not support authentication protocol requested by server; consider upgrading MySQL client in …..’ 錯誤訊息要如何解?
  3. 安裝 PHP 時出現error: Cannot find MySQL header files under yes的錯誤訊息
  4. [Apache][MySQL] error_log: [crit] (70023)This function has not been implemented on this platform: DBD: driver for [DBDriver unset] not available
  5. [WEB][PHP] 關於網站線上人數統計的實做
This entry was posted in MySQL, PHP, 實作, 解決 and tagged , . Bookmark the permalink.

Leave a Reply