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

character-encoding, encoding, mysql, php

Solution

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.

Problem

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.

Original source