關於修正 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″

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.