What is the difference between collation and character set?

Question

I have a general question about databases. We usually use the term collation with databases. I would like to know how its different from character set. I guess collation is a subset of character set. If its true, what is the purpose multiple collation under a character set.

Answer 1

Character sets is a list of symbols. If you compare ASCII to latin1, with latin1 you will be able to write all american words because latin1 contains all ASCII characters, which are sufficient to write any English word. On the contrary, with ASCII you will not be able to write all words of Western European specific languages, because for instance characters like 'À', 'ë', 'õ', 'Ñ' are missing.

Collation is about comparison between characters. It defines a set of rules to compare characters of a character set.

In MySQL, collations are often related to one language (e.g. 'latin1_swedish_ci', 'latin1_german1_ci', etc.). When you order a select query, a word starting by 'ö' will be placed between two words starting by 'o' and 'p' in some languages (with some collations). But with another collation, this character may be placed completely at the end, which make the resulting selection different.

Answer 2

As the previous answer, character set is a set of characters (letters, numbers, ideograms etc.) assigned to a number; to be able to see them, you must have the font (the small "pictures" we read). They were important using LatinX, because for each X, the same number (byte) could refer to a different character.

Nowadays it's more important to select the encoding: the standard is UTF-8, the most successful representation of Unicode (compatible with ASCII but so flexible to include all language, even Chinese and Japanese) and W3C standard. In other words, it includes all character sets.

Collation: this is an old-fashion word, derived from Latin. It means: compare a copy (manuscript) with the original to find differences (errors or changes). It was very useful in the past, but now it's almost an archaicism because our documents and books are written using computers and the copy is always identical to the original. Example: in Italian (derived from latin) there is the verb "collazionare", but it's in the dictionaries and nobody uses it.

So, the collation defines the behaviour of comparison operators: =, >, <, <=, >= ...

Of course those operators are used to decide if two strings are the same, or if a word is "greater" than another one, very important if we want to sort. The letters are not in the same order for every language, so a word can be greater than another one using a collation, but not using another one. See [1] for a practical example.

• the "bin" collations are the most strict: SELECT "b" = "B" => FALSE

• the "ci" collations are not case sensitive: SELECT "b" = "B" => TRUE

• the "general" tend to ignore some differences: SELECT "a" = "à" => TRUE

• other are specific for one or more languages. Example "swedish" (I don't know why swedish is often a default)

I am still searching for a website where the behaviour of every collation is described in detail... those examples are due to my experience.

[1] http://www.olcot.co.uk/sql-blogs/revised-difference-between-collation-sql_latin1_general_cp1_ci_as-and-latin1_general_ci_as