UTF-8ify an existing MySQL database

First do

ALTER DATABASE database_name CHARACTER SET "utf8";
ALTER DATABASE database_name COLLATE "utf8_unicode_ci";

After that, for each table:

ALTER TABLE table_name DEFAULT CHARACTER SET "utf8" COLLATE "utf8_unicode_ci";

This just changes the default character set / collation for each table. To convert them, you need:

ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

Henning Koch

Last edit

2014-08-06

Keywords

utf8, mysql

License

Source code in this card is licensed under the MIT License.

Dumping and importing from/to MySQL in an UTF-8 safe way

In a nutshell: to avoid your shell character set from messing with imports, use -r to export and SOURCE when importing.

# Do not do this, since it might screw up encoding
mysqldump -uroot -p database...

To show the MySQL default character set you have to login to the MySQL console and execute SHOW VARIABLES LIKE 'char%';

mysql> SHOW VARIABLES LIKE  'char%';
+--------------------------+----------------------------+
| Variable_name  ...

Posted by Henning Koch to makandra dev (2010-08-26 20:34)