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.

Before MySQL 4.1.2, BINARY(M) and VARBINARY(M) are treated as CHAR(M) BINARY and VARCHAR(M) BINARY. As of MySQL 4.1.2, 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, 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.

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.