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

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:

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 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)

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