If you need to find the character sets that are available in your MariaDB installation, check out the following two methods.

The SHOW CHARACTER SET Statement

The SHOW CHARACTER SET statement is a quick and easy way to return all the character sets that are 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 CHARACTER SET LIKE 'latin%';

Result:

+---------+-----------------------------+-------------------+--------+
| Charset | Description                 | Default collation | Maxlen |
+---------+-----------------------------+-------------------+--------+
| latin1  | cp1252 West European        | latin1_swedish_ci |      1 |
| latin2  | ISO 8859-2 Central European | latin2_general_ci |      1 |
| latin5  | ISO 8859-9 Turkish          | latin5_turkish_ci |      1 |
| latin7  | ISO 8859-13 Baltic          | latin7_general_ci |      1 |
+---------+-----------------------------+-------------------+--------+

In this case, I narrowed the results to just those character sets that start with latin.

Notice that we can include the LIKE clause immediately after the SHOW CHARACTER SET 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, as well as the OR operator:

SHOW CHARACTER SET 
WHERE Maxlen LIKE '3'
OR Description LIKE '%Japanese%';

Result:

+---------+---------------------------+---------------------+--------+
| Charset | Description               | Default collation   | Maxlen |
+---------+---------------------------+---------------------+--------+
| ujis    | EUC-JP Japanese           | ujis_japanese_ci    |      3 |
| sjis    | Shift-JIS Japanese        | sjis_japanese_ci    |      2 |
| utf8    | UTF-8 Unicode             | utf8_general_ci     |      3 |
| cp932   | SJIS for Windows Japanese | cp932_japanese_ci   |      2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci |      3 |
+---------+---------------------------+---------------------+--------+

The information_schema.CHARACTER_SETS Table

The information_schema.CHARACTER_SETS table contains a full list of supported character sets in MariaDB. This is the same list that SHOW CHARACTER_SETS returns.

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

Example:

SELECT * 
FROM information_schema.CHARACTER_SETS
WHERE CHARACTER_SET_NAME LIKE 'utf%'
AND MAXLEN = '4';

Result:

+--------------------+----------------------+------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION      | MAXLEN |
+--------------------+----------------------+------------------+--------+
| utf8mb4            | utf8mb4_general_ci   | UTF-8 Unicode    |      4 |
| utf16              | utf16_general_ci     | UTF-16 Unicode   |      4 |
| utf16le            | utf16le_general_ci   | UTF-16LE Unicode |      4 |
| utf32              | utf32_general_ci     | UTF-32 Unicode   |      4 |
+--------------------+----------------------+------------------+--------+

As you can see, it returns the same columns that the SHOW CHARACTER SETS 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 
    CHARACTER_SET_NAME,
    DESCRIPTION
FROM information_schema.CHARACTER_SETS
WHERE CHARACTER_SET_NAME LIKE 'utf%'
AND MAXLEN = '4';

Result:

+--------------------+------------------+
| CHARACTER_SET_NAME | DESCRIPTION      |
+--------------------+------------------+
| utf8mb4            | UTF-8 Unicode    |
| utf16              | UTF-16 Unicode   |
| utf16le            | UTF-16LE Unicode |
| utf32              | UTF-32 Unicode   |
+--------------------+------------------+