Character Sets And Collations in MySQL

Introduction

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 choose the database character set and the collation of the database and it can be come to the point on character sets at the server, database, table, and at the column level.

Character Sets

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.

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.

mysql> Show character set;



To the 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:

mysql> show collation like 'latin1%';



Define the column and table with the collate and its character

For Example

In the following example we have to define the column and table with the collation and it's character.



Table character set and a table collation

Example

Here we have to define the table has a table character set and a table collation. Create a table win with first_name column and set the collation.
mysql> Create table win( first_name varchar(30)
> character set latin1 collate latin1_danish_ci;



Collation in character set and column character set

Example

In this example create a table "body" and show the collation on column character set.

mysql> Create table body(> column1 varchar(5) character set latin1 collate latin1_german1_ci>  );



Resources

Here are some useful resources:

• Creating table and retrieving data from MySQl in JDBC
• Textbox Text Character Counter in Asp.net
• SQL SERVER 2005 : Using Special Characters (Wild Characters) in LIKE And NOT LIKE Operator
• CustomWebControlsLib
• Case When Select Command in MySQL Server