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.
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;
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
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.
CREATE TABLE test(
C_ID INT,
C_Name VARCHAR(50)
)
DEFAULT CHARACTER SET LATIN1
COLLATE latin1_general_ci;
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.
Create table RACEWINNER(
R_ID INT,
First_name varchar(30)
) CHARACTER SET latin1
COLLATE latin1_danish_ci;
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.
Create table Company(
C_column varchar(50) CHARACTER SET latin1 COLLATE latin1_german1_ci
);
DESCRIBE Company;
HostForLIFE.eu SQL Server 2019 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.
