How do I see what character set a database / table / column is in MySQL?

Question

How do I see what the character set that a MySQL database, table and column are in? Is there something like

SHOW CHARACTER SET FOR mydatabase;

and

SHOW CHARACTER SET FOR mydatabase.mytable;

and

SHOW CHARACTER SET FOR mydatabase.mytable.mycolumn;

Answer 1

I always just look at SHOW CREATE TABLE mydatabase.mytable.

For the database, it appears you need to look at SELECT DEFAULT_CHARACTER_SET_NAME FROM information_schema.SCHEMATA.

Answer 2

SHOW TABLE STATUS will list all the tables, and the collation column contains details about the character set it uses, which might be of some help?

Answer 3

show create table X

should have some useful info

Answer 4

USE your_database_of_interest;

and type,

show variables like "character_set_database";
show variables like "collation_database";

Cf. this page. And check out the MySQL manual

Answer 5

Here's how I'd do it -

For Schemas:

SELECT default_character_set_name FROM information_schema.SCHEMATA S
WHERE schema_name = "schemaname";

For Tables:

SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,
       information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
  AND T.table_schema = "schemaname"
  AND T.table_name = "tablename";

For Columns:

SELECT character_set_name FROM information_schema.`COLUMNS` C
WHERE schema_name = "schemaname"
  AND table_name = "tablename"
  AND column_name = "columnname";