145

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

+-----------------------------+
|  table  |     collation     |
|-----------------------------|
| t_name  |  latin_general_ci |
+-----------------------------+
BenMorel
  • 34,448
  • 50
  • 182
  • 322
Scott
  • 3,290
  • 4
  • 29
  • 48

5 Answers5

209

SHOW TABLE STATUS shows information about a table, including the collation.

For example SHOW TABLE STATUS where name like 'TABLE_NAME'

Robin
  • 2,616
  • 22
  • 30
Lekensteyn
  • 64,486
  • 22
  • 159
  • 192
83

The above answer is great, but it doesn't actually provide an example that saves the user from having to look up the syntax:

show table status like 'test';

Where test is the table name.

(Corrected as per comments below.)

Moustafa Elqabbany
  • 1,110
  • 9
  • 10
34

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

Giorgos Myrianthous
  • 36,235
  • 20
  • 134
  • 156
10

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)
Qback
  • 4,310
  • 3
  • 25
  • 38
9

Use this query:

SHOW CREATE TABLE tablename

You will get all information related to table.

gvlasov
  • 18,638
  • 21
  • 74
  • 110
Rizwan Siddiquee
  • 831
  • 8
  • 7
  • 3
    SHOW CREATE TABLE will not show collation. You have to use SHOW TABLE STATUS as described above. – KateYoak Apr 11 '16 at 19:50
  • 1
    Works for me in mysql 5.5.52. `...) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=latin1` My guess is it may not show the collation if it is set to the default for the database in later versions of mysql/mariadb. – DeveloperChris Dec 08 '16 at 23:28
  • 1
    @DeveloperChris What you show is the charset, not the collation. Two tables may have the same charset `utf8`, but different collations `utf8_general_ci` vs `utf8_unicode_ci`. This can cause error messages like `HY000, 1267, Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='`... which is the message that brought me to this page. – Dewi Morgan Sep 24 '18 at 19:15