mysql提示:Illegal mix of collations for operation ‘UNION’
发布:smiling 来源: PHP粉丝网 添加日期:2014-09-27 15:04:19 浏览: 评论:0
在保存或查询数据时提示Illegal mix of collations (gbk_chinese_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation \'=\',这里是告诉你你要保存的数据与数据库中的字符编码不致导致数据无法正确保存了,解决办法我们只要统一编码即可.
语句很简单,代码如下:
- <?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))
- { //phpfensi.com
- 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 不可以
Tags: Illegal mix of collations operation
相关文章
- ·mysql中tinyint(1)和zerofill是什么意思(2015-04-17)
- ·解决Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general(2014-10-14)
推荐文章
热门文章
最新评论文章
- 写给考虑创业的年轻程序员(10)
- PHP新手上路(一)(7)
- 惹恼程序员的十件事(5)
- PHP邮件发送例子,已测试成功(5)
- 致初学者:PHP比ASP优秀的七个理由(4)
- PHP会被淘汰吗?(4)
- PHP新手上路(四)(4)
- 如何去学习PHP?(2)
- 简单入门级php分页代码(2)
- php中邮箱email 电话等格式的验证(2)