How to convert mysql database charset to utf-8

Question

I have a vb forum with a huge amount of data

I need a way to convert this forum database tables charset with thier data from

latin1_swedish_ci to utf8-general-ci

How to do this ?

Answer 1

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

If you want to change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a statement like this:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

Follow the link for more details.

Be sure to do this in a test enviornment first. You probably wont run into many issues going from latin1 => utf8 but in general you might run into collation "weirdness" when converting charsets. This is especially true if you have any kind of unique constraints on char-ish columns.

Also keep in mind if you do have any char or varchar columns indexed moving to utf-8 will triple the amount of memory needed

Answer 2

You may want to consider setting the database's default character for new tables going forward using ALTER DATABASE. Here is an example using MySQL 5.5.12 for Windows:

mysql> show create database example;
+----------+--------------------------------------------------------------------+
| Database | Create Database                                                    |
+----------+--------------------------------------------------------------------+
| example  | CREATE DATABASE `example` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter database example default character set utf8;
Query OK, 1 row affected (0.01 sec)

mysql> show create database example;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| example  | CREATE DATABASE `example` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

Give it a Try !!!