MySql | How to find the collation and character set of MySQL tables?

To find the collation of tables within MySQL database, you can use one of the commands listed below

mysql> show table status from exampledb;

mysql> SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA=’exampledb’;

To find the character set used in a MySQL table

mysql> SELECT tbl.table_name, CCSA.character_set_name FROM information_schema.`TABLES` tbl, information_schema.COLLATION_CHARACTER_SET_APPLICABILITY CCSA WHERE CCSA.collation_name = tbl.table_collation AND tbl.table_schema = “schemaname”;