Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
Updated on: 30-Jul-2019
3K+ Views
Advertisements