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

To check what character set a MySQL database/ table/ column is, let us see an example: A database which has the name ‘business’ and table name is ‘student’ which is already present in the database is used.

Syntax in order to check the table character set.

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 = "yourDatabaseName"
AND T.table_name = "yourTableName";

Applying the above syntax to display character set for table student.

mysql> 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 = "business"
   -> AND T.table_name = "student";

The following is the output.

+--------------------+
| CHARACTER_SET_NAME |
+--------------------+
| utf8mb4            |
+--------------------+
1 row in set (0.13 sec)

To find character set for database, here is the syntax.

SELECT default_character_set_name FROM information_schema.SCHEMATA
    WHERE schema_name = "yourDatabaseName";

Applying the above syntax in order to see the character set for database.

mysql> SELECT default_character_set_name FROM information_schema.SCHEMATA
   -> WHERE schema_name = "business";

The following is the output.

+----------------------------+
| DEFAULT_CHARACTER_SET_NAME |
+----------------------------+
| utf8mb4                    |
+----------------------------+
1 row in set (0.00 sec)

To find the character set for column.

SELECT character_set_name FROM information_schema.`COLUMNS`
   WHERE table_schema = "yourDatabaseName"
   AND table_name = "yourTableName"
   AND column_name = "yourColumnName";

Applying the above syntax in order to see what character set is for column name ‘Name’ for the “student” table and “business” database.

mysql> SELECT character_set_name FROM information_schema.`COLUMNS`
   -> WHERE table_schema = "business"
   ->  AND table_name = "student"
   -> AND column_name = "Name";

The following is the output.

+--------------------+
| CHARACTER_SET_NAME |
+--------------------+
| utf8mb4            |
+--------------------+
1 row in set (0.00 sec)