How to Show the Server Collation in MySQL

Running the following command returns the server’s default collation.

SELECT @@collation_server;

Example result:

+--------------------+
| @@collation_server |
+--------------------+
| utf8mb4_0900_ai_ci |
+--------------------+

This returns the collation_server system variable, which contains the collation of the server. However, this isn’t the only way to do it.

Using the SHOW VARIABLES Statement

Another way to retrieve the collation_server 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 |
+----------------------+--------------------+