11.4.2. The BINARY and VARBINARY Types
The BINARY and VARBINARY
types are similar to CHAR and
VARCHAR, except that they contain binary
strings rather than non-binary 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.
The BINARY 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 non-binary character
strings rather than binary byte strings. For example,
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.
When BINARY values are stored, they are
right-padded with the pad value to the specified length. The pad
value is 0x00 (the zero byte). Values are
right-padded with 0x00 on insert, and no
trailing bytes are removed on select. All bytes are significant
in comparisons, including ORDER BY and
DISTINCT operations. 0x00
bytes and spaces are different in comparisons, with
0x00 < space.
Example: For a BINARY(3) column,
'a ' becomes
'a \0' when inserted.
'a\0' becomes 'a\0\0' when
inserted. Both inserted values remain unchanged when selected.
For VARBINARY, there is no padding on insert
and no bytes are stripped on select. All bytes are significant
in comparisons, including ORDER BY and
DISTINCT operations. 0x00
bytes and spaces are different in comparisons, with
0x00 < space.
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\0' causes a
duplicate-key error.
You should consider the preceding padding and stripping
characteristics carefully if you plan to use the
BINARY data type for storing binary data and
you require that the value retrieved be exactly the same as the
value stored. The following example illustrates how
0x00-padding of BINARY
values affects column value comparisons:
mysql> CREATE TABLE t (c BINARY(3));
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO t SET c = 'a';
Query OK, 1 row affected (0.01 sec)
mysql> SELECT HEX(c), c = 'a', c = 'a\0\0' from t;
+--------+---------+-------------+
| HEX(c) | c = 'a' | c = 'a\0\0' |
+--------+---------+-------------+
| 610000 | 0 | 1 |
+--------+---------+-------------+
1 row in set (0.09 sec)
If the value retrieved must be the same as the value specified
for storage with no padding, it might be preferable to use
VARBINARY or one of the
BLOB data types instead.
