Movatterモバイル変換

TheBINARY andVARBINARY types are similar toCHAR andVARCHAR, except that they store binary strings rather than nonbinary strings. That is, they store byte strings rather than character strings. This means they have thebinary character set and collation, and comparison and sorting are based on the numeric values of the bytes in the values.

The permissible maximum length is the same forBINARY andVARBINARY as it is forCHAR andVARCHAR, except that the length forBINARY andVARBINARY is measured in bytes rather than characters.

TheBINARY andVARBINARY data types are distinct from theCHAR BINARY andVARCHAR BINARY data types. For the latter types, theBINARY 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 isutf8mb4,CHAR(5) BINARY is treated asCHAR(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin. This differs fromBINARY(5), which stores 5-byte binary strings that have thebinary character set and collation. For information about the differences between thebinary collation of thebinary character set and the_bin collations of nonbinary character sets, seeSection 12.8.5, “The binary Collation Compared to _bin Collations”.

If strict SQL mode is not enabled and you assign a value to aBINARY orVARBINARY 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. SeeSection 7.1.11, “Server SQL Modes”.

WhenBINARY values are stored, they are right-padded with the pad value to the specified length. The pad value is0x00 (the zero byte). Values are right-padded with0x00 for inserts, and no trailing bytes are removed for retrievals. All bytes are significant in comparisons, includingORDER BY andDISTINCT operations.0x00 and space differ in comparisons, with0x00 sorting before space.

Example: For aBINARY(3) column,'a ' becomes'a \0' when inserted.'a\0' becomes'a\0\0' when inserted. Both inserted values remain unchanged for retrievals.

ForVARBINARY, there is no padding for inserts and no bytes are stripped for retrievals. All bytes are significant in comparisons, includingORDER BY andDISTINCT operations.0x00 and space differ in comparisons, with0x00 sorting before 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 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.

You should consider the preceding padding and stripping characteristics carefully if you plan to use theBINARY 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 how0x00-padding ofBINARY 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 useVARBINARY or one of theBLOB data types instead.