Convert latin1 to utf8

db, development, mysql Add comments

If you import unicode text into latin1 database column, the symbols would be screwed up — russian symbols become a shit like “залоговый депозит”.

To convert such quickly (as a test) you can use Lebedev’s convertor.

To convert the whole table do the following (thanks to dull.ru):

  1. The most important step: dump such data in a file with mysqldump
    mysqldump -u user -p --default-character-set=latin1 --skip-set-charset --no-create-info --extended-insert --complete-insert dbname table > dbname.sql

    If not the whole table data is bad, create another table (CREATE TABLE t2 LIKE t1) and copy wrong rows to the new table.

  2. Replace latin1 by utf8 in the file
    This can be done manually in your editor or by this command:

    sed -r 's/latin1/utf8/g' dbname.sql > dbname_utf.sql
  3. Convert your latin1-table to utf8 (and maybe truncate it):
    ALTER TABLE `table` CONVERT TO CHARACTER SET 'utf8';
  4. Import the utf8 data back to the table:
    mysql -u user -p --default-character-set=utf8 dbname < dbname_utf.sql

That’s it.

Comments are closed.

WP Theme & Icons by N.Design Studio
Entries RSS Comments RSS Log in