If you need to find the collations that are available in your MariaDB installation, check out the following three methods.

The SHOW COLLATIONS Statement

The SHOW COLLATIONS statement is a quick and easy way to return all the collations available in MariaDB. You can use this by itself to return all collations, or filter the results with a LIKE and/or WHERE clause.

Example:

SHOW COLLATION LIKE 'latin7%';

Result:

+-------------------------+---------+------+---------+----------+---------+
| Collation               | Charset | Id   | Default | Compiled | Sortlen |
+-------------------------+---------+------+---------+----------+---------+
| 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 |
+-------------------------+---------+------+---------+----------+---------+

In this case, I narrowed the results to just those collations that start with latin7.

Notice that we can include the LIKE clause immediately after the SHOW COLLATION text. When we do this, it indicates which collation names to match.

Here’s another example where I use both the WHERE clause and the LIKE clause:

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

The Information Schema COLLATIONS Table

The information_schema.COLLATIONS table contains a full list of supported collations in MariaDB. This is the same list that SHOW COLLATIONS returns.

Therefore, we can query this table to return the collations we want to return. We can also narrow the columns to just those that we’re interested in.

Example:

SELECT * 
FROM information_schema.COLLATIONS
WHERE COLLATION_NAME LIKE 'utf%'
AND SORTLEN = '4';

Result:

+---------------------+--------------------+-----+------------+-------------+---------+
| COLLATION_NAME      | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_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 |
+---------------------+--------------------+-----+------------+-------------+---------+

As you can see, it returns the same columns that the SHOW COLLATIONS statement returns.

Given this uses the standard SQL SELECT statement, we can customise our results in a large number of ways. We can also reduce the columns returned, join the table with other tables, etc.

Here’s an example of reducing the columns returned:

SELECT 
    COLLATION_NAME, 
    CHARACTER_SET_NAME 
FROM information_schema.COLLATIONS
WHERE COLLATION_NAME LIKE 'utf%'
AND SORTLEN = '4';

Result:

+---------------------+--------------------+
| COLLATION_NAME      | CHARACTER_SET_NAME |
+---------------------+--------------------+
| utf8_thai_520_w2    | utf8               |
| utf8mb4_thai_520_w2 | utf8mb4            |
| utf16_thai_520_w2   | utf16              |
| utf32_thai_520_w2   | utf32              |
+---------------------+--------------------+

The Information Schema COLLATION_CHARACTER_SET_APPLICABILITY Table

The information_schema.COLLATION_CHARACTER_SET_APPLICABILITY table contains a subset of the information_schema.COLLATIONS table. It contains the same collations and character sets, but that’s all. By that I mean it just contains less columns.

Example:

SELECT * 
FROM information_schema.COLLATION_CHARACTER_SET_APPLICABILITY
LIMIT 10;

Result:

+-----------------------+--------------------+
| COLLATION_NAME        | CHARACTER_SET_NAME |
+-----------------------+--------------------+
| big5_chinese_ci       | big5               |
| big5_bin              | big5               |
| big5_chinese_nopad_ci | big5               |
| big5_nopad_bin        | big5               |
| dec8_swedish_ci       | dec8               |
| dec8_bin              | dec8               |
| dec8_swedish_nopad_ci | dec8               |
| dec8_nopad_bin        | dec8               |
| cp850_general_ci      | cp850              |
| cp850_bin             | cp850              |
+-----------------------+--------------------+

In this case I used the LIMIT clause to limit the rows returned to just 10. Otherwise it would have returned 322 rows.