MySQL 5.6 リファレンスマニュアル  /  ...  /  BINARY および VARBINARY 型


BINARY および VARBINARY 型は、CHAR および VARCHAR 型に似ていますが、非バイナリ文字列ではなく、バイナリ文字列を格納します。つまり、それらには文字の文字列ではなく、バイトの文字列が含まれています。これは、それらに文字セットがなく、ソートおよび比較は値の中のバイトの数値に基づいていることを意味します。

BINARY および VARBINARY で許可される最大長は、CHAR および VARCHAR の場合と同じですが、BINARY および VARBINARY の長さが文字数ではなくバイト数で表される点が異なります。

BINARY および VARBINARY データ型は CHAR BINARY および VARCHAR BINARY データ型とは異なります。後者の型は、BINARY 属性によってカラムがバイナリ文字列カラムとして扱われることはありません。その代わり、これによってカラムの文字セットのバイナリ照合順序が使用され、カラム自体にはバイナリバイト文字列ではなく非バイナリ文字列が格納されます。たとえば、CHAR(5) BINARY は、デフォルト文字セットが latin1 とすれば、CHAR(5) CHARACTER SET latin1 COLLATE latin1_bin として扱われます。これは、文字セットや照合順序を持たない 5 バイトのバイナリ文字列を格納する BINARY(5) とは異なります。非バイナリ文字列のバイナリ照合順序とバイナリ文字列の違いについては、セクション10.1.7.6「_bin および binary 照合順序」を参照してください。

厳密な SQL モードが有効でない場合に、BINARY または VARBINARY カラムにその最大長を超える値を割り当てると、その値はカラムの最大長に合わせて切り捨てられ、警告メッセージが表示されます。値を切り捨てる場合、厳密な SQL モードを使用することで、警告ではなくエラーを発生させて、その値の挿入を抑制できます。セクション5.1.7「サーバー SQL モード」を参照してください。

BINARY 値は格納されると、特定の長さまで右側がパッド値で埋められます。パッド値は 0x00 (ゼロバイト) です。値は挿入時には右側が 0x00 で埋められ、選択時に後続のバイトは削除されません。すべてのバイトは、ORDER BY および DISTINCT 操作を含め比較で意味があります。0x00 バイトとスペースは比較では異なり、0x00 < スペースです。

例: BINARY(3) カラムの場合、'a ' は挿入時に 'a \0' になります。'a\0' は挿入時に 'a\0\0' になります。選択時、挿入された両方の値は変更されません。

VARBINARY では、挿入時にパディングされることも、選択時にバイトが削除されることもありません。すべてのバイトは、ORDER BY および DISTINCT 操作を含め比較で意味があります。0x00 バイトとスペースは比較では異なり、0x00 < スペースです。

後続のパッドバイトが取り除かれたり、比較で無視されたりする場合では、一意の値を必要とするインデックスがカラムに含まれていれば、後続のパッドバイトの個数だけが異なるカラム値への挿入は、重複キーエラーになります。たとえば、テーブルに 'a' が含まれている場合、'a\0' を格納しようとすると、重複キーエラーが発生します。

バイナリデータの格納に BINARY データ型を使用する予定であり、取り出した値を格納した値とまったく同じにする必要がある場合は、先行のパディングと削除文字を考慮する必要があります。次の例は、BINARY 値の 0x00 パディングによって、カラム値の比較がどのような影響を受けるかについて示しています。

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 データ型を使用することをお勧めします。

Download this Manual
EPUB - 7.5Mb
HTML Download (TGZ) - 7.1Mb
HTML Download (Zip) - 7.2Mb
User Comments
  Posted by Andreas Krueger on April 4, 2006
When BINARY or VARBINARY values are stored, e.g. from literal strings like 'abc' or 'Hello', there is of course a character set involved. It' s the standard character set of the operating system that is used to translate each character 'a','b','c' or 'H','e','l','o' to its byte value. (Or byte values for multi-byte character sets.)
Thus, the operating system, with its standard character set, defines how characters are converted into binary values. Only there is no MySQL character set definition involved.
  Posted by Rustam Abdullaev on May 19, 2011
To escape binary values, MySQL supports the following formats: X'val', x'val', or 0xval. The former is SQL standard, the latter is ODBC standard.
mysql> SELECT x'4D7953514C';
-> 'MySQL'
mysql> SELECT 0x4D7953514C;
-> 'MySQL'

  Posted by Xavier P on August 25, 2012
@Andreas, what it means that a value "has no character set" is that what you describe breaks down if the operating system has a different charset when inserting and when querying. If one user inserts text as binary and he's using for example ASCII, then another user reads the binary as text but he's using utf-16, he'll read a different text.

When you use char types the server takes care of things so that anyone will read the same text that was inserted, regardless of everyone's local encoding settings.
  Posted by Ben Griffin on March 25, 2013
I'm not really sure that the documentation explains whether the binary column is indexed as a number or as a binarystring. This becomes relevant regarding where the LSB is in UUID fields. Typically with sequences (eg auto-increment) it's the least significant value which changes most frequently, whereas with UUID() I notice that it is the most significant value which changes most frequently. Which generates the most efficient indices?

In the end, I guess I am wondering if it would be more efficient to index the reverse of the UUID, or leave it as it is?

So the options I'm interested in are as follows (assumption is that a primary field of BINARY(16) is populated with the function):

CREATE FUNCTION id() RETURNS binary(16) RETURN unhex(REPLACE(UUID(),'-',''));


CREATE FUNCTION id() RETURNS binary(16) RETURN unhex(reverse(REPLACE(UUID(),'-','')));

Sign Up Login You must be logged in to post a comment.