MySQL Collations

List Collations

To list all collations available in MySQL, use

SHOW COLLATION;
+-------------------+----------+-----+---------+----------+---------+
| Collation         | Charset  | Id  | Default | Compiled | Sortlen |
+-------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci   | big5     |   1 | Yes     | Yes      |       1 |
| big5_bin          | big5     |  84 |         | Yes      |       1 |
| dec8_swedish_ci   | dec8     |   3 | Yes     | Yes      |       1 |
| dec8_bin          | dec8     |  69 |         | Yes      |       1 |
| cp850_general_ci  | cp850    |   4 | Yes     | Yes      |       1 |
| cp850_bin         | cp850    |  80 |         | Yes      |       1 |
| hp8_english_ci    | hp8      |   6 | Yes     | Yes      |       1 |
| hp8_bin           | hp8      |  72 |         | Yes      |       1 |
| koi8r_general_ci  | koi8r    |   7 | Yes     | Yes      |       1 |
| koi8r_bin         | koi8r    |  74 |         | Yes      |       1 |
| latin1_german1_ci | latin1   |   5 |         | Yes      |       1 |
| latin1_swedish_ci | latin1   |   8 | Yes     | Yes      |       1 |
| latin1_danish_ci  | latin1   |  15 |         | Yes      |       1 |
| latin1_german2_ci | latin1   |  31 |         | Yes      |       2 |
| latin1_bin        | latin1   |  47 |         | Yes      |       1 |
| latin1_general_ci | latin1   |  48 |         | Yes      |       1 |
...

You can filter the list to show only collations for charset ‘latin1’:

SHOW COLLATION LIKE 'latin1%';
+-------------------+---------+----+---------+----------+---------+
| Collation         | Charset | Id | Default | Compiled | Sortlen |
+-------------------+---------+----+---------+----------+---------+
| latin1_german1_ci | latin1  |  5 |         | Yes      |       1 |
| latin1_swedish_ci | latin1  |  8 | Yes     | Yes      |       1 |
| latin1_danish_ci  | latin1  | 15 |         | Yes      |       1 |
| latin1_german2_ci | latin1  | 31 |         | Yes      |       2 |
| latin1_bin        | latin1  | 47 |         | Yes      |       1 |
| latin1_general_ci | latin1  | 48 |         | Yes      |       1 |
| latin1_general_cs | latin1  | 49 |         | Yes      |       1 |
| latin1_spanish_ci | latin1  | 94 |         | Yes      |       1 |
+-------------------+---------+----+---------+----------+---------+
8 rows in set (0.00 sec)

The convention for a collation name in MySQL: first character set name, then the name of the language, finally the type of the collation (ci stands for case-insensitive, cs for case-sensitive, bin for binary collation). So ‘latin1_danish_ci’ is a collation for charset ‘latin1’, for the Danish language and is case-insensitive.

Every collation in MySQL is assigned to exactly one character set. Every character set has one default collation which is used if the collation is not specified explicitly. MySQL checks if the collation and the character set match. If they don’t match, an error is raised.

mysql> create database wrong_collation character set utf8 collate  latin2_czech_cs;
ERROR 1253 (42000): COLLATION 'latin2_czech_cs' is not valid for CHARACTER SET 'utf8'

You can even add your own collation to MySQL: http://dev.mysql.com/doc/refman/5.7/en/adding-collation.html

Default Collation

In MySQL you can set the default collation for server, database, table or column. The default collation is inherited; that is, if you don’t change the collation for a database the server’s default will be used. If you don’t change the collation for a table the database’s collation will be used and so on.

Set Collation for a Database

        
CREATE DATABASE swedish_database
    CHARACTER SET latin1
    COLLATE latin1_swedish_ci;

ALTER DATABASE german_database
    CHARACTER SET latin1
    COLLATE latin1_german1_ci;

If you don’t specify the collation for a database, the default collation for selected charset is used.

Set Collation for a Table

CREATE TABLE users (
	id integer primary key,
	name varchar(255),
	email varchar(255),
	password varchar(255))
    CHARACTER SET utf8
    COLLATE utf8_polish_ci;

ALTER TABLE users
    CHARACTER SET utf8
    COLLATE utf8_czech_ci;

Set Collation for a Column

CREATE TABLE users
(
    id integer primary key,
    name VARCHAR(255)
      CHARACTER SET latin1
      COLLATE latin1_german1_ci
);

ALTER TABLE users MODIFY
    name VARCHAR(5)
      CHARACTER SET latin1
      COLLATE latin1_swedish_ci;