UFT8 in string literals using MySQL client over ssh

MySQL documentation ain't what it used to be; it took some experimenting to figure out how to get a utf string into my db from the command-line client interface.

The problem is that for some reason when I run mysql on my server through ssh, non-latin characters just get dropped. It might be an ssh config thing, you never know, but I explored the mysql avenue first.

Here's what I found. Suppose you want to update a field containing some non-ascii character, like "à", like this:

update events set timetable = 'de 10h à 12h' where id > 100000;

But after you paste this into your shell, the "à" is missing:

update events set timetable = 'de 10h  12h' where id > 100000;

And your data doesn't get updated the way you expect.

This is the mysql documentation on string literals: string-literals.html

And here's a handy utf8 lookup table: http://www.utf8-chartable.de/

And here's the solution:

update events set timetable = concat(_utf8'de 10h ', _utf8 0xC3A0, _utf8' 12h' where id > 100000);

To explain: _utf8 0xC3A0 gives you your "à", you concatenate that with the rest of your string and away you go. It's ugly, but it works, so stop complaining.