MySQL Character Set

Introduction to MySQL Character Set

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

Syntax #1

Below is the syntax to check the character set:

SHOW CHARACTER SET;

Syntax #2

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

SELECT * FROM information_schema.SCHEMATA;

Syntax #3

Below is the syntax to check the character set used in 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 Character set works in MySQL?

Now let us check the same that is present in this. This can be found by the query:

Code:

SHOW CHARACTER SET;

Output:

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:

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:

Examples

Now let us create a table with columns of character type 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 charcter 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 are converting 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 to get the length of the string in bytes and char_length to get the length of string in characters. To convert the character set we use the below syntax: –

Code #5

CONVERT (column_name USING character_set_name)

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

Code #6

CAST(string AS character_type CHARACTER SET character_set_name)

Conclusion

Character set in MySQL is a set of characters. This in MySQL is one or more collations. Character set can be seen on database level, table level, and column level. 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 set. Latin1, latin2, etc., are single byte character set. Conversion of the character set can be done. We have char_length function is to get the length of string in characters.

Recommended Articles

This is a guide to MySQL Character Set. Here we discuss an introduction to MySQL Character Set, syntax, how does it work with examples. You can also go through our other related articles to learn more –

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