Post
Ask Question

Character Sets And Collations in MySQL

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.
  1. Introduction
  2. Character Sets
  3. Conversion between different Character Sets
  4. Collations
  5. Setting Character Sets and Collation
  6. 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.
  1. SHOW CHARACTER SET; 
 
Note:
“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
  1. SHOW COLLATION LIKE 'latin1%'; 
 

Setting Character Sets and Collations at Database Level

 
When you create a database, you can specify the default character set and the collation for a database. But, if you don’t specify it, MySQL will use the default character sets and collation.
 
Syntax
CREATE DATABASE <database_name>
CHARACTER SET <character_set_name>
COLLATE <collation_name>
 

Setting Character Sets and Collations at the Table Level

 
When you create a table, you can also specify the default character set and the collation for a table. But, if you don’t specify it, MySQL will use the default character sets and collation.
 
Syntax
CREATE TABLE <table_name> (
<column_name1> datatype,
<column_name2> datatype, …
<column_nameN> datatype
)
CHARACTER SET <character_set_name>
COLLATE <collation_name>;
 
For Example
 

1) Define the column and table with the collate and its character

 
Example - In the following example we have to define the column and table with the collation and its character.
  1. CREATE TABLE test(  
  2.   C_ID INT,  
  3.   C_Name VARCHAR(50)  
  4. )  
  5. DEFAULT CHARACTER SET LATIN1  
  6. COLLATE latin1_general_ci;  
  7.   
  8. DESCRIBE TEST; 
 

2) Setting Collation and Character Set a Table Level

 
Example - Here we have to define the table has a table character set and a table collation. Create a table “RACEWINNER” with R_ID and First_name column and set the collation.
  1. Create table RACEWINNER(  
  2.   R_ID INT,  
  3.   First_name varchar(30)  
  4. ) CHARACTER SET latin1  
  5. COLLATE latin1_danish_ci;  
  6.   
  7. DESCRIBE RACEWINNER; 
 

3) Setting Collation and character set at Column Level

 
Example - In this example create a table "Company" and show the collation on the column character set.
  1. Create table Company(  
  2.   C_column varchar(50) CHARACTER SET latin1 COLLATE latin1_german1_ci  
  3. );  
  4.   
  5. DESCRIBE Company;
 

CONCLUSION

 
In this article, I have discussed the concept of Character Sets and Collations in MySQL with various examples.
 
I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about MySQL.
 
Thanks for reading this article.


Similar Articles