How to convert an entire MySQL database characterset and collation to UTF-8?

Question

How can I convert entire MySQL database character-set to UTF-8 and collation to UTF-8?

Answer 1

Use the ALTER DATABASE and ALTER TABLE commands.

ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 

Or if you're still on MySQL 5.5.2 or older which didn't support 4-byte UTF-8, use utf8 instead of utf8mb4:

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci; ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; 

Answer 2

1. Make a backup!

2. Then you need to set the default char sets on the database. This does not convert existing tables, it only sets the default for newly created tables.

   ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci; 

3. Then, you will need to convert the char set on all existing tables and their columns. This assumes that your current data is actually in the current char set. If your columns are set to one char set but your data is really stored in another then you will need to check the MySQL manual on how to handle this.

   ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;