You can specify the character set and collation at various levels in MariaDB. You can specify them at the connection level, the server level, the database level, the table level, and the column level.

You can also specify a collation in your queries so that it overrides any collation that has been previously specified at the aforementioned levels.

To set the character set and collation at the database level, use the CREATE DATABASE statement or ALTER DATABASE statement (depending on whether you’re creating the database or modifying it).

When Creating the Database

Here’s an example of setting the collation and character set when creating the database:

CREATE DATABASE MyDB
CHARACTER SET latin1 
COLLATE latin1_german1_ci;

That creates a database called MyDB with a latin1 character set, and a collation of latin1_german1_ci.

There are several ways to check the database’s collation and character set in MariaDB. Here’s one:

USE MyDB; 
SELECT @@character_set_database, @@collation_database;

Result:

+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| latin1                   | latin1_german1_ci    |
+--------------------------+----------------------+

We can see that the database uses the collation and character set that we specified when creating it.

Alter an Existing Database

Here’s an example of changing the collation and character set on an existing database:

ALTER DATABASE MyDB 
CHARACTER SET utf8 
COLLATE utf8_spanish_ci;

Let’s check the database again:

USE MyDB; 
SELECT @@character_set_database, @@collation_database;

Result:

+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8                     | utf8_spanish_ci      |
+--------------------------+----------------------+

It now uses the new collation and character set.

Checking the Collation and Character Sets in MariaDB

There are various ways to check the collation and character sets in MariaDB, depending on the level that you’re checking (server, database, table, column, connection, etc). See 4 Ways to Get the Database Collation and Show the Collation in MariaDB for examples.