Running the following statement lists all collations supported by the server in MySQL:
SHOW COLLATION;
However, the resulting list is quite long, and if you have a collation in mind, you can always filter the list with either the LIKE clause or the WHERE clause.
Here’s an example using the LIKE clause:
SHOW COLLATION LIKE 'latin%';
This returns the following result in MySQL 8.0.11:
| Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
|---|---|---|---|---|---|---|
| latin1_bin | latin1 | 47 | Yes | 1 | PAD SPACE | |
| latin1_danish_ci | latin1 | 15 | Yes | 1 | PAD SPACE | |
| latin1_general_ci | latin1 | 48 | Yes | 1 | PAD SPACE | |
| latin1_general_cs | latin1 | 49 | Yes | 1 | PAD SPACE | |
| latin1_german1_ci | latin1 | 5 | Yes | 1 | PAD SPACE | |
| latin1_german2_ci | latin1 | 31 | Yes | 2 | PAD SPACE | |
| latin1_spanish_ci | latin1 | 94 | Yes | 1 | PAD SPACE | |
| latin1_swedish_ci | latin1 | 8 | Yes | Yes | 1 | PAD SPACE |
| latin2_bin | latin2 | 77 | Yes | 1 | PAD SPACE | |
| latin2_croatian_ci | latin2 | 27 | Yes | 1 | PAD SPACE | |
| latin2_czech_cs | latin2 | 2 | Yes | 4 | PAD SPACE | |
| latin2_general_ci | latin2 | 9 | Yes | Yes | 1 | PAD SPACE |
| latin2_hungarian_ci | latin2 | 21 | Yes | 1 | PAD SPACE | |
| latin5_bin | latin5 | 78 | Yes | 1 | PAD SPACE | |
| latin5_turkish_ci | latin5 | 30 | Yes | Yes | 1 | PAD SPACE |
| latin7_bin | latin7 | 79 | Yes | 1 | PAD SPACE | |
| latin7_estonian_cs | latin7 | 20 | Yes | 1 | PAD SPACE | |
| latin7_general_ci | latin7 | 41 | Yes | Yes | 1 | PAD SPACE |
| latin7_general_cs | latin7 | 42 | Yes | 1 | PAD SPACE |
You can also use the WHERE clause to provide an expression.
Example:
SHOW COLLATION WHERE Charset = 'hebrew';
Result:
| Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
|---|---|---|---|---|---|---|
| hebrew_bin | hebrew | 71 | Yes | 1 | PAD SPACE | |
| hebrew_general_ci | hebrew | 16 | Yes | Yes | 1 | PAD SPACE |
You can also use SHOW CHARACTER SET to return a list of available character sets.