How SHOW COLLATION Works in MariaDB

In MariaDB, you can use the SHOW COLLATION administrative SQL statement to return all available collations in MariaDB.

Syntax

The syntax goes like this:

SHOW COLLATION [LIKE 'pattern' | WHERE expr]

There are a lot of collations, so it helps to use the LIKE and/or WHERE clauses whenever you have an idea of the subset of collations you’re looking for.

Examples

Here are some examples to demonstrate.

The LIKE Clause

Here’s an example that uses the LIKE clause to return all collations that start with latin:

SHOW COLLATION LIKE 'latin%';

Result:

+-------------------------+---------+------+---------+----------+---------+
| Collation               | Charset | Id   | Default | Compiled | Sortlen |
+-------------------------+---------+------+---------+----------+---------+
| latin1_german1_ci       | latin1  |    5 |         | Yes      |       1 |
| latin1_swedish_ci       | latin1  |    8 | Yes     | Yes      |       1 |
| latin1_danish_ci        | latin1  |   15 |         | Yes      |       1 |
| latin1_german2_ci       | latin1  |   31 |         | Yes      |       2 |
| latin1_bin              | latin1  |   47 |         | Yes      |       1 |
| latin1_general_ci       | latin1  |   48 |         | Yes      |       1 |
| latin1_general_cs       | latin1  |   49 |         | Yes      |       1 |
| latin1_spanish_ci       | latin1  |   94 |         | Yes      |       1 |
| latin1_swedish_nopad_ci | latin1  | 1032 |         | Yes      |       1 |
| latin1_nopad_bin        | latin1  | 1071 |         | Yes      |       1 |
| latin2_czech_cs         | latin2  |    2 |         | Yes      |       4 |
| latin2_general_ci       | latin2  |    9 | Yes     | Yes      |       1 |
| latin2_hungarian_ci     | latin2  |   21 |         | Yes      |       1 |
| latin2_croatian_ci      | latin2  |   27 |         | Yes      |       1 |
| latin2_bin              | latin2  |   77 |         | Yes      |       1 |
| latin2_general_nopad_ci | latin2  | 1033 |         | Yes      |       1 |
| latin2_nopad_bin        | latin2  | 1101 |         | Yes      |       1 |
| latin5_turkish_ci       | latin5  |   30 | Yes     | Yes      |       1 |
| latin5_bin              | latin5  |   78 |         | Yes      |       1 |
| latin5_turkish_nopad_ci | latin5  | 1054 |         | Yes      |       1 |
| latin5_nopad_bin        | latin5  | 1102 |         | Yes      |       1 |
| latin7_estonian_cs      | latin7  |   20 |         | Yes      |       1 |
| latin7_general_ci       | latin7  |   41 | Yes     | Yes      |       1 |
| latin7_general_cs       | latin7  |   42 |         | Yes      |       1 |
| latin7_bin              | latin7  |   79 |         | Yes      |       1 |
| latin7_general_nopad_ci | latin7  | 1065 |         | Yes      |       1 |
| latin7_nopad_bin        | latin7  | 1103 |         | Yes      |       1 |
+-------------------------+---------+------+---------+----------+---------+

The WHERE Clause

Here’s an example that uses the WHERE clause to return all collations with a Sortlen of 2:

SHOW COLLATION WHERE Sortlen LIKE '2';

Result:

+-------------------+---------+----+---------+----------+---------+
| Collation         | Charset | Id | Default | Compiled | Sortlen |
+-------------------+---------+----+---------+----------+---------+
| latin1_german2_ci | latin1  | 31 |         | Yes      |       2 |
| cp1250_czech_cs   | cp1250  | 34 |         | Yes      |       2 |
+-------------------+---------+----+---------+----------+---------+

The AND Keyword

We can combine conditions with the AND keyword:

SHOW COLLATION 
WHERE Sortlen LIKE '4' 
AND Collation LIKE 'utf%';

Result:

+---------------------+---------+-----+---------+----------+---------+
| Collation           | Charset | Id  | Default | Compiled | Sortlen |
+---------------------+---------+-----+---------+----------+---------+
| utf8_thai_520_w2    | utf8    | 578 |         | Yes      |       4 |
| utf8mb4_thai_520_w2 | utf8mb4 | 610 |         | Yes      |       4 |
| utf16_thai_520_w2   | utf16   | 674 |         | Yes      |       4 |
| utf32_thai_520_w2   | utf32   | 738 |         | Yes      |       4 |
+---------------------+---------+-----+---------+----------+---------+

Return All Collations

To return all collations, simply remove all clauses:

SHOW COLLATION;

See Full List of Collations Supported in MariaDB for the result.