MySQL: Script to find default Character Set for Database, Table and Column

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am sharing few scripts for finding the default character set of a MySQL Database, Table and Column.

Generally, during the process of database migration, we are checking default character set and collation at MySQL Server level.

But in our production MySQL Server, I found that some of the tables have different character set which was not required.

As a database professional, we have to maintain consistency between all database objects.

Find default Characater Set for Database:

SELECT

default_character_set_name

FROM information_schema.SCHEMATA

WHERE schema_name = 'Database_Name';

Find default Characater Set for Table:

SELECT

CCSA.character_set_name

FROM information_schema.TABLES AS T

INNER JOIN information_schema.COLLATION_CHARACTER_SET_APPLICABILITY AS CCSA

ON CCSA.collation_name = T.table_collation

WHERE T.table_schema = 'Database_Name'

AND T.table_name = 'Table_Name';

Find default Character Set for Column:

SELECT

character_set_name

FROM information_schema.COLUMNS

WHERE table_schema = 'Database_Name'

AND table_name = 'Table_Name'

AND column_name = 'Column_Name';

Please Visit Other Related Articles !

Database Theory: What is Character Set and Collation

Apr 24, 2016Anvesh Patel