Support / KnowledgeBase

 
Search the KnowledgeBase Search

Default MySQL character set and collation

  • Applies to: All DV, Grid

  • Difficulty: Easy

  • Time needed: 5 minutes

  • Tools needed: None

 
  • Applies to: Grid
    • Difficulty: Easy
    • Time Needed: 10
    • Tools Required: None
  • Applies to: All DV
    • Difficulty: Easy
    • Time Needed: 10
    • Tools Required: None

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.

READ ME FIRST

Changing your database collation is not supported by (mt) Media Temple. However, we have provided instructions here for your convenience.

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

 

Continue