mysql check collation of a table
MysqlCollationMysql Problem Overview
How can I see what collation a table has? I.E. I want to see:
+-----------------------------+
| table | collation |
|-----------------------------|
| t_name | latin_general_ci |
+-----------------------------+
Mysql Solutions
Solution 1 - Mysql
SHOW TABLE STATUS shows information about a table, including the collation.
For example SHOW TABLE STATUS where name like 'TABLE_NAME'
Solution 2 - Mysql
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.)
Solution 3 - Mysql
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
Solution 4 - Mysql
Use this query:
SHOW CREATE TABLE tablename
You will get all information related to table.
Solution 5 - Mysql
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)