The BINARY and VARBINARY types are similar to the CHAR and VARCHAR types. The difference is that the BINARY and VARBINARY types store binary data instead of strings.
The BINARY and VARBINARY types use the binary character set and collation.
BINARY
The BINARY type is similar to the CHAR type, but it stores binary byte strings. The syntax is as follows:
BINARY[(M)]
The parameter M is optional. It indicates the length of the column in bytes. If you do not specify M, the default value 1 is used.
The maximum length of the BINARY type is the same as that of the CHAR type, except that the former uses bytes as the unit. When strict SQL mode is not enabled, if the value assigned to a BINARY column exceeds the maximum length of the column, the excess part of the value is truncated, and an alert is generated.
When BINARY values are stored, the values are right-padded with 0x00 to the specified length. If an inserted value ends with 0x00, the trailing bytes for padding are also retained to ensure the retrieval performance. 0x00 and spaces are different in a byte comparison, such as ORDER BY or DISTINCT. 0x00 is sorted before spaces.
VARBINARY
The VARBINARY type is similar to the VARCHAR type, but it stores binary byte strings. The syntax is as follows:
VARBINARY(M)
Here, M indicates the maximum column length in bytes.
The maximum length of the VARBINARY type is the same as that of the VARCHAR type, except that the former uses bytes as the unit. When strict SQL mode is not enabled, if the value assigned to a VARBINARY column exceeds the maximum length of the column, the excess part of the value is truncated, and an alert is generated.
When data is inserted into a VARBINARY column, the inserted data is not padded, and no tailing bytes are deleted. 0x00 and spaces are different in a byte comparison, such as ORDER BY or DISTINCT. 0x00 is sorted before spaces.