MySQL - Collate

database, mysql

Solution

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 |
+----------------------+-------------------+

Problem

Original source