Concatenating a utf8 char field and a datetime field in mysql

In a database of mine (using mysql 5), I needed to make a ‘fake name’ – concatenating the real name and a datetime, so I did a concat(name, ‘(‘,thetime,’)’) in the sql and got just what I expected TheName (2011.02.02 12:45). That is – until I used a name containing letters outside the old 7-bit ascii. if name was NørwegiånName, what I got was NørwegiÃ¥nName (2011.02.02 12:45) in other words – the original utf8 string (I am using utf8 all through the application) was re-encoded into utf8 …

Looking into what mysql had done to my view, it turned out that my simple concat had been automagically turned into concat(cast(name as char charset utf8), ‘(‘,thetime,’)’) – removing the added cast did not change anything. When running the query in mysql workbench i noticed that the concatenated fields came back as a blob – indeed, something rather fishy was going on behind the scenes…

In the end, it turned out that mysql in it attempt to help me had for some reason converted the wrong field. The automatic cast of a datetime field does not seem to return a utf8-string – even though everything from the schema down to the textfields are set as utf8. casting the datetime at last gave what I wanted: concat(name, ‘ (‘,cast(thetime as char charset utf8),’)’) at last gave me my NørwegiånName (2011.02.02 12:45).

Thank you som much for the assistance mysql…

This entry was posted in sql. Bookmark the permalink.