mysql提示:Illegal mix of collations for operation ‘UNION’

語句很簡單

代碼如下	複製代碼
<?php $connection=mysql_connect("localhost","root","password"); //串連並選擇資料庫伺服器 mysql_select_db("test",$connection); $query="select * from users where name='你好'"; $result=mysql_query($query,$connection); while($row=mysql_fetch_array($result)) { echo "使用者ID：".$row[0]."<br>"; //也可以寫做$row["user_id"] echo "使用者名稱：".$row[1]."<br>"; //也可以寫做$row["user_name"] } ?>

運行結果提示

Error: Illegal mix of collations (gbk_chinese_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='

意思是說：操作符“=”比較的字元集不一致，一個是gbk_chinese_ci,一個是latin1_swedish_ci。

首先、查看mysql的my.cnf,確認為utf8

代碼如下

複製代碼

mysql> show variables like "%char%"; +--------------------------+-------------------------------------+ | Variable_name            | Value                               | +--------------------------+-------------------------------------+ | character_set_client     | utf8                                | | character_set_connection | utf8                                | | character_set_database   | latin1                              | | character_set_filesystem | binary                              | | character_set_results    | utf8                                | | character_set_server     | utf8                                | | character_set_system     | utf8                                | | character_sets_dir       | D:/MySQL Server 5.0/share/charsets/ | +--------------------------+-------------------------------------+ 8 rows in set (0.00 sec)     mysql> show variables like "%coll%"; +----------------------+-------------------+ | Variable_name        | Value             | +----------------------+-------------------+ | collation_connection | utf8_general_ci   | | collation_database   | latin1_swedish_ci | | collation_server     | utf8_general_ci   | +----------------------+-------------------+ 3 rows in set (0.00 sec)

然後將資料庫中所有編碼方式改為統一：

代碼如下	複製代碼
mysql> set character_set_database=utf8; Query OK, 0 rows affected (0.00 sec)   mysql> set collation_database= utf8_general_ci

重新查看字元集及資料表欄位編碼字元是否一致：

總結

utf8_bin
utf8_general_ci
utf8_unicode_ci

utf8_bin 與 utf8_general_ci 可以

utf8_bin 與 utf8_unicode_ci 可以

utf8_general_ci 與 utf8_unicode_ci 不可以