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; $result=mysql_query($query,$connection); while($row=mysql_fetch_arra
语句很简单
代码如下
$connection=mysql_connect("localhost","root","password"); //连接并选择数据库服务器
mysql_select_db("test",$connection);
$query="select * from users where";
$result=mysql_query($query,$connection);
while($row=mysql_fetch_array($result))
{
echo "用户ID:".$row[0]."
"; //也可以写做$row["user_id"]
echo "用户名:".$row[1]."
"; //也可以写做$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 不可以
,推荐阅读
-
mysql提示:Illegal mix of collations for operation ‘UNION’
-
mysql 保存数据出现 Illegal mix of collations (gbk_chinese_ci
-
Mysql彻底解决中文乱码问题的方案(Illegal mix of collations for operation)
-
解决 Mysql Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)
-
对mysql Illegal mix of collations(gbk_chinese_ci,IMPLICIT) a_MySQL
-
MySQL查询:Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) fo
-
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (_MySQL
-
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (_MySQL
-
MySQL Illegal mix of collations for operation 'like' 解决方案
-
mysql 保存数据出现 Illegal mix of collations (gbk_chinese_ci