How to Find the Collation in MySQL

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).

How to Show the Collation of a Column in MySQL

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.

Reduce the Number of Columns Returned

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

Using SHOW CREATE TABLE

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).

How to Show the Collation of a Table in MySQL

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.

Querying the information_schema.tables Table

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

How to Show the Collation of a Database in MySQL

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

How to Show the Collation of your Connection in MySQL

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

Using the SHOW VARIABLES Statement

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

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

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

What is Collation in Databases?

In database systems, Collation specifies how data is sorted and compared in a database. Collation provides the sorting rules, case, and accent sensitivity properties for the data in the database.

For example, when you run a query using the ORDER BY clause, collation determines whether or not uppercase letters and lowercase letters are treated the same.

Collation is also used to determine how accents are treated, as well as character width and Japanese kana characters. Collation can also be used to distinguish between various ideographic variation selectors in certain collations (such as the Japanese_Bushu_Kakusu_140 and Japanese_XJIS_140 collations that were introduced in SQL Server 2017).

Different database management systems will provide different collation options. Depending on the DBMS, collation can be specified at the server level, the database level, the table level, and the column level. Collations can also be specified at the expression level (so you can specify which collation to use when you run a query), and at the identifier level.

Example

Below is a comparison between running a case-sensitive query vs a case-insensitive query in SQL Server.

Case-insensitive

USE Music;
SELECT ArtistId, ArtistName
FROM Artists
ORDER BY ArtistName COLLATE Latin1_General_CI_AI;

This query uses the ORDER BY clause to order the results, and it uses the COLLATE clause to specify the collation to be used when ordering those results. In this case, we use the Latin1_General_CI_AI collation. Note that this collation has the letters CI, which stands for case-insensitive.

The above query returns the following result:

ArtistId  ArtistName            
--------  ----------------------
1         Aardvark              
2         AArdvark              
3         aArdvark              
4         aardvark  

Case-sensitive

However, if we change the collation to case-sensitive by using the Latin1_General_CS_AIcollation, we get the following result:

ArtistId  ArtistName            
--------  ----------------------
4         aardvark              
3         aArdvark              
1         Aardvark              
2         AArdvark  

Collation Options

As mentioned, case-sensitivity is just one of various options when specifying collation. The options associated with a collation are case sensitivity, accent sensitivity, Kana-sensitivity, width sensitivity, variation-selector-sensitivity.

Collations are expressed in different ways, depending on the DBMS that you use. In SQL Server, collation options are specified by appending the options to the collation name. So for example, Latin1_General_CI_AS is case-insensitive and accent-sensitive.

Below is a quick overview of the options:

Option	Explanation
Case-sensitive (_CS)	Lowercase letters sort ahead of their uppercase versions.
Case-insensitive (_CI)	Uppercase and lowercase versions of letters are considered identical for sorting purposes.
Accent-sensitive (_AS)	Distinguishes between accented and unaccented characters. For example, a is not equal to ấ.
Accent-insensitive (_AI)	Accented and unaccented versions of letters are considered identical for sorting purposes. For example, a is equal to ấ.
Kana-sensitive (_KS)	Distinguishes between the Japanese kana characters Hiragana and Katakana. If _KS is omitted, then it is kana-insensitive, which means that Hiragana and Katakana characters are considered equal for sorting purposes.
Width-sensitive (_WS)	Distinguishes between full-width and half-width characters. If _WS is omitted, then it is width-insensitive, which means that full-width and half-width representations of the same character are considered identical for sorting purposes.
Variation-selector-sensitive (_VSS)	Distinguishes between various ideographic variation selectors in Japanese collations Japanese_Bushu_Kakusu_140 and Japanese_XJIS_140. These collations were introduced in SQL Server 2017. If _VSS is not selected, the collation is variation selector insensitive, and the variation selector is not considered in the comparison.

You should be able to see a list of available collations for your database by running a query. The syntax will depend on your database management system.  The list of available collations is usually quite large, due to the fact that each collation can have many different permutations of the above options.

For example, below is a list of the various Latin1 General collations available in SQL Server 2017:

