在保存或查詢數據時提示Illegal mix of collations (gbk_chinese_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation /'=/',這里是告訴你你要保存的數據與數據庫中的字符編碼不致導致數據無法正確保存了,解決辦法我們只要統一編碼即可.

語句很簡單,代碼如下:

1. <?php 
2. $connection=mysql_connect("localhost","root","password"); //連接并選擇數據庫服務器 
3. mysql_select_db("test",$connection); 
4. $query="select * from users where name='你好'"; 
5. $result=mysql_query($query,$connection); 
6. while($row=mysql_fetch_array($result)) 
7. { //Vevb.com 
8. echo "用戶ID：".$row[0]."<br>"; //也可以寫做$row["user_id"] 
9. echo "用戶名：".$row[1]."<br>"; //也可以寫做$row["user_name"] 
10. } 
11. ?> 

運行結果提示: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,代碼如下:

1. mysql> show variables like "%char%"; 
2. +--------------------------+-------------------------------------+ 
3. | Variable_name            | Value                               | 
4. +--------------------------+-------------------------------------+ 
5. | character_set_client     | utf8                                | 
6. | character_set_connection | utf8                                | 
7. | character_set_database   | latin1                              | 
8. | character_set_filesystem | binary                              | 
9. | character_set_results    | utf8                                | 
10. | character_set_server     | utf8                                | 
11. | character_set_system     | utf8                                | 
12. | character_sets_dir       | D:/MySQL Server 5.0/share/charsets/ | 
13. +--------------------------+-------------------------------------+ 
14. 8 rows in set (0.00 sec) 
15.  
16. mysql> show variables like "%coll%"; 
17. +----------------------+-------------------+ 
18. | Variable_name        | Value             | 
19. +----------------------+-------------------+ 
20. | collation_connection | utf8_general_ci   | 
21. | collation_database   | latin1_swedish_ci | 
22. | collation_server     | utf8_general_ci   | 
23. +----------------------+-------------------+ 
24. 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 不可以