Here are four ways to return the database collation in MariaDB.

The @@collation_database Variable

Example:

USE PetHouse; 
SELECT @@character_set_database, @@collation_database;

Example result:

+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4                  | utf8mb4_general_ci   |
+--------------------------+----------------------+

In this example, I also returned the @@character_set_database variable.

Here’s what I get if I switch to a different database:

USE test; 
SELECT @@character_set_database, @@collation_database;

Example result:

+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| latin1                   | latin1_swedish_ci    |
+--------------------------+----------------------+

The information_schema.schemata Table

Another way to get the collation for a database is by querying the information_schema.schemata table. This includes the default character set and default collation name for the database.

Example:

SELECT 
    default_character_set_name, 
    default_collation_name 
FROM information_schema.schemata 
WHERE schema_name = 'PetHouse';

Example result:

+----------------------------+------------------------+
| default_character_set_name | default_collation_name |
+----------------------------+------------------------+
| utf8mb4                    | utf8mb4_general_ci     |
+----------------------------+------------------------+

The SHOW VARIABLES Statement

The SHOW VARIABLES statement is a quick and easy way to return system variables. You can use a LIKE or WHERE clause to narrow the variables to just those you’re interested in.

Example:

SHOW VARIABLES WHERE Variable_name = 'collation_database';

Example result:

+--------------------+--------------------+
| Variable_name      | Value              |
+--------------------+--------------------+
| collation_database | utf8mb4_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 |
+----------------------+--------------------+

The mariadb-admin Utility (aka mysqladmin)

It’s also possible to get the database 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_database variable.

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

mariadb-admin variables | grep collation_database

Result:

| collation_database      | utf8mb4_general_ci

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

Like this:

mysqladmin variables | grep collation_database

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.