Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

MySQL 5.5 Reference Manual  /  ...  /  Character Sets and Collations in MySQL

10.1.2 Character Sets and Collations in MySQL

MySQL Server supports multiple character sets. To list the available character sets, use the INFORMATION_SCHEMA CHARACTER_SETS table or the SHOW CHARACTER SET statement. A partial listing follows. For more complete information, see Section 10.1.14, “Character Sets and Collations Supported by MySQL”.

mysql> SHOW CHARACTER SET;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
...
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
...
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
...
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
...

A given character set always has at least one collation, and most character sets have several. To list the available collations for a character set, use the INFORMATION_SCHEMA COLLATIONS table or the SHOW COLLATION statement. For example, to see the collations for the latin1 (cp1252 West European) character set, use this statement:

mysql> SHOW COLLATION WHERE Charset = 'latin1';
+-------------------+---------+----+---------+----------+---------+
| Collation         | Charset | Id | Default | Compiled | Sortlen |
+-------------------+---------+----+---------+----------+---------+
| latin1_german1_ci | latin1  |  5 |         | Yes      |       1 |
| latin1_swedish_ci | latin1  |  8 | Yes     | Yes      |       1 |
| latin1_danish_ci  | latin1  | 15 |         | Yes      |       1 |
| latin1_german2_ci | latin1  | 31 |         | Yes      |       2 |
| latin1_bin        | latin1  | 47 |         | Yes      |       1 |
| latin1_general_ci | latin1  | 48 |         | Yes      |       1 |
| latin1_general_cs | latin1  | 49 |         | Yes      |       1 |
| latin1_spanish_ci | latin1  | 94 |         | Yes      |       1 |
+-------------------+---------+----+---------+----------+---------+

The latin1 collations have the following meanings.

CollationMeaning
latin1_binBinary according to latin1 encoding
latin1_danish_ciDanish/Norwegian
latin1_general_ciMultilingual (Western European)
latin1_general_csMultilingual (ISO Western European), case sensitive
latin1_german1_ciGerman DIN-1 (dictionary order)
latin1_german2_ciGerman DIN-2 (phone book order)
latin1_spanish_ciModern Spanish
latin1_swedish_ciSwedish/Finnish

Collations have these general characteristics:

  • Two different character sets cannot have the same collation.

  • Each character set has one collation that is the default collation. For example, the default collations for latin1 and utf8 are latin1_swedish_ci and utf8_general_ci, respectively. The INFORMATION_SCHEMA CHARACTER_SETS table and the SHOW CHARACTER SET statement indicate the default collation for each character set. The INFORMATION_SCHEMA COLLATIONS table and the SHOW COLLATION statement have a column that indicates for each collation whether it is the default for its character set (Yes if so, empty if not).

  • Collation names start with the name of the character set with which they are associated, followed by one or more suffixes indicating other collation characteristics. For additional information about naming conventions, see Section 10.1.3, “Collation Naming Conventions”.

When a character set has multiple collations, it might not be clear which collation is most suitable for a given application. To avoid choosing an inappropriate collation, perform some comparisons with representative data values to make sure that a given collation sorts values the way you expect.

Collation-Charts.Org is a useful site for information that shows how one collation compares to another.


User Comments
Sign Up Login You must be logged in to post a comment.