UTF8 and MySQL

To get LAMP to work with UTF8 encoding is not as easy as one would like to think it is. This spring I had huge trouble to get it to work. I had set all the table and database settings to UTF8 and the problem presented itself when PHP connected itself to MySQL and tried to present a query to a (browser) client. The MySQL client always hesitated to convert everything to latin1 encoding.

In the beginning i used the old mysql PHP module and later tried the new mysqli PHP module. Neither of these gave the right encoding. mysqli even has a special function to set the prefered encoding but it didn't work, neither did changing locale on the server/client help me - nothing, niet, nada.

Googling the web told me that I wasn't the only one having this problem. There were numerous bug reports at MySQL's website and people were trying all different sorts of things on forums and blogs. Finally I found a solution to the problem and it is to run the command

SET NAMES utf8

every time the (PHP) MySQL client connects to the server.

If you have administration right to the MySQL server you're connecting to another way is two add the line

[mysqld]
...
init_connect='SET NAMES utf8'
...

in /etc/mysql/my.cnf (or whatever the MySQL configuration file is called...).

Why I'm writing this now is because I didn't have a blog this spring and I just encountered the problem again. It's a strange MySQL haven't done something about this, especially when they're claiming that they have full UTF8 encoding.