Database Reference
In-Depth Information
A collation can be case sensitive (
a
and
A
are different), case insensitive (
a
and
A
are the
same), or binary (two characters are the same or different based on whether their nu‐
meric values are equal). A collation name ending in
_ci
,
_cs
, or
_bin
is case insensitive,
case sensitive, or binary, respectively.
A binary collation provides a sort order for nonbinary strings that is something like the
order for binary strings, in the sense that comparisons for binary strings and binary
collations both use numeric values. The difference is that binary string comparisons are
always based on single-byte units, whereas a binary collation compares nonbinary
strings using
character
numeric values; depending on the character set, some of these
might be multibyte values.
The following example illustrates how collation affects sort order. Suppose that a table
contains a
latin1
string column and has the following rows:
mysql>
CREATE TABLE t (c CHAR(3) CHARACTER SET latin1);
mysql>
INSERT INTO t (c) VALUES('AAA'),('bbb'),('aaa'),('BBB');
mysql>
SELECT c FROM t;
+------+
| c |
+------+
| AAA |
| bbb |
| aaa |
| BBB |
+------+
By applying the
COLLATE
operator to the column, you can choose which collation to use
for sorting and thus affect the order of the result:
• A case-insensitive collation sorts
a
and
A
together, placing them before
b
and
B
.
However, for a given letter, it does not necessarily order one lettercase before an‐
other, as shown by the following result:
mysql>
SELECT c FROM t ORDER BY c COLLATE latin1_swedish_ci;
+------+
| c |
+------+
| AAA |
| aaa |
| bbb |
| BBB |
+------+
• A case-sensitive collation puts
A
and
a
before
B
and
b
, and sorts uppercase before
lowercase:
mysql>
SELECT c FROM t ORDER BY c COLLATE latin1_general_cs;
+------+
| c |
+------+
