mysql提示:Illegal mix of collations for operation ‘UNION’
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 不可以