I have latin1 encoded data located in mysql UTF-8 database, how can I fix it?

Question

I have latin1 encoded data located in mysql UTF-8 database, how can I fix it? Unfortunately, there is no source data.

As I understand it, as the only way I could display the data correctly was to install all latin1 in PHP, HTML and MySQL.

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

Version: mysql Ver 14.12 Distribution 5.0.51a for debian-linux-gnu (x86_64) using readline 5.2

EDIT: I have to mention that everything works fine as I speak PHP and HTML to use latin1 encoding, however it just seems bad to me.

Answer 1

I believe this article does exactly what you need .

I rephrased the steps you need to take below - replace "MyDb" with the name of your database. I would recommend making a backup before you start!

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 above statements SELECTinto an SQL script. Add the following to it:

ALTER DATABASE MyDb CHARACTER SET utf8;

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

Answer 2

You can try the following query:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

. , MY-SQL (http://dev.mysql.com/doc/refman/5.0/en/charset-column.html).
, , , , ( , ). , .