What is the best MySQL collation for German language
I am building a web site in German language, So I will be using characters like ä, ü, ß etc., So what are your recommendations?
Answer
This answer is outdated. For full emoji support, see this answer.
As the character set, if you can, definitely UTF-8.
As the collation - that's a bit nasty for languages with special characters. There are various types of collations. They can all store all Umlauts and other characters, but they differ in how they treat Umlauts in comparisons, i.e. whether
u = ü
is true or false; and in sorting (where in the alphabets the Umlauts are located in the sorting order).
To make a long story short, your best bet is either
utf8_unicode_ci
It allows case insensitive searches; It treats ß as ss and uses DIN-1 sorting. Sadly, like all non-binary Unicode collations, it treats u = ü which is a terrible nuisance because a search for "Muller" will also return "Müller". You will have to work around that by setting a Umlaut-aware collation in real time.
or utf8_bin
This collation does not have the u = ü problem but only case sensitive searches are possible.
I'm not entirely sure whether there are any other side effects to using the binary collation; I asked a question about that here.
This mySQL manual page gives a good overview over the various collations and the consequences they bring in everyday use.
Here is a general overview on available collations in mySQL.