MySQL UTF8 Conversion - Why dump the database?

Question

I'm needing to convert my MySQL database from latin1_sweedish_ci to utf8, it looks like utf8_bin is my ideal option. I keep reading that some people have exported their databases and recreated them. Is this really necessary? Can't I just run queries to convert the data?

I read that one guy when installing MySQL 5, specified UTF-8 during MySQL compiling...

Answer 1

You have to export the database and convert them. Setting database, table, and row encodings change how they will be operated on in the future, but do not convert the data that has already been stored.

There is no database encoding swap free lunch.

In MySQL 5, you have to reset all the character encodings for the db, tables, and columns, AND THEN re-import the data. The wordpress guide to changing char sets is a good step-by-step resource to do this.

I would suggest reading the Wordpress how-to as the steps are well explained and clear, but the highlights are:

Set DB char set:

ALTER DATABASE db_name CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Then set existing tables encodings:

ALTER TABLE db_table CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Then dump w/ one encoding, re-import with another

mysqldump --user=username --password=password --default-character-set=latin1_swedish_ci dbname > dump.sql

mysql --user=username --password=password --default-character-set=utf8 dbname < dump.sql

Note that if you do not want to dump & reload the whole database, you can perform the change column-by-column by converting each text column to the equivalent blob type, and then converting it back to a text column w/ the desired encoding as described in the Wordpress guide's convert columns to blob and back.