Today again I had to convert a MySQL database from Latin1 to UTF8. As I don’t want to search for tutorials every time I have to do this, I decided to write my way of doing that down here:
I exported the database using the following command:
mysqldump -u user -p --default-character-set=latin1 --skip-set-charset\ --single-transaction database > database.sql
The “–default-character-set=latin1″ is mandatory, when the default encoding in MySQL already is UTF8.
Then I opened the file in Notepad++ and replaced all “DEFAULT CHARSET=latin1″ with “DEFAULT CHARSET=utf8″. I did not have to change the encoding of the document, as it already was UTF8. If collation is defined, replace it as well (in my databases it generally isn’t).
I created a new database. Just to be sure that the database character set is correct, you can run:
ALTER DATABASE database DEFAULT CHARACTER SET utf8\ DEFAULT COLLATE utf8_general_ci;
After that I imported the sql file:
mysql -u user -p --default-character-set=utf8 database < database.sql