Change MySQL Database Character Encoding

Here we will cover how to change the character encoding and collation of databases, as well as changing the character encoding and collation of tables. These can be changed through phpMyAdmin or from the command line.

Changing the character encoding of a MySQL database is necessary if characters are used that are not included in the standard character set.

These methods can be used to change the character encoding to utf8, utf8_bin, utf8_general_ci, or any other collation type.

As always, it is strongly recommended to back up the database before making database changes like this.

Related Articles

Create Database Using MySQL Database Wizard
Import MySQL Database Using phpMyAdmin

Change Database Character Encoding

1. phpMyAdmin Method
   CAUTION: It is recommended to back up the database before making changes. Backing up a database can be done using phpMyAdmin or through SSH.
   1. Log into cPanel
   2. Navigate to phpMyAdmin, located in the Database section
   3. To update an entire database's collation setting
      1. Select your database from the list on the left
         TIP: If you aren't sure which database the site is using, check the configuration file in the document root of the website.
      2. Click on Operations from the top set of tabs:
         
      3. In the Collation box, choose your new collation from the dropdown menu
         TIP: utf8_general_ci is the most common utf8 collation.
         
      4. Click Go
         TIP: In newer versions of phpMyAdmin, the Go button may be in the bottom-right of the Collation box.
         
   4. To update just one database table
      1. Select the table from the list on the left
         
      2. Click on Operations from the top set of tabs
         
      3. In Table options, under Collation, choose your new collation from the dropdown menu
         
      4. Click Go on the bottom right of the Table options box
         
2. Command Line Method
   CAUTION: It is recommended to back up the database before making changes. Backing up a database can be done using phpMyAdmin or through SSH.
   IMPORTANT: This solution will require root access. If this needs to be obtained, follow the guide on requesting root access.
   1. SSH into the appropriate server
   2. Log into MySQL
      mysql -u admin -p`cat /etc/psa/.psa.shadow`
   3. Enter the 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 one table:
      ALTER TABLE tablename CHARACTER SET utf8 COLLATE utf8_general_ci;
      NOTE: For either of these examples, please replace the example character set and collation with your desired values.
   6. For a list of the available character sets, when logged into mysql, use show character set;
      1. EXAMPLE:
         

         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 |
         +----------+-----------------------------+---------------------+--------+

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