数据库用的sqlyog 写语句报错:
Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='
我检查了这几个表的的编码都是genercal 按理说没问题,但还是报错,应该是数据库内含的编码冲突,所以百度了一下,解决了问题。
报错的程序:
SELECT A.course AS coursre,project,projectnote
FROM projectinfo A INNER JOIN courseinfo B ON A.`course`=B.`course`
WHERE B.`course` = (
SELECT C.course
FROM `courseinfo` C JOIN `laboratoryinfo` D ON C.`labno` = D.`labno`
WHERE D.labadminno = '200001'
)
改正方法:
CONVERT( D.labno
USING utf8) COLLATE utf8_general_ci
把D.labno
换成“=”你想连接的表的字段
正确运行的代码:
SELECT A.course AS coursre,project,projectnote
FROM projectinfo A INNER JOIN courseinfo B ON A.`course`=CONVERT( B.`course` USING utf8) COLLATE utf8_general_ci
WHERE B.`course` IN (
SELECT C.course
FROM `courseinfo` C JOIN `laboratoryinfo` D ON C.`labno` = CONVERT( D.`labno` USING utf8) COLLATE utf8_general_ci
WHERE D.labadminno = '200001'
)
ok 不报错了