Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 12 down vote accepted

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.

share|improve this answer
    
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.? –  Shameer Jul 22 '11 at 9:08
1  
I 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. –  deadalnix Jul 22 '11 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 [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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.