Convert existing MySQL database from one charset encoding to another

Wednesday, 26 March 2008

Convert existing MySQL database from one charset encoding to another

Warning: Always make backups.


Convert existing MySQL database from one charset encoding to another

ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Convert existing table from one charset encoding to another

ALTER TABLE db_table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

The following commands seem useful (see Related )

Export your latin1 encoded database
mysqldump --user=username --password=password --default-character-set=latin1 --compatible=mysql40 dbname > dump.sql
Import database as utf8
mysql --user=username --password=password --default-character-set=utf8 dbname < dump.sql

Related

 Digg  Del.icio.us  Reddit  SlashDot

8 comments:

Krishantha said...

Thanks really helpful.

In your option 2:
mv command is not due to single space in from and to location. It is better make it more clear by adding one more space char between paths.

eg:
mv delme/.svn /local/path/project/data/

Thanks again.
Krishantha.

Svetoslav Marinov said...

Maybe you've commented a different posting :) anyways thanks for the comment.

GIN said...

Спасибо!

Svetoslav Marinov said...

Zdrastvuy GIN.

Нет проблем!

Нестримний said...

Here is a php script which changes encoding to cp1251

http://tarasprystavskyj.hmarka.net/change_database_encoding.txt

with a litle modification it may serve to you

Svetoslav Marinov said...

Нестримний,

Thanks for comment and the link.

I've seen that in popular blogs and cms such as Wordpress and Drupal people have converted the (var)char fields to binary ones and then converted the scheme to utf.
Otherwise you may loose come content during the conversion.
IMHO.

Bob said...

The script here will, in theory, convert a database to any character set. It preserves compound keys, comments in the DB, etc.

http://bobsguides.com/convert-db-utf8.html

Svetoslav Marinov said...

Thanks for the link Bob