The main point of a database collation is determining how data is sorted and compared.
Case sensitivity of string comparisons
SELECT "New York" = "NEW YORK";`
will return true for a case insensitive collation; false for a case sensitive one.
Which collation does which can be told by the _ci and _cs suffix in the collation's name. _bin collations do binary comparisons (strings must be 100% identical).
Comparison of umlauts/accented characters
the collation also determines whether accented characters are treated as their latin base counterparts in string comparisons.
SELECT "Düsseldorf" = "Dusseldorf";
SELECT "èclair" = "Eclair";
will return true in the former case; false in the latter. You will need to read each collation's description to find out which is which.
String sorting
The collation influences the way strings are sorted.
For example,
Umlauts ? ? ü are at the end of the alphabet in the finnish/swedish alphabet latin1_swedish_ci
they are treated as A O U in German DIN-1 sorting (latin_german1_ci)
and as AE OE UE in German DIN-2 sorting (latin_german2_ci). ("phone book" sorting)
In latin1_spanish_ci, "?" (n-tilde) is a separate letter between "n" and "o".
These rules will result in different sort orders when non-latin characters are used.
Using collations at runtime
You have to choose a collation for your table and columns, but if you don't mind the performance hit, you can force database operations into a certain collation at runtime using the COLLATE keyword.
This will sort table by the name column using German DIN-2 sorting rules:
SELECT name
FROM table
ORDER BY name COLLATE latin1_german2_ci;
Using COLLATE at runtime will have performance implications, as each column has to be converted during the query. So think twice before applying this do large data sets.
MySQL Reference:
与恶龙缠斗过久,自身亦成为恶龙；凝视深渊过久,深渊将回以凝视…
