In MySQL we can use the SHOW COLLATION statement to return all collations supported by the server. By default it returns all available collations, but we can filter the output down to just those collations that we’re interested in.

Syntax

The syntax goes like this:

SHOW COLLATION
    [LIKE 'pattern' | WHERE expr]

We can use the LIKE clause and/or WHERE clause to reduce the output to a subset of collations that we’re interested in.

Example

Here’s how to return all collations:

SHOW COLLATION;

Partial result:

+-----------------------------+----------+-----+---------+----------+---------+---------------+
| Collation                   | Charset  | Id  | Default | Compiled | Sortlen | Pad_attribute |
+-----------------------------+----------+-----+---------+----------+---------+---------------+
| armscii8_bin                | armscii8 |  64 |         | Yes      |       1 | PAD SPACE     |
| armscii8_general_ci         | armscii8 |  32 | Yes     | Yes      |       1 | PAD SPACE     |
| ascii_bin                   | ascii    |  65 |         | Yes      |       1 | PAD SPACE     |
| ascii_general_ci            | ascii    |  11 | Yes     | Yes      |       1 | PAD SPACE     |
...

That’s a partial result because my system returns 286 rows, which is too many to list here.

The LIKE Clause

We can use the LIKE clause to filter the results to just those collations we’re interested in.

Example:

SHOW COLLATION LIKE 'euc%';

Result:

+---------------------+---------+----+---------+----------+---------+---------------+
| Collation           | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
+---------------------+---------+----+---------+----------+---------+---------------+
| eucjpms_bin         | eucjpms | 98 |         | Yes      |       1 | PAD SPACE     |
| eucjpms_japanese_ci | eucjpms | 97 | Yes     | Yes      |       1 | PAD SPACE     |
| euckr_bin           | euckr   | 85 |         | Yes      |       1 | PAD SPACE     |
| euckr_korean_ci     | euckr   | 19 | Yes     | Yes      |       1 | PAD SPACE     |
+---------------------+---------+----+---------+----------+---------+---------------+

The WHERE Clause

We can also use the WHERE clause as another way to filter the results:

SHOW COLLATION WHERE Sortlen = 2;

Result:

+--------------------+---------+-----+---------+----------+---------+---------------+
| Collation          | Charset | Id  | Default | Compiled | Sortlen | Pad_attribute |
+--------------------+---------+-----+---------+----------+---------+---------------+
| cp1250_czech_cs    | cp1250  |  34 |         | Yes      |       2 | PAD SPACE     |
| gb18030_chinese_ci | gb18030 | 248 | Yes     | Yes      |       2 | PAD SPACE     |
| latin1_german2_ci  | latin1  |  31 |         | Yes      |       2 | PAD SPACE     |
+--------------------+---------+-----+---------+----------+---------+---------------+

The AND Keyword

We can also combine conditions with the AND keyword:

SHOW COLLATION 
WHERE Sortlen = 8 
AND Collation LIKE 'gb%';

Result:

+------------------------+---------+-----+---------+----------+---------+---------------+
| Collation              | Charset | Id  | Default | Compiled | Sortlen | Pad_attribute |
+------------------------+---------+-----+---------+----------+---------+---------------+
| gb18030_unicode_520_ci | gb18030 | 250 |         | Yes      |       8 | PAD SPACE     |
+------------------------+---------+-----+---------+----------+---------+---------------+

About the Default Column

If we want to filter by the Default column we need to surround it by backticks, due to Default being a reserved word in MySQL:

SHOW COLLATION 
WHERE Charset LIKE 'utf%'
AND `Default` = 'Yes';

Result:

+--------------------+---------+-----+---------+----------+---------+---------------+
| Collation          | Charset | Id  | Default | Compiled | Sortlen | Pad_attribute |
+--------------------+---------+-----+---------+----------+---------+---------------+
| utf16le_general_ci | utf16le |  56 | Yes     | Yes      |       1 | PAD SPACE     |
| utf16_general_ci   | utf16   |  54 | Yes     | Yes      |       1 | PAD SPACE     |
| utf32_general_ci   | utf32   |  60 | Yes     | Yes      |       1 | PAD SPACE     |
| utf8mb3_general_ci | utf8mb3 |  33 | Yes     | Yes      |       1 | PAD SPACE     |
| utf8mb4_0900_ai_ci | utf8mb4 | 255 | Yes     | Yes      |       0 | NO PAD        |
+--------------------+---------+-----+---------+----------+---------+---------------+