Latin1_General_BIN	Latin1-General, binary sort
Latin1_General_BIN2	Latin1-General, binary code point comparison sort
Latin1_General_CI_AI	Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive
Latin1_General_CI_AI_WS	Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive, width-sensitive
Latin1_General_CI_AI_KS	Latin1-General, case-insensitive, accent-insensitive, kanatype-sensitive, width-insensitive
Latin1_General_CI_AI_KS_WS	Latin1-General, case-insensitive, accent-insensitive, kanatype-sensitive, width-sensitive
Latin1_General_CI_AS	Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive
Latin1_General_CI_AS_WS	Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-sensitive
Latin1_General_CI_AS_KS	Latin1-General, case-insensitive, accent-sensitive, kanatype-sensitive, width-insensitive
Latin1_General_CI_AS_KS_WS	Latin1-General, case-insensitive, accent-sensitive, kanatype-sensitive, width-sensitive
Latin1_General_CS_AI	Latin1-General, case-sensitive, accent-insensitive, kanatype-insensitive, width-insensitive
Latin1_General_CS_AI_WS	Latin1-General, case-sensitive, accent-insensitive, kanatype-insensitive, width-sensitive
Latin1_General_CS_AI_KS	Latin1-General, case-sensitive, accent-insensitive, kanatype-sensitive, width-insensitive
Latin1_General_CS_AI_KS_WS	Latin1-General, case-sensitive, accent-insensitive, kanatype-sensitive, width-sensitive
Latin1_General_CS_AS	Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive
Latin1_General_CS_AS_WS	Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-sensitive
Latin1_General_CS_AS_KS	Latin1-General, case-sensitive, accent-sensitive, kanatype-sensitive, width-insensitive
Latin1_General_CS_AS_KS_WS	Latin1-General, case-sensitive, accent-sensitive, kanatype-sensitive, width-sensitive
Latin1_General_100_BIN	Latin1-General-100, binary sort
Latin1_General_100_BIN2	Latin1-General-100, binary code point comparison sort
Latin1_General_100_CI_AI	Latin1-General-100, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive
Latin1_General_100_CI_AI_WS	Latin1-General-100, case-insensitive, accent-insensitive, kanatype-insensitive, width-sensitive
Latin1_General_100_CI_AI_KS	Latin1-General-100, case-insensitive, accent-insensitive, kanatype-sensitive, width-insensitive
Latin1_General_100_CI_AI_KS_WS	Latin1-General-100, case-insensitive, accent-insensitive, kanatype-sensitive, width-sensitive
Latin1_General_100_CI_AS	Latin1-General-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive
Latin1_General_100_CI_AS_WS	Latin1-General-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-sensitive
Latin1_General_100_CI_AS_KS	Latin1-General-100, case-insensitive, accent-sensitive, kanatype-sensitive, width-insensitive
Latin1_General_100_CI_AS_KS_WS	Latin1-General-100, case-insensitive, accent-sensitive, kanatype-sensitive, width-sensitive
Latin1_General_100_CS_AI	Latin1-General-100, case-sensitive, accent-insensitive, kanatype-insensitive, width-insensitive
Latin1_General_100_CS_AI_WS	Latin1-General-100, case-sensitive, accent-insensitive, kanatype-insensitive, width-sensitive
Latin1_General_100_CS_AI_KS	Latin1-General-100, case-sensitive, accent-insensitive, kanatype-sensitive, width-insensitive
Latin1_General_100_CS_AI_KS_WS	Latin1-General-100, case-sensitive, accent-insensitive, kanatype-sensitive, width-sensitive
Latin1_General_100_CS_AS	Latin1-General-100, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive
Latin1_General_100_CS_AS_WS	Latin1-General-100, case-sensitive, accent-sensitive, kanatype-insensitive, width-sensitive
Latin1_General_100_CS_AS_KS	Latin1-General-100, case-sensitive, accent-sensitive, kanatype-sensitive, width-insensitive
Latin1_General_100_CS_AS_KS_WS	Latin1-General-100, case-sensitive, accent-sensitive, kanatype-sensitive, width-sensitive
Latin1_General_100_CI_AI_SC	Latin1-General-100, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive, supplementary characters
Latin1_General_100_CI_AI_WS_SC	Latin1-General-100, case-insensitive, accent-insensitive, kanatype-insensitive, width-sensitive, supplementary characters
Latin1_General_100_CI_AI_KS_SC	Latin1-General-100, case-insensitive, accent-insensitive, kanatype-sensitive, width-insensitive, supplementary characters
Latin1_General_100_CI_AI_KS_WS_SC	Latin1-General-100, case-insensitive, accent-insensitive, kanatype-sensitive, width-sensitive, supplementary characters
Latin1_General_100_CI_AS_SC	Latin1-General-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive, supplementary characters
Latin1_General_100_CI_AS_WS_SC	Latin1-General-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-sensitive, supplementary characters
Latin1_General_100_CI_AS_KS_SC	Latin1-General-100, case-insensitive, accent-sensitive, kanatype-sensitive, width-insensitive, supplementary characters
Latin1_General_100_CI_AS_KS_WS_SC	Latin1-General-100, case-insensitive, accent-sensitive, kanatype-sensitive, width-sensitive, supplementary characters
Latin1_General_100_CS_AI_SC	Latin1-General-100, case-sensitive, accent-insensitive, kanatype-insensitive, width-insensitive, supplementary characters
Latin1_General_100_CS_AI_WS_SC	Latin1-General-100, case-sensitive, accent-insensitive, kanatype-insensitive, width-sensitive, supplementary characters
Latin1_General_100_CS_AI_KS_SC	Latin1-General-100, case-sensitive, accent-insensitive, kanatype-sensitive, width-insensitive, supplementary characters
Latin1_General_100_CS_AI_KS_WS_SC	Latin1-General-100, case-sensitive, accent-insensitive, kanatype-sensitive, width-sensitive, supplementary characters
Latin1_General_100_CS_AS_SC	Latin1-General-100, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive, supplementary characters
Latin1_General_100_CS_AS_WS_SC	Latin1-General-100, case-sensitive, accent-sensitive, kanatype-insensitive, width-sensitive, supplementary characters
Latin1_General_100_CS_AS_KS_SC	Latin1-General-100, case-sensitive, accent-sensitive, kanatype-sensitive, width-insensitive, supplementary characters
Latin1_General_100_CS_AS_KS_WS_SC	Latin1-General-100, case-sensitive, accent-sensitive, kanatype-sensitive, width-sensitive, supplementary characters

As you can see, none of the collations in the above list include the _VSS option, because that option is only applicable to certain Japanese collations. SQL Server 2017 includes 32 collations with this option (such as Japanese_Bushu_Kakusu_140_CI_AI_KS_WS_VSS for example).