When you run a query against a MySQL database, MySQL 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 MySQL can translate it into an appropriate character set and collation.

When sending the query results back to the client, MySQL can translate these results back to a different character set altogether if required. MySQL uses system variables to determine which character sets and collations to use at each of these steps.

The following singles out the connection collation:

SELECT @@collation_connection;

Example result:

+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_0900_ai_ci     |
+------------------------+

Continue reading →

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.
Continue reading →

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.
Continue reading →

This page contains three ways of returning the collation of a column in MySQL.

Running the following query is the quickest way to return the collation of a column. In particular, it returns information about each column in a given table. This includes the collation information.

SHOW FULL COLUMNS FROM Artists;

That results in a lot of columns being returned with all sorts of information about the column, including the collation. To reduce the number of columns returned, see below.
Continue reading →

Here are two ways to return the collation of a table in MySQL.

The quickest way to return the collation of a given table in MySQL is to run the following statement:

SHOW TABLE STATUS LIKE '%Artists%';

Running this statement will return a whole bunch 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.

Of course, you’ll need to replace %Artists% with your own table name. And you can omit the percentage signs if you don’t think they’re needed. This statement also accepts other clauses, such as FROM, WHERE, and IN, so this gives you some options when building your statement.
Continue reading →

In MySQL, collation can be applied at many levels. It can be applied at the server level, the connection level, the database level, the table level, and even at the column level. You can also specify a collation in your queries that will override any collation that has been applied at the database, table, or column levels.

Here’s how to find out what collation is being applied at each of these levels.

Shortcut for Connection, Server, and Database Collation

The quickest way to get collation information for the connection, server, and database is to use the following statement. This statement returns all system variables starting with collation:

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 |
+----------------------+--------------------+

You can also return each of these system variables separately if required. See below for instructions on how to do that.
Continue reading →