When you run a query against a MariaDB database, MariaDB uses a bunch of system variables to determine which character set and collation to use whenever queries are run. If the client uses a different character set to the server, then MariaDB can translate it into an appropriate character set and collation.

When sending the query results back to the client, MariaDB can translate these results back to a different character set altogether if required. MariaDB uses system variables to determine which character sets and collations to use at each of these steps.

Here are three ways to show the connection collation in MariaDB.

The @@collation_connection Variable

The following returns the collation for the connection (you can use the same syntax for any of the system variables):

SELECT @@collation_connection;

Example result:

+------------------------+
| @@collation_connection |
+------------------------+
| utf8_general_ci        |
+------------------------+

The SHOW VARIABLES Statement

The SHOW VARIABLES statement shows the values of MariaDB system variables. This can return a lot of data, so it’s usually best to use either a WHERE or LIKE clause to narrow it down to only those variables that you’re interested in.

Example:

SHOW VARIABLES 
WHERE Variable_name = 'collation_connection';

Example result:

+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
+----------------------+-----------------+

Alternatively, you can use the LIKE clause to return variables that begin with collation:

SHOW VARIABLES LIKE 'collation%';

Example result:

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8_general_ci    |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+

By default, SHOW VARIABLES shows the SESSION variables. In this case, it returns the values that are in effect for the current connection.

Therefore, the previous example could be rewritten as follows:

SHOW SESSION VARIABLES LIKE 'collation%';

Example result:

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8_general_ci    |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+

Alternatively, you can replace SESSION with LOCAL (which is a synonym for SESSION):

SHOW LOCAL VARIABLES LIKE 'collation%';

You can also use the GLOBAL modifier to display the values that are used for new connections to MariaDB.

Example:

SHOW GLOBAL VARIABLES LIKE 'collation%';

Example result:

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+

In this case, the GLOBAL variable for collation_connection returned a different value to its LOCAL counterpart.

The mariadb-admin Utility (aka mysqladmin)

It’s also possible to get server collation information with the mariadb-admin utility.

You can use this utility to return all variables. And you can narrow those variables down to just the ones you’re interested in – in this case, the collation_connection variable.

To return just the collation_connection variable, open a new terminal and run the following command:

mariadb-admin variables | grep collation_connection

Result:

| collation_connection      | utf8mb4_general_ci

Note that this is the same value as the GLOBAL variable returned above (and not the SESSION variable for the current connection). This is the default collation for new connections to MariaDB.

Another way to do it is replace mariadb-admin with mysqladmin.

Like this:

mysqladmin variables | grep collation_connection

You will need to use mysqladmin if you use an earlier version of MariaDB (before 10.4.6). From MariaDB 10.4.6, mariadb-admin is a symlink to mysqladmin. From MariaDB 10.5.2, mariadb-admin is the name of the script, with mysqladmin a symlink. See the MariaDB documentation for mysqladmin for more information.