Character set of the file phpmyadmin

Unfortunately, phpMyAdmin is one of the first php application that talk to MySQL about charset correctly. Your problem is most likely due to the fact that the database does not store the correct UTF-8 strings at first place.

In order to correctly display the characters correctly in phpMyAdmin, the data must be correctly stored in the database. However, convert the database into correct charset often breaks web apps that does not aware charset-related feature provided by MySQL.

May I ask: is MySQL > version 4.1? What web app is the database for? phpBB? Was the database migrated from an older version of the web app, or an older version of MySQL?

My suggestion is not to brother if the web app you are using is too old and not supported. Only convert database to real UTF-8 if you are sure the web app can read them correctly.

Edit:

Your MySQL is > 4.1, that means it's charset-aware. What's the charset collation settings for you database? I am pretty sure you are using latin1, which is MySQL name for ASCII, to store the UTF-8 text in 'bytes', into the database.

For charset-insensitive clients (i.e. mysql-cli and php-mod-mysql), characters get displayed correctly since they are being transfer to/from database as bytes. In phpMyAdmin, bytes get read and displayed as ASCII characters, that's the garbage text you seem.

Countless hours had been spend years ago (2005?) when MySQL 4.0 went obsolete, in many parts of Asia. There is a standard way to deal with your problem and gobbled data:

1. Back up your database as .sql
2. Open it up in UTF-8 capable text editor, make sure they look correct.
3. Look for charset collation latin1_general_ci, replace latin1 to utf8.
4. Save as a new sql file, do not overwrite your backup
5. Import the new file, they will now look correctly in phpMyAdmin, and Japanese on your web app will become question marks. That's normal.
6. For your php web app that rely on php-mod-mysql, insert mysql_query("SET NAMES UTF8"); after mysql_connect(), now the question marks will be gone.

7. Add the following configuration my.ini for mysql-cli:

   # CLIENT SECTION
   [mysql]
   default-character-set=utf8
   # SERVER SECTION
   [mysqld]
   default-character-set=utf8
   

For more information about charset on MySQL, please refer to manual: http://dev.mysql.com/doc/refman/5.0/en/charset-server.html

Note that I assume your web app is using php-mod-mysql to connect to the database (hence the mysql_connect() function), since php-mod-mysql is the only extension I can think of that still trigger the problem TO THIS DAY.

phpMyAdmin use php-mod-mysqli to connect to MySQL. I never learned how to use it because switch to frameworks* to develop my php projects. I strongly encourage you do that too.

• Many frameworks, e.g. CodeIgniter, Zend, use mysqli or pdo to connect to databases. mod-mysql functions are considered obsolete cause performance and scalability issue. Also, you do not want to tie your project to a specific type of database.

Overview

The default character set for MySQL at (mt) Media Temple is latin1, with a default collation of latin1_swedish_ci. This is a common type of encoding for Latin characters. You can also change the encoding. utf8 is a common character set for non-Latin characters.

NOTE:
Please note that changing your database collation is not covered by our statement of support. These resources were provided as a courtesy to assist you to the extent of our abilities. For more information on our statement of support, feel free to click here.

Advanced Support can help!
Need further assistance with your server? Help is available via Advanced Support, our premium services division. For more information on what Advanced Support can do for you, feel free to click here.

Changing database character set and collation

You can change the character set and collation of your databases and tables through phpMyAdmin or from the command line. You may want to back up your database before making changes. Please see this article for instructions: Export and import MySQL databases.

phpMyAdmin

1. Log into phpMyAdmin.
2. Select your database from the list on the left.
3. Click on "Operations" from the top set of tabs.
4. In the Collation box, choose your new collation from the dropdown menu. utf8_general_ci is the most common utf8 collation.
5. Select your database from the list on the left.
6. Click on "Operations" from the top set of tabs:

7. In the Collation box, choose your new collation from the dropdown menu. utf8_general_ci is the most common utf8 collation.
   

8. Click Go.
9. To update a database table, select it from the list on the left.

10. Click on "Operations" from the top set of tabs.

11. In "Table options," under "Collation," choose your new collation from the dropdown menu.

12. Click Go.

Command Line

1. Log into MySQL with SSH:

   mysql -h internal-db.s00000.gridserver.com -u db00000 -p

2. Log into MySQL with SSH:

   mysql -u admin -p`cat /etc/psa/.psa.shadow`

3. Enter your database password when prompted.
4. Run the following command to change the character set and collation of your database:

   ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;

5. Run the following command to change the character set and collation of your table:

   ALTER TABLE tablename CHARACTER SET utf8 COLLATE utf8_general_ci;

   For either of these examples, please replace the example character set and collation with your desired values.

For more information about database collation, please read the MySQL documentation:

http://dev.mysql.com/doc/refman/5.0/en/charset-database.html

Available character sets

The following character sets are available:

mysql> show character set;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
| cp850    | DOS West European           | cp850_general_ci    |      1 |
| hp8      | HP West European            | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                    | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |
| cp1250   | Windows Central European    | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode               | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                 | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 |
| macce    | Mac Central European        | macce_general_ci    |      1 |
| macroman | Mac West European           | macroman_general_ci |      1 |
| cp852    | DOS Central European        | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 |
| cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 |
| cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic              | cp1257_general_ci   |      1 |
| binary   | Binary pseudo charset       | binary              |      1 |
| geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |
+----------+-----------------------------+---------------------+--------+

How do I change the character set in phpMyAdmin?

What is character set in MySQL?

How do I find the character set of a MySQL database?

What is character set utf8mb4?