Collation Issues
785
mysql>
CREATE TABLE t1 (
->
a CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin,
->
b BINARY(10)
->
);
Query OK, 0 rows affected (0.09 sec)
mysql>
INSERT INTO t1 VALUES ('a','a');
Query OK, 1 row affected (0.01 sec)
mysql>
SELECT HEX(a), HEX(b) FROM t1;
+--------+----------------------+
| HEX(a) | HEX(b) |
+--------+----------------------+
| 61 | 61000000000000000000 |
+--------+----------------------+
1 row in set (0.04 sec)
10.1.7.7. The
BINARY
Operator
The
BINARY
[948]
operator casts the string following it to a binary string. This is an easy way to force
a comparison to be done byte by byte rather than character by character.
BINARY
[948]
also causes
trailing spaces to be significant.
mysql>
SELECT 'a' = 'A';
-> 1
mysql>
SELECT BINARY 'a' = 'A';
-> 0
mysql>
SELECT 'a' = 'a ';
-> 1
mysql>
SELECT BINARY 'a' = 'a ';
-> 0
BINARY str
is shorthand for
CAST(str AS BINARY)
[948]
.
The
BINARY
attribute in character column definitions has a different effect. A character column
defined with the
BINARY
attribute is assigned the binary collation of the column character set. Every
character set has a binary collation. For example, the binary collation for the
latin1
character set
is
latin1_bin
, so if the table default character set is
latin1
, these two column definitions are
equivalent:
CHAR(10) BINARY
CHAR(10) CHARACTER SET latin1 COLLATE latin1_bin
The effect of
BINARY
as a column attribute differs from its effect prior to MySQL 4.1. Formerly,
BINARY
resulted in a column that was treated as a binary string. A binary string is a string of bytes that has no
character set or collation, which differs from a nonbinary character string that has a binary collation.
For both types of strings, comparisons are based on the numeric values of the string unit, but for
nonbinary strings the unit is the character and some character sets support multi-byte characters.
Section 11.1.6.2, “The
BINARY
and
VARBINARY
Types”
.
The use of
CHARACTER SET binary
in the definition of a
CHAR
,
VARCHAR
, or
TEXT
column causes
the column to be treated as a binary data type. For example, the following pairs of definitions are
equivalent:
CHAR(10) CHARACTER SET binary
BINARY(10)
VARCHAR(10) CHARACTER SET binary
VARBINARY(10)
TEXT CHARACTER SET binary
BLOB
10.1.7.8. Examples of the Effect of Collation
Example 1: Sorting German Umlauts
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
