MySQL - Collate

Question

When I create a database I am ask to choose Default Collate, when I create a table I am ask to choose Collate. utf8_general_ci or ...latin...? What is the basis to distinguish which one is the right one?

Answer 1

A collation determines the sort order of characters, it is a set of rules for comparing characters in a character set. Any given character set always has at least one collation. The end of a collation name ci, cs, and bin means case insensitive, case sensitive and binary, respectively. A binary collation like utf8_bin is usually case sensitive, too, because it makes binary string comparisons based on the numerical values of strings.

This means if you want to make case insensitive select queries, where a and A are not different, you should use a collation which ends with ci. If you want to make a case sensitive query, where a and A are different, you should use a collation which ends with cs or bin.

If none is selected, the standard level is applied. There are various levels of collation in MySQL, they can for example be shown by

mysql> show variables like '%collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+