The BINARY
and
VARBINARY
types are similar to
CHAR
and
VARCHAR
, except that they contain
binary strings rather than nonbinary strings. That is, they
contain byte strings rather than character strings. This means
that they have no character set, and sorting and comparison are
based on the numeric values of the bytes in the values.
The allowable maximum length is the same for
BINARY
and
VARBINARY
as it is for
CHAR
and
VARCHAR
, except that the length
for BINARY
and
VARBINARY
is a length in bytes
rather than in characters.
Before MySQL 4.1.2,
BINARY(
and
M
)VARBINARY(
are
treated as M
)CHAR(
and
M
)
BINARYVARCHAR(
.
As of MySQL 4.1.2, the M
) BINARYBINARY
and
VARBINARY
data types are distinct
from the CHAR BINARY
and VARCHAR
BINARY
data types. For the latter types, the
BINARY
attribute does not cause the column to
be treated as a binary string column. Instead, it causes the
binary collation for the column character set to be used, and
the column itself contains nonbinary character strings rather
than binary byte strings. For example, in 4.1.2 and up,
CHAR(5) BINARY
is treated as CHAR(5)
CHARACTER SET latin1 COLLATE latin1_bin
, assuming that
the default character set is latin1
. This
differs from BINARY(5)
, which stores 5-bytes
binary strings that have no character set or collation. For
information about differences between nonbinary string binary
collations and binary strings, see
Section 9.1.7.4, “The _bin
and binary
Collations”.
If you assign a value to a BINARY
or VARBINARY
column that exceeds
the column's maximum length, the value is truncated to fit. If
the truncated characters are not spaces, a warning is generated.
The handling of trailing spaces is the same for
BINARY
and
VARBINARY
as it is for
CHAR
and
VARCHAR
. When
BINARY
values are stored, they
are right-padded with spaces to the specified length. When
BINARY
values are retrieved,
trailing spaces are removed. For
VARBINARY
, trailing spaces are
removed when values are stored.
For those cases where trailing pad bytes are stripped or
comparisons ignore them, if a column has an index that requires
unique values, inserting into the column values that differ only
in number of trailing pad bytes will result in a duplicate-key
error. For example, if a table contains 'a'
,
an attempt to store 'a '
causes a
duplicate-key error. Trailing spaces are significant in
comparisons.
You should consider the preceding padding and stripping
characteristics carefully if you plan to use one of these data
types for storing binary data and you require that the value
retrieved be exactly the same as the value stored. The following
example illustrates how space-padding of
BINARY
values affects column
value comparisons:
mysql>CREATE TABLE t (c BINARY(3));
Query OK, 0 rows affected (0.00 sec) mysql>INSERT INTO t SET c = 'a ';
Query OK, 1 row affected (0.00 sec) mysql>SELECT HEX(c), c = 'a', c = 'a ' from t;
+--------+---------+-----------+ | HEX(c) | c = 'a' | c = 'a ' | +--------+---------+-----------+ | 61 | 1 | 0 | +--------+---------+-----------+ 1 row in set (0.00 sec)
If the value retrieved must be the same as the value specified
for storage with no padding, it might be preferable to use one
of the BLOB
data types instead.
User Comments
When BINARY or VARBINARY values are stored, e.g. from literal strings like 'abc' or 'Hello', there is of course a character set involved. It' s the standard character set of the operating system that is used to translate each character 'a','b','c' or 'H','e','l','o' to its byte value. (Or byte values for multi-byte character sets.)
Thus, the operating system, with its standard character set, defines how characters are converted into binary values. Only there is no MySQL character set definition involved.
Add your own comment.