{"id":901,"date":"2011-02-03T09:46:48","date_gmt":"2011-02-03T07:46:48","guid":{"rendered":"http:\/\/sickel.net\/blogg\/?p=901"},"modified":"2011-05-05T22:09:05","modified_gmt":"2011-05-05T20:09:05","slug":"concatenating-a-utf8-char-field-and-a-datetime-field-in-mysql","status":"publish","type":"post","link":"https:\/\/sickel.net\/blogg\/?p=901","title":{"rendered":"Concatenating a utf8 char field and a datetime field in mysql"},"content":{"rendered":"

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)<\/em>. That is – until I used a name containing letters outside the old 7-bit ascii. if name was N\u00f8rwegi\u00e5nName, what I got was N\u00c3\u00b8rwegi\u00c3\u00a5nName (2011.02.02 12:45)<\/em> in other words – the original utf8 string (I am using utf8 all through the application) was re-encoded into utf8 …<\/p>\n

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… <\/p>\n

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\u00f8rwegi\u00e5nName (2011.02.02 12:45)<\/em>.<\/p>\n

Thank you som much for the assistance mysql…<\/p>\n","protected":false},"excerpt":{"rendered":"

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 … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[27],"tags":[],"class_list":["post-901","post","type-post","status-publish","format-standard","hentry","category-sql"],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/pnVtD-ex","jetpack_sharing_enabled":true,"jetpack_likes_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=\/wp\/v2\/posts\/901","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=901"}],"version-history":[{"count":3,"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=\/wp\/v2\/posts\/901\/revisions"}],"predecessor-version":[{"id":1027,"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=\/wp\/v2\/posts\/901\/revisions\/1027"}],"wp:attachment":[{"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=901"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=901"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=901"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}