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.
Server-Level Collation
Running the following command returns the server’s default collation.
SELECT @@collation_server;
Example result:
+--------------------+ | @@collation_server | +--------------------+ | utf8mb4_0900_ai_ci | +--------------------+
Connection-Level Collation
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 (you can use the same syntax for any of the system variables):
SELECT @@collation_connection;
Example result:
+------------------------+ | @@collation_connection | +------------------------+ | utf8mb4_0900_ai_ci | +------------------------+
You can also return all character set system variables with the following query:
SHOW VARIABLES LIKE 'character_set%';
Example result:
+--------------------------+----------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql/share/charsets/ | +--------------------------+----------------------------------+
Database-Level Collation
The following statement can be used to check the collation of a given database:
USE Music; SELECT @@character_set_database, @@collation_database;
Example result:
+--------------------------+----------------------+ | @@character_set_database | @@collation_database | +--------------------------+----------------------+ | utf8 | utf8_general_ci | +--------------------------+----------------------+
Alternatively, you can use the following statement (which eliminates the need to change the default database):
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 | +----------------------------+------------------------+
Table-Level Collation
Running the following 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.
SHOW TABLE STATUS LIKE '%Artists%';
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.
One problem with the previous statement is that it returns a lot of columns, and you might be forced to scroll sideways to find the collation column. If you’re only interested in the collation info, you can query information_schema.tables. You can also return the collation for all tables within a given database if required. Here’s how to do that:
SELECT table_schema, table_name, table_collation FROM information_schema.tables WHERE table_schema = 'Music';
Example results:
+--------------+------------+-----------------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_COLLATION | +--------------+------------+-----------------+ | Music | Albums | utf8_general_ci | | Music | Artists | utf8_general_ci | | Music | Genres | utf8_general_ci | +--------------+------------+-----------------+
Column-Level Collation
Running the following query 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.
You can reduce the number of columns returned by doing this:
SELECT column_name, character_set_name, collation_name FROM information_schema.columns WHERE table_name = 'Artists';
Example result:
+-------------+--------------------+-----------------+ | COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME | +-------------+--------------------+-----------------+ | ArtistId | NULL | NULL | | ArtistName | utf8 | utf8_spanish_ci | | ActiveFrom | NULL | NULL | +-------------+--------------------+-----------------+
You can also run the SHOW CREATE TABLE statement to display a definition of the table (which includes its columns).
SHOW CREATE TABLE Artists;
This returns something like this:
+---------+--------------------------------+ | Table | Create Table | +---------+--------------------------------+ | Artists | CREATE TABLE `Artists` ( `ArtistId` int(11) NOT NULL AUTO_INCREMENT, `ArtistName` varchar(255) CHARACTER SET utf8 COLLATE utf8_spanish_ci NOT NULL, `ActiveFrom` datetime NOT NULL, PRIMARY KEY (`ArtistId`) ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 | +---------+--------------------------------+
However, the character set and collation information is only returned if they differ from the table’s default collation. In this example, I explicitly set the ArtistName column to a different collation just for demonstration purposes (otherwise the collation info wouldn’t have been returned).