{"id":17,"date":"2007-10-03T18:44:02","date_gmt":"2007-10-03T06:44:02","guid":{"rendered":"http:\/\/p-s.co.nz\/wordpress\/?p=17"},"modified":"2007-11-30T22:21:59","modified_gmt":"2007-11-30T10:21:59","slug":"collation-in-mysql-concat-function","status":"publish","type":"post","link":"http:\/\/p-s.co.nz\/wordpress\/collation-in-mysql-concat-function\/","title":{"rendered":"Collation in MySQL CONCAT function"},"content":{"rendered":"

This simple concat expression works in MySQL 4.1.21-standard:<\/p>\n

select CONCAT(DATE_FORMAT(dtmTest,”%Y-%m-%d”), ” – “, strType)
\nFROM tbltest<\/p>\n

but not in MySQL 5.0.45-community-nt:<\/p>\n

Error Code : 1270
\nIllegal mix of collations (utf8_general_ci,COERCIBLE), (utf8_general_ci,COERCIBLE), (latin1_swedish_ci,IMPLICIT) for operation ‘concat’
\n(0 ms taken)<\/p>\n

Solution: use COLLATE thus:<\/p>\n

select CONCAT(DATE_FORMAT(dtmTest,”%Y-%m-%d”), ” – ” COLLATE utf8_general_ci, strType)
\nFROM tbltest;<\/p>\n

This is relevant: http:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/charset-collate.html<\/a>
\nand the introduction of string repertoire could possibly make the problem go away in later versions?<\/p>\n

http:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/charset-repertoire.html<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

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 … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[],"class_list":["post-17","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"http:\/\/p-s.co.nz\/wordpress\/wp-json\/wp\/v2\/posts\/17"}],"collection":[{"href":"http:\/\/p-s.co.nz\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/p-s.co.nz\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/p-s.co.nz\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/p-s.co.nz\/wordpress\/wp-json\/wp\/v2\/comments?post=17"}],"version-history":[{"count":0,"href":"http:\/\/p-s.co.nz\/wordpress\/wp-json\/wp\/v2\/posts\/17\/revisions"}],"wp:attachment":[{"href":"http:\/\/p-s.co.nz\/wordpress\/wp-json\/wp\/v2\/media?parent=17"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/p-s.co.nz\/wordpress\/wp-json\/wp\/v2\/categories?post=17"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/p-s.co.nz\/wordpress\/wp-json\/wp\/v2\/tags?post=17"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}