Convert MySQL tables and data from latin1 to utf8

Submitted by Kamal Wickramanayake on September 12, 2009 - 17:14

Context: MySQL tables maintain textual data in latin1 character encoding unless the default settings are overridden at the server, database, table or column level. Still it is possible that text in utf8 goes into these latin1 tables (for example via a web form submission). For some reason you want to change the character set from latin1 to utf8.

Problem: MySQL has alter table and other commands that allow you to change the character set of tables (database and columns too) and also to automatically convert the data from source character set to destination character set. However, they don't work well here since you have utf8 characters in latin1 columns.

Solution: Here's a PHP script that correctly handles the conversion where latin1 column data is first converted to binary and then back to utf8. latin1-to-utf8.txt