Converting a MySQL database to UTF-8

Converting a MySQL database to UTF-8
This article explains how to convert a MySQL database’s character set to UTF-8 (Unicode), which supports many alphabets and characters for various languages. Many databases use the Latin character set by default, which can be limiting.

Determining the current character encoding set

1. Log in to your account via SSH:

mysql -u username -p

Enter your password when prompted.

2. To check the character set for a database (replace dbname with your database name):

SELECT default_character_set_name
FROM information_schema.SCHEMATA S
WHERE schema_name = "dbname";

3. To check the character set for a specific table (replace dbname and tablename):

SELECT CCSA.character_set_name
FROM information_schema.`TABLES` T,
     information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
  AND T.table_schema = "dbname"
  AND T.table_name = "tablename";

4. To exit MySQL:

\q

Converting the character encoding set to UTF-8

Warning: Back up your database before starting!

1. Log in via SSH.
2. Create a .my.cnf file for login credentials:

nano .my.cnf

Add the following, replacing username and password:

[client]
user=username
password="password"

Press Ctrl+X, then y to save, and Enter.

3. Convert all tables in the database to UTF-8:

mysql --database=dbname -B -N -e "SHOW TABLES" | awk '{print "SET foreign_key_checks = 0; ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; SET foreign_key_checks = 1; "}' | mysql --database=dbname

4. Start MySQL:

mysql

5. Convert the database itself to UTF-8:

ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;

6. Exit MySQL:

\q

7. Remove the .my.cnf file:

rm .my.cnf

8. Verify the character set is now UTF-8 by repeating the steps in the Determining the current character encoding set section.

More information

• UTF-8 and Unicode