What is MySQL Collation?

MySQL Collation is the collection of rules used to compare characters in a character sets. The Collation Set used is mentioned in the Default_collation column in the Show Character set command.

 A collation for a character set begins with the character set name and ends with _ci (case insensitive) _cs  (case sensitive) or _bin  (binary).

Show collation in MySQL

To see details of all the Collations we can do a show,

SHOW COLLATION;

We can also get details related to one character set as follows:

SHOW COLLATION like '%utf8%';

Setting collation for a database in MySQL

We can change the Collation sets that a character set uses at server startup time or while creating a database. To set the Collation for a character set while creating a database we do

CREATE DATABASE <database_name>
CHARACTER SET <character_set_name>;
COLLATE <collation_name>

For example 

CREATE DATABASE testdb
CHARACTER SET ascii
COLLATE ascii_general_ci;

We can also alter a database property after it has been created by using the Alter command

ALTER DATABASE <database_name>
CHARACTER SET <character_set_name>
COLLATE <collation_name>;

For a created database employees, we want to change the collation from latine to greek, we can do:

ALTER DATABASE employees
CHARACTER SET greek
COLLATE greek_general_ci;

Setting collation for a table in MySQL

Similar to a database we can set the collation for a table as well.

CREATE TABLE table_name(
  <columns> <datatypes> <constraints>
)
CHARACTER SET <character_set_name>
COLLATE <collation_name>

For example, if we have a table that will store Korean words, then we can use the utf8 collation.

CREATE TABLE korean_words(
  words varchar(50)
)
CHARACTER SET 'euckr'
COLLATE 'utf8_unicode_ci';

We can also alter the collation of an already created table

ALTER TABLE <table_name>(
  ...
)
CHARACTER SET <character_set_name>
COLLATE <collation_name>

For example,

ALTER TABLE some_table
CHARACTER SET latin1
COLLATE latin1_german1_ci;