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