Check character set and collation for MySQL
Check all database character Set and Collation
mysql> SELECT SCHEMA_NAME 'database', default_character_set_name 'charset', DEFAULT_COLLATION_NAME 'collation' FROM information_schema.SCHEMATA;
+--------------------+---------+--------------------+ | database | charset | collation | +--------------------+---------+--------------------+ | mysql | utf8mb4 | utf8mb4_0900_ai_ci | | information_schema | utf8 | utf8_general_ci | | performance_schema | utf8mb4 | utf8mb4_0900_ai_ci | | sys | utf8mb4 | utf8mb4_0900_ai_ci | | test | utf8mb4 | utf8mb4_0900_ai_ci | +--------------------+---------+--------------------+ 5 rows in set (0.00 sec)
Check the Default Character Set for Schema
SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = 'TEST';
+----------------------------+ | DEFAULT_CHARACTER_SET_NAME | +----------------------------+ | utf8mb4 | +----------------------------+ 1 row in set (0.00 sec)
-- From Variable
mysql> USE your_database_name;
mysql> show variables like "character_set_database";
+------------------------+---------+ | Variable_name | Value | +------------------------+---------+ | character_set_database | utf8mb4 | +------------------------+---------+ 1 row in set (0.00 sec)
Check the character set for table in MySQL
SELECT table_name,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 = 'TEST'
+------------+--------------------+ | TABLE_NAME | CHARACTER_SET_NAME | +------------+--------------------+ | test | utf8mb4 | +------------+--------------------+ 1 row in set (0.00 sec)
--From variable
SHOW TABLE STATUS WHERE NAME LIKE 'my_tablename';
Check the column character set in MySQL
SELECT table_name,column_name,character_set_name FROM information_schema.`COLUMNS`
WHERE table_schema = 'TEST'
AND table_name = 'test';
+------------+-------------+--------------------+ | TABLE_NAME | COLUMN_NAME | CHARACTER_SET_NAME | +------------+-------------+--------------------+ | test | id | NULL | +------------+-------------+--------------------+ 1 row in set (0.00 sec)
--From variable
SHOW FULL COLUMNS FROM my_tablename;
Note: For MySQL > 5.6 default-character-set is not valid and you need to use character-set-server instead.
Check character set from Workbench
Connect with Workbench and go to Status and System Variable –> System Variable –> Type char –> it will show you all Char parameters as shown in following image:
Smart way of Technology 