phptext.net PHPText.Net
PHPText.Net > 文章 > 技术 > 数据库

MySQL 错误之编码不同 Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

2014/12/24 23:32
4243人阅读
由于前期的一些设计疏忽导致字段编码不统一,带来了 这次的错误,我么一起来拜读吧~~~

今天有个SQL执行不了,通过错误输出查到了问题,大致SQL如下:

SELECT * FROM `table` WHERE `field1`=`filed2`

这个SQL看起来是一点错误都没有,但是缺爆出了下面这个错误:

Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

意思就是编码不一致不能用“=” 运算符。那怎么办呢。。。还用说就是转换编码了,还好MySQL也有相关的函数。

CONVERT(table-colum USING utf8) COLLATE utf8_unicode_ci

比如是field1编码是utf8_unicode_ci,filed2编码是utf8_general_ci那么就会出现,那么SQL语句应该修改成这样子。

SELECT * FROM `table` WHERE `field1`=CONVERT(`filed2` USING utf8) COLLATE utf8_unicode_ci

这样编码就统一了,不过这个只是临时解决。最终还是根解决最好,直接修改字段的本身编码。

同类推荐

MySQL Column 'xxx' specified twice / 重复字段MySQL Column 'xxx' specified twice / 重复字段
MySQL计算经纬度距离、获取距离范围内的数据 1KM 3KM 5KMMySQL计算经纬度距离、获取距离范围内的数据 1KM 3KM 5KM
MySQL 错误之 1558 - Column count of mysql.proc is wrong.MySQL 错误之 1558 - Column count of mysql.proc is wrong.
安装MySQL遇到 mysql srever setup wizard ended prematurely because of an error. your system has not been modified.安装MySQL遇到 mysql srever setup wizard ended prematurely because of an error. your system has not been modified.
MySQL 错误之编码不同 Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='MySQL 错误之编码不同 Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='
MySQL严格模式关闭 / STRICT_TRANS_TABLES / 1364 - Field xxx doesn't have a default valueMySQL严格模式关闭 / STRICT_TRANS_TABLES / 1364 - Field xxx doesn't have a default value
MySQL innodb 设置引起的 ERROR 2013 (HY000): Lost connection to MySQL server during queryMySQL innodb 设置引起的 ERROR 2013 (HY000): Lost connection to MySQL server during query
MySQL 之 tinyint、smallint、mediumint、int、bigint 的 unsignedMySQL 之 tinyint、smallint、mediumint、int、bigint 的 unsigned
MySQL 错误 Can’t create/write to file ‘C\Windows:\Temp/#sql-XXX.MYI′ (Errcode: 13)MySQL 错误 Can’t create/write to file ‘C\Windows:\Temp/#sql-XXX.MYI′ (Errcode: 13)
MySQL WHERE IN 排序/按照IN排序~MySQL WHERE IN 排序/按照IN排序~