mysql check collation of a table

user_109277 0
Viewed 124231

How can I see what collation a table has? I.E. I want to see:

+-----------------------------+
|  table  |     collation     |
|-----------------------------|
| t_name  |  latin_general_ci |
+-----------------------------+
collation mysql
5 Answers

Checking the collation of a specific table

You can query INFORMATION_SCHEMA.TABLES and get the collation for a specific table:

SELECT TABLE_SCHEMA
    , TABLE_NAME
    , TABLE_COLLATION 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 't_name';

that gives a much more readable output in contrast to SHOW TABLE STATUS that contains a lot of irrelevant information.

Checking the collation of columns

Note that collation can also be applied to columns (which might have a different collation than the table itself). To fetch the columns' collation for a particular table, you can query INFORMATION_SCHEMA.COLUMNS:

SELECT TABLE_SCHEMA 
    , TABLE_NAME 
    , COLUMN_NAME 
    , COLLATION_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 't_name';

For more details you can refer to the article How to Check and Change the Collation of MySQL Tables

user_71317570

Check collation of the whole database

If someone is looking here also for a way to check collation on the whole database:

  1. use mydatabase; (where mydatabase is the name of the database you're going to check)
  2. SELECT @@character_set_database, @@collation_database;

You should see the result like:

+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4                  | utf8mb4_unicode_ci   |
+--------------------------+----------------------+
1 row in set (0.00 sec)
user_72845820
Related