INTRODUCTION
In this tutorial, I am going to explain about Character Sets and Collations in MySQL with examples. This article will cover the following topics. Let’s see.
- Introduction
- Character Sets
- Conversion between different Character Sets
- Collations
- Setting Character Sets and Collation
- Conclusion
CHARACTER SETS
A character set is a set of symbols and their encoding. It is a set of behavior; it is used for analyzing the characters in a character set. MySQL also includes the character set support in which it prepares for storing data by using a collection of character sets and it also performs the estimation according to a variety of collations. MySQL chooses the database character set and the collation of the database and it can become the point on character sets at the server, database, table, and at the column level. Each character set contains one or more collations that determine a set of character comparison rules within the character set.
Character sets have developed through history but that history is beyond the bounds of this article. During this time the Unicode standard developed and achieves identification of the older character sets which are closed throughout. It is important to be conscious of the difference between Unicode and local character sets.
MySQL supports various character sets that allow almost any character to be stored in a string. Below is the CHARACTER SET Statement, by which the user can get all available character sets in the MySQL database.
- SHOW CHARACTER SET;
“Latin1” is the default character set in MySQL. And, you can use Unicode character sets, which are "utf8" or "ucs2", if you want to store characters from different languages in a single field.
Conversion between different Character Sets
By using the “CONVERT” and “CAST” functions in MySQL, you can convert strings between different character sets.
Syntax
a) Using Convert Function
CONVERT(expression USING character_set_name);
b) Using CAST Function
CAST(string AS character_type CHARACTER SET character_set_name);
COLLATION
Collations affect text comparison. We have to observe the default character set in MySQL. The default collation is latin1_swedish_ci. The Collations also deals with text ordering. For any two strings, the collation can affect the determination of which is greater, or if they are equal. The most common situation we will see the collations in action is when you use ORDER BY for a text column.
MySQL supports 70 and more collations for 30 and above the character sets. It indicates which character sets MySQL supports or not in the database. By using the “SHOW CHARACTER SET” statement in MySQL, the user can get the default collations of every character set.
In the above-given character set, it must have one collation. And, the given character set, it has several collations. To the collations list for a given character set, it includes all variable character sets by using the following statement.
In MySQL, you can use the "SHOW COLLATION" statement to get all the collations for a given character set.
Syntax
SHOW COLLATION LIKE 'character_set_name%';
Example
Join the conversation! Your thoughts help the community grow.