Database Reference
In-Depth Information
Suppose that the table has these contents:
+--------+--------+---------+
| ci_str | cs_str | bin_str |
+--------+--------+---------+
| AAA | AAA | AAA |
| aaa | aaa | aaa |
| bbb | bbb | bbb |
| BBB | BBB | BBB |
+--------+--------+---------+
Each column contains the same values, but the natural sort orders for the column data
types produce three different results:
• The 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 ci_str FROM str_val ORDER BY ci_str;
+--------+
| ci_str |
+--------+
| AAA |
| aaa |
| bbb |
| BBB |
+--------+
• The case-sensitive collation puts
A
and
a
before
B
and
b
, and sorts uppercase before
lowercase:
mysql>
SELECT cs_str FROM str_val ORDER BY cs_str;
+--------+
| cs_str |
+--------+
| AAA |
| aaa |
| BBB |
| bbb |
+--------+
• The binary strings sort numerically. Assuming that uppercase letters have numeric
values less than those of lowercase letters, a binary sort results in the following
ordering:
mysql>
SELECT bin_str FROM str_val ORDER BY bin_str;
+---------+
| bin_str |
+---------+
| AAA |
| BBB |
| aaa |
