What is the best MySQL collation for German language

by

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.

More Related Contents:

  1. MySQL treats ÅÄÖ as AAO?
  2. What’s the difference between utf8_general_ci and utf8_unicode_ci?
  3. What is the difference between utf8mb4 and utf8 charsets in MySQL?
  4. Troubleshooting “Illegal mix of collations” error in mysql
  5. UTF-8: General? Bin? Unicode?
  6. What is the best collation to use for MySQL with PHP? [closed]
  7. How to change the CHARACTER SET (and COLLATION) throughout a database?
  8. How to change collation of database, table, column?
  9. What effects does using a binary collation have?
  10. Are column and table name case sensitive in MySQL?
  11. Illegal mix of collations error in MySql
  12. #1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’
  13. How to change the default charset of a MySQL table?
  14. MySQL Illegal mix of collations
  15. UTF8 problem with MySQL 5
  16. How to get maximum number of products matched vendors list. But i don't want to check same products again [closed]
  17. How to select the nth row in a SQL database table?
  18. MySQL – How to unpivot columns to rows?
  19. MySQL SELECT only not null values
  20. How can I use mySQL replace() to replace strings in multiple records?
  21. Difference between two dates in MySQL
  22. How to remove accents in MySQL?
  23. MySQL search and replace some text in a field
  24. Why is MySQL InnoDB insert so slow?
  25. Group mysql query by 15 min intervals
  26. MySQL – force not to use cache for testing speed of query
  27. MySQL FULL JOIN?
  28. Using Count to find the number of occurrences
  29. How would I use ON DUPLICATE KEY UPDATE in my CodeIgniter model?
  30. Transpose rows into columns in MySQL

Leave a Comment