EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Character Set
 

MySQL Character Set

Roja Metla
Article byRoja Metla
EDUCBA
Reviewed byRavi Rathore

MySQL-Character-Set

Introduction to MySQL Character Set

A character set in MySQL is a set of characters. Character sets in MySQL can have one or more collations. You can see MySQL character sets at the database, table, and column levels. The default character set is “LATIN1”. Unicode character set is used to store multiple languages in a column. Character set can hold single-byte characters and multi-character sets. Latin1, latin2, etc., are single-byte character sets. MySQL can perform the conversion of the character set. We have a char_length function to get the string length in characters.

 

 

Syntax #1

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Below is the syntax to check the character set:

SHOW CHARACTER SET;

Syntax #2

Below is the syntax to check the character set used at the database level:

SELECT * FROM information_schema.SCHEMATA;

Syntax #3

Below is the syntax to check the character set used at the table level:

SELECT T.*,COL.character_set_name FROM information_schema.TABLES T,
information_schema.COLLATION_CHARACTER_SET_APPLICABILITY COL
WHERE COL.collation_name = T.table_collation
AND T.table_schema = "<SCHEMA_NAME>";

Syntax #4

Below is the syntax to check the character set used in the COLUMN:

SELECT * FROM information_schema.COLUMNS
WHERE table_schema = "<SCHEMA_NAME>"
AND table_name = "<TABLE_NAME>"
AND column_name = "<COLUMN_NAME>";

How does the Character set work in MySQL?

Now let us check the same that is present in this. The query can find this:

Code:

SHOW CHARACTER SET;

Output:

Character set

Let us get the same from the tables:

Code:

SELECT T.TABLE_SCHEMA,T.TABLE_NAME,T.ENGINE,T.TABLE_COLLATION,COL.character_set_name
FROM information_schema.TABLES T, information_schema.COLLATION_CHARACTER_SET_APPLICABILITY COL
WHERE COL.collation_name = T.table_collation
AND T.table_schema = "sourcedb"
AND T.TABLE_NAME IN ("COLLEGEDATA", "LOAN_TABLE", "T1", "TEST", "DATA", "NEW_SAMPLE1");

Output:

MySQL Character Set - 2

Code: Below is the syntax to check the same used in the COLUMN: –

SELECT Table_name,
Column_name,
Character_set_name,
collation_name
FROM information_schema.COLUMNS
WHERE table_schema = "sourcedb"
AND table_name = "collegedata"
AND column_name in ( "college_name", "location") ;

Output:

COLUMN

Examples

Now let us create a table with character-type columns and check the “character_set”. And we can perform the conversion of the character set.

Code #1

create table characterset_table
(
id int,
text1 varchar(30),
text2 varchar(30),
text3 varchar(30),
text4 varchar(30)
);

Code #2 Now let us insert data into the table

insert into characterset_table values (1, 'My textbook', 'character set table', 'how are you', 'same to you');
insert into characterset_table values (2, 'My notebook', 'character set', 'hello!', 'same too');
insert into characterset_table values (3, 'My laptop', 'et table', 'hello my world', 'text needed');
insert into characterset_table values (4, 'My resort', 'table', 'my world', 'entered random data');
insert into characterset_table values (5, 'My planet', 'character table', 'world', 'example for character set');
select * from characterset_table;

Code #3 – Query to get the character set details from the table

SELECT Table_name,
Column_name,
Character_set_name,
collation_name
FROM information_schema.COLUMNS
WHERE table_schema = "sourcedb"
AND table_name = "characterset_table"
AND column_name in ( "text1", "text2", "text3", "text4") ;

Code #4 Here, we convert the character set from utf8mb4 to ucs2. We can see the size difference between them.

select TEXT1,
LENGTH(TEXT1), /* -- actual size with character set utf8mb4 -- */
CHAR_LENGTH(TEXT1), /* -- actual size with character set utf8mb4 -- */
LENGTH(CONVERT (TEXT1 USING UCS2)), /* -- size with character set ucs2 -- */
CHAR_LENGTH(CONVERT (TEXT1 USING UCS2)) /* -- size with character set uc2 -- */
from
characterset_table;

Here the LENGTH is used to get the length of the string in bytes, and char_length to get the length of the string in characters. To convert the character set, we use the below syntax: –

Code #5

CONVERT (column_name USING character_set_name)

CONVERT function

The CAST function is similar to the CONVERT function. It converts a string to a different character set. Below is the syntax for the same: –

Code #6

CAST(string AS character_type CHARACTER SET character_set_name)

Conclusion

A character set in MySQL is a set of characters. This MySQL is one or more collations. Character sets can be seen on the database, table, and column levels. The default character set is “LATIN1”. Unicode character set is used to store multiple languages in a column. This can hold single-byte characters and multi-character sets. Latin1, latin2, etc., are single-byte character sets. Conversion of the character set can be done. We have a char_length function to get the string length in characters.

Recommended Articles

We hope that this EDUCBA information on “MySQL Character Set” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. MySQL IN Operator
  2. LIKE In MySQL
  3. ANY In MySQL
  4. Cursor In MySQL

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW