Mysql: latin1-> utf8. Convert characters to their multibyte equivalents

Question

There was a table in latin1 and site in cp1252 I want to have table in utf8 and site in utf-8

I've done:

1) on web page: Content-Type: text/html;charset=utf-8

2) Mysql: ALTER TABLE XXX CONVERT TO CHARACTER SET utf8

_

This SQL doesn't work as I want - it doesn't convert ä & ü characters in database to their multibyte equivalents

Please Help. Tanks

Answer 1

Since you claim you now get s**t back, it suggests that the characters were modified in the database.

How are you accessing the data in mysql? If you are using a programming interface such as PHP, then you may need to tell that interface what character encoding to expect.

For example, in PHP you will need to call something like mysql_set_charset("utf8"); but it can also be done with an SQL query of SET NAMES utf8

You will then also need to make sure that whatever is displaying the text knows it is utf8 and is rendering with an appropriate encoding. For example, on a web page you would need to set the content type to utf-8. something like Content-Type: text/html;charset=utf-8

Answer 2

As this blog post says, using MySQL's ALTER TABLE CONVERT syntax is A Bad Idea [TM]. Export your data, convert the table and then reimport the data, as described in the blog post.

Another idea: Have you set your default client connection charset via /etc/my.cnf or mysqli::set-charset .

Answer 3

I've been a fool. SET NAMES was missing.

What I know now:

1) Every time the charset of a column is changed, the actual data is ALWAYS recoded! Change field to binary to see that.

2) The charset of a column is prior!, the table and db charset follow in the priority. They are used mainly for setting defaults. (not 100% sure about last sentence)

3) SET NAMES is very important. German characters can come in latin1 and be placed get correctly in utf8 table(recoded by Mysql silently) when you SET NAMES correctly. The server can send data to a web page in the encoding you desire, no matter what the table encoding is. It can be recoded for output

4) If there is a column in encoding A and a column in encoding B, and you compare them (or use LIKE), the Mysql will silently convert them so that it looks like they are in one encoding

5) Mysql is smart. It never operates with text as with bytes unless the type is binary. It always operates as characters! He wants that ё in latin1 would equal ё in utf8 if he knows the data encoding