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 | +--------------------+---------+-----+---------+----------+---------+---------------+