php - How to convert latin1_swedish_ci data into utf8_general_ci?

Question

I have a MySQL database with all the table fields collation as

latin1_swedish_ci

It has almost 1000 of the records already stored and now I want to convert all these data into

utf8_general_ci

So that I can display any language content. I have already altered the field collations into utf8_general_ci but this does not CONVERT all the old records into utf8_general_ci

Answer 1

one funny thing.

CONVERT TO CHARSET and CONVERT()/CAST() suggested by Anshu will work fine if charset in the table is in right encoding.

If for some reason latin1 column containts utf8 text, CONVERT() and CAST() will not be able to help. I had "messed" my database with that setup so spend bit more time on solving this.

to fix this in addition to character set conversion, there are several exercises required.

1. "Hard one" is to recreate the database from dump that will be converted via console
2. "Simple one" is to convert row by row or table by table:

INSERT INTO UTF8_TABLE (UTF8_FIELD)
SELECT convert(cast(convert(LATIN1_FIELD using latin1) as binary) using utf8)
  FROM LATIN1_TABLE;

basically, both cases will process string to original symbols and then to right encoding, that won't happen with simple convert(field using encoding) from table; command.