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.
Also refer to stackoverflow.com/questions/341273/…– Sanghyun LeeDec 29, 2015 at 13:28
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.
Thanks, Its almost clear now. So I think selecting the collation is a serious concern while planning for a multilingual site? For example if the collation is 'latin1_swedish_ci', I wont be getting expected result while dealing with unicode languages.?– ShameerJul 22, 2011 at 9:08
1I would suggest you to go for unicode or you'll ends up having some trouble at some point (asiatics languages for exemple). The collation will depend on the language of the user. This is something you have to be carefull with if you want to have a multilingual tool. Jul 22, 2011 at 10:27
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  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.
1swedish is the default in some bits of MySQL still. Its legacy from when MySQL was created by a small team in Sweden back in '95 Aug 25, 2016 at 14:44