MySQL Latin1 to UTF8 conversion

Many people think it’s as simple as doing a mysqldump->sed->mysql command (or clicking Change->Collation in PhpMyAdmin) but that only changes the encoding for NEW data, while leaving the old data intact and possibly coming out dirty.

Luckily there is a really handy tool called iconv that does character set conversions.

So to migrate character encodings properly in a MySQL database use a command like:

$ mysqldump -u USERNAME -pPASSWORD DATABASE |
replace CHARSET=latin1 CHARSET=utf8 |
iconv -f latin1 -t utf8 |
mysql -u USERNAME -pPASSWORD DATABASE

Where USERNAME, PASSWORD & DATABASE should be replaced with your username, password and database, of course.

Codemonkey of Node.js, Django, PHP and all things HTML5 Solving tech challenges, one bit at a time.

Posted in bash, Database, linux, servers Tagged with: , , , , , , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

*