Table of Contents for

MySQL in a Nutshell, 2nd Edition

Use this statement to list all of the collation character sets. You can use the LIKE clause and the wildcard characters (% 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. Here is an example:

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 SHOW CHARACTER SET, we can see information on this character set. Looking at the Default just shown (and the Default collation shown next), we can see that greek_general_ci is the default collation for the character set greek. This is indicated with the Yes value. The field Compiled in the results shown previously indicates 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 | 
+---------+------------------+-------------------+--------+