How can I see what collation a table has? I.E. I want to see:
+-----------------------------+ | table | collation | |-----------------------------| | t_name | latin_general_ci | +-----------------------------+ 
Add a comment |
5 Answers
SHOW TABLE STATUS shows information about a table, including the collation.
For example SHOW TABLE STATUS where name like 'TABLE_NAME'
2 Comments
Pablo Pazos
no need of using LIKE, if that's the name just use =
racitup
You need to select a database first:
use <databasename>;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.)
Comments
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
answered Sep 24, 2018 at 12:40
Giorgos Myrianthous
40.4k21 gold badges155 silver badges174 bronze badges
Check collation of the whole database
If someone is looking here also for a way to check collation on the whole database:
use mydatabase;(wheremydatabaseis the name of the database you're going to check)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) 
Comments
Use this query:
SHOW CREATE TABLE tablename You will get all information related to table.
3 Comments
KateYoak
SHOW CREATE TABLE will not show collation. You have to use SHOW TABLE STATUS as described above.
DeveloperChris
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.
Dewi Morgan
@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.