How to Show the Collation of a Database in MySQL

Posted on by Ian

This article provides three ways to return the collation of a database in MySQL.

The following statement can be used to check the default character set and collation for a given database:

USE Music;
SELECT @@character_set_database, @@collation_database;

Example result:

+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8                     | utf8_general_ci      |
+--------------------------+----------------------+

This example shows the collation for a database called Music. First, we switch to that database, then we do the SELECT statement to return system variables for the character set and the collation.

The character_set_database and collation_database system variables contain the character set and collation of the default database. If there is no default database, the variables have the same value as the corresponding server-level system variables, character_set_server and collation_server.

Querying the information_schema.schemata Table

Another way of getting the database collation is to query the information_schema.schemata table. This eliminates the need to change the default database (like in the previous statement):

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

Example result:

+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| utf8                       | utf8_general_ci        |
+----------------------------+------------------------+

Using the SHOW VARIABLES Statement

Another way to retrieve the collation_database system variable is to use the SHOW VARIABLES statement to return various collation-related system variables. The easiest way to do this is to use the LIKE clause to narrow it down to only variables that begin with collation. Like this:

SHOW VARIABLES LIKE 'collation%';

This returns the collation for the server, connection, and database. Like this:

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

Also see: