I have latin1 encoded data sitting in a UTF-8 mysql database, how do I fix this?

Question

I have latin1 encoded data sitting in a UTF-8 mysql database, how do I fix this? There is no original data to go from unfortunately.

I figured out this much as the only way I could display the data correctly was to set everything latin1 in PHP, HTML and MySQL.

Once this is completed, I can change everything back to utf-8 in my html and php.

Versions: mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (x86_64) using readline 5.2

EDIT: I should mention, everything is working OK as I am telling PHP and HTML to use latin1 encoding, however, this just seems bad to me.

Answer 1

You could try run the following query:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

NOTE: I haven't tried this, but this is taken from the MY-SQL docs (http://dev.mysql.com/doc/refman/5.0/en/charset-column.html).
The page linked above has some more information, but this seems to be the query that you want (it is posted right at the bottom in case you were wondering). Hope this helps.

Answer 2

I believe that this article does exactly what you need to.

I've paraphrased the steps you need to take below - replace 'MyDb' with the name of your database. I would recommend making a backup before you begin!

USE information_schema;
SELECT CONCAT('ALTER TABLE ', table_name, ' MODIFY ', column_name, ' ', REPLACE(column_type, 'char', 'binary'), ';') FROM columns WHERE table_schema = 'MyDb' and data_type LIKE '%char%';
SELECT CONCAT('ALTER TABLE ', table_name, ' MODIFY ', column_name, ' ', REPLACE(column_type, 'text', 'blob'), ';') FROM columns WHERE table_schema = 'MyDb' and data_type LIKE '%text%';
SELECT CONCAT('ALTER TABLE ', table_name, ' MODIFY ', column_name, ' ', column_type, ' CHARACTER SET utf8;') FROM columns WHERE table_schema = 'MyDb' and data_type LIKE '%char%';
SELECT CONCAT('ALTER TABLE ', table_name, ' MODIFY ', column_name, ' ', column_type, ' CHARACTER SET utf8;') FROM columns WHERE table_schema = 'MyDb' and data_type LIKE '%text%';

Copy the output of all the SELECT statements above into a SQL script. Add the following to it:

ALTER DATABASE MyDb CHARACTER SET utf8;

Switch to MyDb (USE MyDb;) and run the SQL script.