Convert WordPress database from Latin1 to UTF-8

January 12, 2008 5:39 pm UTC | In Tech

Some early WordPress databases use raw Latin1 encoding to represent UTF-8 characters. The following seems to be an O.K. way to convert those databases to native UTF-8 databases (at least it works for me, Mysql 5.0.45):

  1. Stop/restrict web service.
  2. Dump the database:
    $ mysqldump --default-character-set=latin1 --databases wordpress > m.sql
  3. Change encoding settings in m.sql, including table default encoding and client communication encoding:
    $ replace "CHARSET=latin1" "CHARSET=utf8" \
        "SET NAMES latin1" "SET NAMES utf8" < m.sql > m2.sql
  4. Import the “converted” sql:
    $ mysql < m2.sql
  5. Add the following lines to WordPress wp-config.php
    define('DB_CHARSET', 'utf8');
    define('DB_COLLATE', '');
  6. Reopen web service. That’s it.

Note: For future convenience, you might as well change the database default encoding to utf8.

Ref to WordPress documentation for details: here and here.

Tags: , , , ,

4 Comments »

RSS feed for comments on this post.

  1. Lord of wordpress!!!!!

    Comment by kxn — January 12, 2008 10:17 pm UTC #

  2. 要命,前两天转trac DB,我怎么没想到 replace …

    Orz

    Comment by hutuworm — January 16, 2008 10:25 pm UTC #

  3. Hi, I would extremely like to use your work,
    but unfortunately, I got this message:
    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘$ replace “CHARSET=latin1″ “CHARSET=utf8″ \
    “SET NAMES latin1″ “SET NAMES u’ at line 1

    it might be really simple – but I do not know about it..

    Comment by csaba — April 13, 2009 2:27 pm UTC #

  4. Hi,

    I’m new to this, sorry. Where do you enter these commands?

    Via the terminal on sql prompt? I try but no changes to collation occur?

    Please help

    Comment by jb — January 26, 2016 4:49 am UTC #

Leave a comment

XHTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

This weblog is licensed under a Creative Commons License.
Powered by WordPress. Theme based on Pool by Borja Fernandez.