This article describes how to convert a MySQL database's character set to UTF-8 encoding (also known as Unicode). The UTF-8 character encoding set supports many alphabets and characters for a wide variety of languages.
Although MySQL supports the UTF-8 character encoding set, it is often not used as the default character set during database and table creation. As a result, many databases use the Latin character set, which can be limiting depending upon the application.
To determine which character encoding set a MySQL database or table is currently using:
mysql -u username -p
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 databasemysql> 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 convert the character encoding set to UTF-8:
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"
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 delete the .my.cnf file, type the following command at the command line:
rm .my.cnf
For more information about UTF-8 and Unicode, please visit http://en.wikipedia.org/wiki/UTF-8.
Subscribe to receive weekly cutting edge tips, strategies, and news you need to grow your web business.
No charge. Unsubscribe anytime.
Did you find this article helpful? Then you'll love our support. Experience the A2 Hosting difference today and get a pre-secured, pre-optimized website. Check out our web hosting plans today.
We use cookies to personalize the website for you and to analyze the use of our website. You consent to this by clicking on "I consent" or by continuing your use of this website. Further information about cookies can be found in our Privacy Policy.