This simple concat expression works in MySQL 4.1.21-standard:
select CONCAT(DATE_FORMAT(dtmTest,”%Y-%m-%d”), ” – “, strType)
FROM tbltest
but not in MySQL 5.0.45-community-nt:
Error Code : 1270
Illegal mix of collations (utf8_general_ci,COERCIBLE), (utf8_general_ci,COERCIBLE), (latin1_swedish_ci,IMPLICIT) for operation ‘concat’
(0 ms taken)
Solution: use COLLATE thus:
select CONCAT(DATE_FORMAT(dtmTest,”%Y-%m-%d”), ” – ” COLLATE utf8_general_ci, strType)
FROM tbltest;
This is relevant: http://dev.mysql.com/doc/refman/5.0/en/charset-collate.html
and the introduction of string repertoire could possibly make the problem go away in later versions?
http://dev.mysql.com/doc/refman/5.0/en/charset-repertoire.html