How to convert mysql latin1 to utf8

Question

I inherited a web system that I need to develop further. The system seems to be created by someone who read two chapters of a PHP tutorial and thought he could code...

So... the webpage itself is in UTF8 and displays and inputs everything in it. The database tables have been created with UTF8 character set. But, in the config, there is "SET NAMES LATIN1". In other words, UTF8 encoded strings are populated into the database with forced latin1 coding.

Is there a way to convert this mess to actually store in utf8 and get rid of latin1?

I tried this, but since the database table is set to utf8, this does not work. Also tried this one without success.

I maybe able to do this by reading all tables in PHP with latin1 encoding then write them back to a new database in utf8, but I want to avoid it if possible.

Answer 1

I managed to solve it by running updates on text fields like this:

UPDATE table SET title = CONVERT(CONVERT(CONVERT(title USING latin1) USING binary) USING UTF8)

Answer 2

The situation isn't as bad as you think it is, unless you already have lots of non-Roman characters (that is, characters that aren't representable in Latin-1) in your database already. Latin-1 is a proper subset of utf8. Your web app works in utf8 and your tables' contents are in utf8 as well. So there's no need to convert the tables.

So, try changing the SET NAMES latin1 to SET NAMES utf8. It will probably solve your problem, by allowing your php program's connection to work with the same character set as the code on either end of the connection.

Read this. http://dev.mysql.com/doc/refman/5.7/en/charset-connection.html

Answer 3

Change column data type

to VARBINARY and it's automatically convert the latin1 datas

Thank you guys. I hope it's helpful.