HOWTO: Convert mysql data to UTF-8 quickly

Posted on Tuesday, Jun 5, 2007 at 12:37 AM in Linux, Mysql

I've been revamping my Bolivian soccer blog, which I finally updated and made live tonight.  In doing so, I needed to convert the data from ISO-8859-1 (back when I didn't know better about how to setup a database) to UTF-8.  I was fearing the process would be complicated and tedious, involving some script to read the data and use PHP's mbstring or iconv extensions to do the work.  Thanks to a post on a Mysql message board (I wish I'd saved the link, I'd give you full credit), it turned out to be a drop dead simple, or is it quick-and-dirty, 3 step process at the command line using the iconv tool.

$ mysqldump --opt --quote-names my_database > my_database.sql
$ iconv -f ISO_8859-1 -t UTF-8 my_database.sql > my_database-utf-8.sql
$ mysql my_database < my_database-utf-8.sql

Comments

jo says

that will convert your existing data but won't do anything to database/table/field definitions
and without that you will have converted your data in vain pretty much

Posted Tuesday, Jun 5, 2007 at 05:26 AM

Oscar says

Hence the title being about how to convert data. I should have added the switch to just get the data from mysqldump. Of course, you'd still have to convert the table defintions to use UTF-8.

Posted Tuesday, Jun 5, 2007 at 10:51 AM

Sandy says

BTW, I found out today that if you have already dumped UTF-8 data stored in a latin-1 database,
you're still SOL.

Posted Thursday, Jun 14, 2007 at 06:52 PM

jo says

Interesting. Why are you SOL? (i.e, what is it that won't work). I guess you're saying that
running iconv (to either latin-1 or UTF-8) on UTF-8 data stored as latin-1 won't work?

Posted Tuesday, Jun 19, 2007 at 05:59 AM

Post your comment