Checking character set in MySQL
Start your free 7-days trial now!
We can check the character set in MySQL on a database, table, column, string level using varying syntax.
Examples
Databases
To check the charset for our database (this works only for databases that you are currently using):
SHOW variables LIKE "character_set_database";
+------------------------+---------+| Variable_name | Value |+------------------------+---------+| character_set_database | utf8mb4 |+------------------------+---------+
To check the charset for all databases:
SELECT SCHEMA_NAME 'database', default_character_set_name FROM information_schema.SCHEMATA;
+--------------------+----------------------------+| database | DEFAULT_CHARACTER_SET_NAME |+--------------------+----------------------------+| mysql | utf8mb4 || information_schema | utf8 || performance_schema | utf8mb4 || sys | utf8mb4 || people | utf8mb4 || tutorial | latin2 |+--------------------+----------------------------+
For Tables
SELECT t.TABLE_NAME, ccsa.CHARACTER_SET_NAME FROM information_schema.tables t, information_schema.collation_character_set_applicability ccsa WHERE t.table_collation = ccsa.collation_name AND t.TABLE_SCHEMA = 'database_name' AND t.TABLE_NAME = 'table_name';
Consider the following information about some students:
student_id | fname | lname | day_enrolled | age | username |
|---|---|---|---|---|---|
1 | Sky | Towner | 2015-12-03 | 17 | stowner1 |
2 | Ben | Davis | 2016-04-20 | 19 | bdavis2 |
3 | Travis | Apple | 2018-08-14 | 18 | tapple3 |
4 | Arthur | David | 2016-04-01 | 16 | adavid4 |
5 | Benjamin | Town | 2014-01-01 | 17 | btown5 |
The above sample table can be created using the code here.
To check the character set and collation for the table students within the people database:
SELECT t.TABLE_NAME, ccsa.CHARACTER_SET_NAME, t.TABLE_COLLATION FROM information_schema.tables t, information_schema.collation_character_set_applicability ccsa WHERE t.table_collation = ccsa.collation_name AND t.TABLE_SCHEMA = 'people' AND t.TABLE_NAME = 'students';
+------------+--------------------+--------------------+| TABLE_NAME | CHARACTER_SET_NAME | TABLE_COLLATION |+------------+--------------------+--------------------+| students | utf8mb4 | utf8mb4_0900_ai_ci |+------------+--------------------+--------------------+
For Columns
SHOW FULL COLUMNS FROM table_name;
Consider the following information about some students:
student_id | fname | lname | day_enrolled | age | username |
|---|---|---|---|---|---|
1 | Sky | Towner | 2015-12-03 | 17 | stowner1 |
2 | Ben | Davis | 2016-04-20 | 19 | bdavis2 |
3 | Travis | Apple | 2018-08-14 | 18 | tapple3 |
4 | Arthur | David | 2016-04-01 | 16 | adavid4 |
5 | Benjamin | Town | 2014-01-01 | 17 | btown5 |
The above sample table can be created using the code here.
To check the character set and collation for columns within the students table:
SHOW FULL COLUMNS FROM students;
+--------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |+--------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+| student_id | int unsigned | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | || fname | varchar(30) | utf8mb4_0900_ai_ci | YES | | NULL | | select,insert,update,references | || lname | varchar(30) | utf8mb4_0900_ai_ci | YES | | NULL | | select,insert,update,references | || day_enrolled | date | NULL | YES | | NULL | | select,insert,update,references | || age | int | NULL | YES | | NULL | | select,insert,update,references | || username | varchar(15) | utf8mb4_0900_ai_ci | YES | | NULL | | select,insert,update,references | |+--------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
Note that although the character set is not given directly as a column in the results, we can infer the character set from the Collation value.
For Strings
To check the character set for string 'apple':
SELECT CHARSET('apple');
+------------------+| CHARSET('apple') |+------------------+| utf8mb4 |+------------------+
