Tag Archives: column

Check character set and collation for MySQL

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:

Character Set.JPG