This article explains how to convert the character set of a MySQL database to UTF-8 encoding (commonly known as Unicode). Many alphabets and characters from various languages are supported by the UTF-8 character encoding set.
Despite the fact that MySQL supports the UTF-8 character encoding set, it is rarely chosen as the default character set when creating databases and tables. As a result, many databases utilize the Latin character set, which, depending on the application, can be limited.
Determining the current character encoding set
To determine which character encoding set a MySQL database or table is currently using:
- Log in to your Hostginger Hosting SSH account.
- At the command line, type the following command, replacing username with your username:
mysql -u username -p
- At the Enter Password prompt, type your password. When you type the correct password, the mysql> prompt appears.
-
To display the current character encoding set for a particular database, type the following command at the mysql> prompt. Replace dbname with the database name:
SELECT default_character_set_name FROM information_schema.SCHEMATA S WHERE schema_name = "dbname";
-
To display the current character encoding set for a particular table in a database, type the following command at the mysql> prompt. Replace dbname with the database name, and tablename with the name of the table:
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";
- To exit the mysql program, type \q at the mysql> prompt.
Converting the character encoding set to UTF-8
- Log in to your Hostginger Hosting SSH account.
- Create a text file named .my.cnf. To do this, you can use a text editor such as Vim or Nano. This procedure shows how to use Nano. At the command line, type the following command:
nano .my.cnf
-
Add the following lines to the file, replacing username with your username and password with your password (make sure the password is enclosed in quotation marks):
[client] user=username password="password"
- When the edits are complete, press Ctrl+X, type y to save the file, and then press Enter.
-
To change the character set encoding to UTF-8 for all of the tables in the specified database, type the following command at the command line. Replace dbname with the database name:
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
-
After the command finishes, type the following command to start the mysql program:
mysql
-
To change the character set encoding to UTF-8 for the database itself, type the following command at the mysql> prompt. Replace dbname with the database name:
ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;
- To exit the mysql program, type \q at the mysql> prompt.
-
To delete the .my.cnf file, type the following command at the command line:
rm .my.cnf
- To verify that the character set encoding is now set to UTF-8, follow the steps mentioned above.