In MariaDB, collation can be applied at many levels. Collation can be applied at the server level, the connection level, the database level, the table level, and even at the column level.
It’s also possible to specify a collation at the query level, so that it will override any collation that has been applied at the database, table, or column levels.
MariaDB provides a number of options for returning the collation at the various levels.
Show the Connection, Server, and Database Collation
We can use the SHOW VARIABLES administrative statement with the LIKE clause to return variables that include a certain string within their names.
Example:
SHOW VARIABLES LIKE 'collation%';Example result:
+----------------------+--------------------+ | Variable_name | Value | +----------------------+--------------------+ | collation_connection | utf8_general_ci | | collation_database | utf8mb4_general_ci | | collation_server | utf8mb4_general_ci | +----------------------+--------------------+
These are the results that I get in my test environment. The result you get will depend on your settings.
Each of these separate variables can be returned separately if required (see below).
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 | +----------------------+--------------------+
Server Collation
The following command returns the server’s default collation:
SELECT @@collation_server;Example result:
+--------------------+ | @@collation_server | +--------------------+ | utf8mb4_general_ci | +--------------------+
Connection-Level Collation
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.
The following singles out the connection collation (you can use the same syntax for any of the system variables):
SELECT @@collation_connection;Example result:
+------------------------+ | @@collation_connection | +------------------------+ | utf8_general_ci | +------------------------+
You can also return all character set system variables with the following query:
SHOW VARIABLES LIKE 'character_set%';Result:
+--------------------------+--------------------------------------------------------+ | Variable_name | Value | +--------------------------+--------------------------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | character_sets_dir | /usr/local/Cellar/mariadb/10.5.9/share/mysql/charsets/ | +--------------------------+--------------------------------------------------------+
Database-Level Collation
The following statement can be used to check the collation of a given database:
USE PetHouse;
SELECT @@character_set_database, @@collation_database;Example result:
+--------------------------+----------------------+ | @@character_set_database | @@collation_database | +--------------------------+----------------------+ | utf8mb4 | utf8mb4_general_ci | +--------------------------+----------------------+
Another way of doing it, is to use a statement like this:
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 | +----------------------------+------------------------+
Using this second method allows you to get the results without switching the database.
Here it is using a different database:
SELECT
default_character_set_name,
default_collation_name
FROM information_schema.schemata
WHERE schema_name = 'test';Example result:
+----------------------------+------------------------+ | default_character_set_name | default_collation_name | +----------------------------+------------------------+ | latin1 | latin1_swedish_ci | +----------------------------+------------------------+
This is the test database that was created when I first installed MariaDB.
Table-Level Collation
The following statement returns a number of columns that provide information about any matching table/s. One of these columns is called Collation, and it provides the collation of all matching tables.
SHOW TABLE STATUS LIKE '%Pets%';Example result (using vertical output):
*************************** 1. row ***************************
Name: Pets
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 8
Avg_row_length: 2048
Data_length: 16384
Max_data_length: 0
Index_length: 32768
Data_free: 0
Auto_increment: NULL
Create_time: 2021-03-30 09:10:38
Update_time: 2021-03-30 09:16:39
Check_time: NULL
Collation: utf8mb4_general_ci
Checksum: NULL
Create_options:
Comment:
Max_index_length: 0
Temporary: N
In this case, I got information about a table called Pets. We can see that the Collation column contains utf8mb4_general_ci, which is the collation of the table.
This statement also accepts other clauses, such as FROM, WHERE, and IN, so this gives you some options when building your statement.
Another way to get collation information about your tables is to run a query against information_schema.tables:
SELECT
table_schema,
table_name,
table_collation
FROM information_schema.tables
WHERE table_schema = 'PetHouse';Example result:
+--------------+---------------+--------------------+ | table_schema | table_name | table_collation | +--------------+---------------+--------------------+ | pethouse | vpettypecount | NULL | | pethouse | Pets | utf8mb4_general_ci | | pethouse | Owners | utf8mb4_general_ci | | pethouse | PetTypes | utf8mb4_general_ci | +--------------+---------------+--------------------+
Column-Level Collation
Running the following query returns information about each column in the Pets table. This includes the collation information.
SHOW FULL COLUMNS FROM Pets;Example result:
+-----------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +-----------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+ | PetId | int(11) | NULL | NO | PRI | NULL | | select,insert,update,references | | | PetTypeId | int(11) | NULL | NO | MUL | NULL | | select,insert,update,references | | | OwnerId | int(11) | NULL | NO | MUL | NULL | | select,insert,update,references | | | PetName | varchar(60) | utf8mb4_general_ci | NO | | NULL | | select,insert,update,references | | | DOB | date | NULL | YES | | NULL | | select,insert,update,references | | +-----------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
Alternatively, we can query information_schema.columns. In this case, we can choose just the columns we’re interested in:
SELECT
column_name,
character_set_name,
collation_name
FROM information_schema.columns
WHERE table_name = 'Pets';Example result:
+-------------+--------------------+--------------------+ | column_name | character_set_name | collation_name | +-------------+--------------------+--------------------+ | PetId | NULL | NULL | | PetTypeId | NULL | NULL | | OwnerId | NULL | NULL | | PetName | utf8mb4 | utf8mb4_general_ci | | DOB | NULL | NULL | +-------------+--------------------+--------------------+
And another option we have in our toolkit is to check out the CREATE TABLE statement of our table.
Like this:
SHOW CREATE TABLE Pets;Result:
+-------+------------------------------+ | Table | Create Table | +-------+------------------------------+ | Pets | CREATE TABLE `Pets` ( `PetId` int(11) NOT NULL, `PetTypeId` int(11) NOT NULL, `OwnerId` int(11) NOT NULL, `PetName` varchar(60) NOT NULL, `DOB` date DEFAULT NULL, PRIMARY KEY (`PetId`), KEY `PetTypeId` (`PetTypeId`), KEY `OwnerId` (`OwnerId`), CONSTRAINT `pets_ibfk_1` FOREIGN KEY (`PetTypeId`) REFERENCES `PetTypes` (`PetTypeId`), CONSTRAINT `pets_ibfk_2` FOREIGN KEY (`OwnerId`) REFERENCES `Owners` (`OwnerId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | +-------+-------------------------------+
However, the character set and collation information is only returned if they differ from the table’s default collation. In this example, the collation information did not differ, and so no collation information was returned.
Let’s change the collation:
ALTER TABLE Pets
MODIFY PetName VARCHAR(255)
CHARACTER SET utf8 COLLATE utf8_spanish_ci NOT NULL;And run SHOW CREATE TABLE again:
SHOW CREATE TABLE Pets;Result:
+-------+------------------------------+ | Table | Create Table | +-------+------------------------------+ | Pets | CREATE TABLE `Pets` ( `PetId` int(11) NOT NULL, `PetTypeId` int(11) NOT NULL, `OwnerId` int(11) NOT NULL, `PetName` varchar(255) CHARACTER SET utf8 COLLATE utf8_spanish_ci NOT NULL, `DOB` date DEFAULT NULL, PRIMARY KEY (`PetId`), KEY `PetTypeId` (`PetTypeId`), KEY `OwnerId` (`OwnerId`), CONSTRAINT `pets_ibfk_1` FOREIGN KEY (`PetTypeId`) REFERENCES `PetTypes` (`PetTypeId`), CONSTRAINT `pets_ibfk_2` FOREIGN KEY (`OwnerId`) REFERENCES `Owners` (`OwnerId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | +-------+-------------------------------+
This time we can see the new character set and collation settings against the PetName column.