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

Also see:

Reduce the Number of Columns Returned

Using SHOW CREATE TABLE

Using `SHOW CREATE TABLE`