SHOW COLLATION

Use this MySQL statement to list all of the collation character sets. This is related to SHOW CHARACTER SET in that collation is the ordering (i.e., alphabetizing) of the characters of the character set.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

SHOW COLLATION [LIKE 'pattern'| WHERE expression]

Explanation

Use this statement to list all of the collation character sets. You can use the LIKE clause and the wildcard characters (i.e., % and _) to list character sets based on a naming pattern. Or you may use the WHERE clause to refine the results set. This statement is available as of version 4.1 of MySQL.

Examples

SHOW COLLATION LIKE '%greek%';

+------------------+---------+----+---------+----------+---------+
| Collation        | Charset | Id | Default | Compiled | Sortlen |
+------------------+---------+----+---------+----------+---------+
| greek_general_ci | greek   | 25 | Yes     | Yes      |       1 | 
| greek_bin        | greek   | 70 |         | Yes      |       1 | 
+------------------+---------+----+---------+----------+---------+

In this example, character sets that contain the letters greek in their name are listed. These are Greek character sets. Under the Charset column is shown the character set for which the collation relates. Both are for the greek character set. Using the SHOW CHARACTER SET below we can see information on this character set. Looking at the Default above (and the Default collation below), we can see that greek_general_ci is the default collation for the character set greek. This is indicated with the Yes value above. The field Compiled in the results above indicate that the character set was compiled in the MySQL server. The field Sortlen indicates the bytes needed when collating data.

SHOW CHARACTER SET LIKE 'greek';

+---------+------------------+-------------------+--------+
| Charset | Description      | Default collation | Maxlen |
+---------+------------------+-------------------+--------+
| greek   | ISO 8859-7 Greek | greek_general_ci  |      1 | 
+---------+------------------+-------------------+--------+