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;