BINARY 和 VARBINARY 类型类似于 CHAR 和 VARCHAR，只不过它们存储的是二进制字符串而不是非二进制字符串。也就是说，它们存储的是字节字符串而不是字符字符串。这意味着它们具有 binary 字符集和校对规则，比较和排序是基于值中的字节的数值。The BINARY and VARBINARY types are similar to CHAR and VARCHAR, except that they store binary strings rather than nonbinary strings. That is, they store byte strings rather than character strings. This means they have the binary character set and collation, and comparison and sorting are based on the numeric values of the bytes in the values.

允许的最大长度对于 BINARY 和 VARBINARY 与 CHAR 和 VARCHAR 相同，只是 BINARY 和 VARBINARY 的长度以字节为单位，而不是字符。The permissible 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 measured in bytes rather than characters.

该BINARY以及VARBINARY data types are distinct from the CHAR BINARY以及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 (_bin) collation for the column character set (or the table default character set if no column character set is specified) to be used, and the column itself stores nonbinary character strings rather than binary byte strings. For example, if the default character set is utf8mb4, CHAR(5) BINARY is treated as CHAR(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin. This differs from BINARY(5), which stores 5-byte binary strings that have the binary character set and collation. For information about the differences between the binary字符集的排序规则与非二进制字符集的binary排序规则之间差异的信息，请参见_bin第 12.8.5 节，“二进制排序规则与_bin 排序规则的比较”一些条件适用于所有空间类型转换，某些条件仅适用于转换结果为特定空间类型时。有关诸如.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 (_bin) collation for the column character set (or the table default character set if no column character set is specified) to be used, and the column itself stores nonbinary character strings rather than binary byte strings. For example, if the default character set is utf8mb4, CHAR(5) BINARY is treated as CHAR(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin. This differs from BINARY(5), which stores 5-byte binary strings that have the binary character set and collation. For information about the differences between the binary collation of the binary character set and the _bin collations of nonbinary character sets, see Section 12.8.5, “The binary Collation Compared to _bin Collations”.

如果没有启用严格 SQL 模式，并且你向 BINARY 或 VARBINARY 列分配一个超过列最大长度的值，该值将被截断以适应列长度，并生成警告。对于截断情况，要使其发生错误（而不是警告）并阻止插入该值，请使用严格 SQL 模式。参见 第 7.1.11 节，“服务器 SQL 模式”。If strict SQL mode is not enabled and you assign a value to a BINARY or VARBINARY column that exceeds the column's maximum length, the value is truncated to fit and a warning is generated. For cases of truncation, to cause an error to occur (rather than a warning) and suppress insertion of the value, use strict SQL mode. See Section 7.1.11, “Server SQL Modes”.

当存储 BINARY 值时，它们会使用填充值在右侧填充到指定长度。填充值是 0x00（零字节）。插入时值会在右侧用 0x00 填充，检索时不会移除尾部字节。比较时所有字节都具有意义，包括 ORDER BY 和 DISTINCT 操作。0x00 和空格在比较中不同，0x00 排在空格之前。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 for inserts, and no trailing bytes are removed for retrievals. All bytes are significant in comparisons, including ORDER BY and DISTINCT operations. 0x00 and space differ in comparisons, with 0x00 sorting before space.

示例：对于 BINARY(3) 列，'a ' 插入时会变为 'a \0'。'a\0' 插入时会变为 'a\0\0'。插入的两个值在检索时保持不变。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 for retrievals.

对于 VARBINARY，插入时没有填充，检索时也不会去掉字节。所有字节在比较中都是重要的，包括 ORDER BY 和 DISTINCT 操作。0x00 和空格在比较中是不同的，0x00 排在空格之前。For VARBINARY, there is no padding for inserts and no bytes are stripped for retrievals. All bytes are significant in comparisons, including ORDER BY and DISTINCT operations. 0x00 and space differ in comparisons, with 0x00 sorting before space.

对于那些去掉尾部填充字节或比较时忽略它们的情况，如果某列有需要唯一值的索引，将仅在尾部填充字节数量上不同的值插入该列会导致重复键错误。例如，如果一个表包含 'a'，尝试存储 'a\0' 会导致重复键错误。For those cases where trailing pad bytes are stripped or comparisons ignore them, if a column has an index that requires unique values, inserting values into the column that differ only in number of trailing pad bytes results in a duplicate-key error. For example, if a table contains 'a', an attempt to store 'a\0' causes a duplicate-key error.

如果你打算使用 BINARY 数据类型来存储二进制数据，并且要求检索到的值与存储的值完全相同，则应仔细考虑前面的填充和去除特性。下面的示例说明了 BINARY 值的 0x00 填充如何影响列值比较：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)

如果检索到的值必须与存储时指定的值完全相同且不带填充，则可能更适合使用 VARBINARY 或 BLOB 数据类型之一。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.