Easiest way to fix MySQL UTF8 to Latin1 character set encoding issues.

MySQL originally used the latin1 character set by default which stored characters in a 2-byte sequence. In recent versions it defaults to UTF-8 to be friendlier to international users.

When migrating MySQL databases, occasionally you’ll see odd characters appear on the new system. For example, a simple quote mark may be replaced by 4-5 characters of junk symbols.

This happens when MySQL is trying to display characters using a different character set to the one they are stored in. To fix, we need to make sure the database is marked as latin1 when we export it from the old system, and then re-encode it into UTF-8 when importing it into it’s new home.

Export:

mysqldump -u $user -p --opt --quote-names --skip-set-charset \
--default-character-set=latin1 $dbname > dump.sql

Import:

mysql -u $user -p --default-character-set=utf8 $dbname < dump.sql

7 Comments

Filed under Code

7 Responses to Easiest way to fix MySQL UTF8 to Latin1 character set encoding issues.

  1. Paul Green

    Excellent thoughts

  2. Issac Maez

    Thanks for that awesome posting. It saved MUCH time :-)

  3. Lindsey

    i love your blog, i have it in my rss reader and always like new things coming up from it.

  4. David Berkan

    So simple – Thanks :)

  5. Thanks for this. I had spent about 2 hours trying out other alternatives before stumbling on this, which fixed it for me.

  6. Karhuvaari

    Very nice! Thank you! :)

  7. Budy

    Wow man you just save my life. I did not spent 2 hours on this, but 2 days. And in a production environment !
    Thank you so much for this tips.

Leave a Reply

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

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>