Database Reference
In-Depth Information
| AAA |
| aaa |
| BBB |
| bbb |
+------+
• A binary collation sorts characters using their numeric values. Assuming that up‐
percase letters have numeric values less than those of lowercase letters, a binary
collation results in the following ordering:
mysql>
SELECT c FROM t ORDER BY c COLLATE latin1_bin;
+------+
| c |
+------+
| AAA |
| BBB |
| aaa |
| bbb |
+------+
Note that because characters in different lettercases have different numeric values,
a binary collation produces a case-sensitive ordering. However, the order differs
from that for the case-sensitive collation.
If you require that comparison and sorting operations use the sorting rules of a particular
language, choose a language-specific collation. For example, if you store strings using
the
utf8
character set, the default collation (
utf8_general_ci
) treats
ch
and
ll
as two-
character strings. To use the traditional Spanish ordering that treats
ch
and
ll
as single
characters that follow
c
and
l
, respectively, specify the
utf8_spanish2_ci
collation. The
two collations produce different results, as shown here:
mysql>
CREATE TABLE t (c CHAR(2) CHARACTER SET utf8);
mysql>
INSERT INTO t (c) VALUES('cg'),('ch'),('ci'),('lk'),('ll'),('lm');
mysql>
SELECT c FROM t ORDER BY c COLLATE utf8_general_ci;
+------+
| c |
+------+
| cg |
| ch |
| ci |
| lk |
| ll |
| lm |
+------+
mysql>
SELECT c FROM t ORDER BY c COLLATE utf8_spanish2_ci;
+------+
| c |
+------+
| cg |
| ci |
| ch |
