A character set is a set of characters while a collation is the rules for comparing and sorting a particular character set.
For example, a subset of a character set could consist of the letters A, B and C. A default collation could define these as appearing in an ascending order of A, B, C.
If we consider different case characters, more complexity is added. A binary collation would evaluate the characters A and a differently, ordering them in a particular way. A case-insensitive collation would evaluate A and a equivalently, while the German phone book collation evaluates the characters ue and ü equivalently.
A character set can have many collations associated with it, while each collation is only associated with one character set. In MariaDB, the character set name is always part of the collation name. For example, the latin1_german1_ci collation applies only to the latin1 character set. Each character set also has one default collation. The latin1 default collation is latin1_swedish_ci.
As an example, by default, the character y comes between x and z, while in Lithuanian, it’s sorted between i and k. Similarly, the German phone book order is different to the German dictionary order, so while they share the same character set, the collation is different.
