返回顶部

收藏

Error Code: 1267. Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

更多

mysql中执行字符串比较操作时出现了下面的错误信息:

Error Code: 1267. Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

执行的sql语句如下:

select gu.id as github_id, gu.avatar, gu.site_feed_url,gu.home_url,f.id as feedId,f.flags as feedFlags
from github_user gu
inner join f_feed f on
replace((replace((SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(gu.site_url, '//', ''), '/', 1), '*', -2)), 'http:','')),'https:','')
= f.host
where f.flags & 2 = 0

上面的inner join条件的=比较出错了,可以通过在比较表达式后添加COLLATE utf8_unicode_ci来设置字符集,从而达到比较的目的, 下面是可以正常执行的sql

select gu.id as github_id, gu.avatar, gu.site_feed_url,gu.home_url,f.id as feedId,f.flags as feedFlags
from github_user gu
inner join f_feed f on
replace((replace((SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(gu.site_url, '//', ''), '/', 1), '*', -2)), 'http:','')),'https:','')
= f.host COLLATE utf8_unicode_ci
where f.flags & 2 = 0

标签:mysql,字符集

收藏

0人收藏

支持

1

反对

0