🎃

MySQLのバイナリデータ型 BINARY, VARBINARY 詳解

2023/09/03に公開

研修医をしているgorogoroumaruと申します。
普段は病院で働いていますが、最近ソフトウェアエンジニアへの転職活動を始めました。転職オファー等あればいつでもご連絡ください。

この記事の対象者

  • MySQLのデータ型を知ってdisk効率の良いスキーマ設計をしたい方
  • MySQLで各データ型がどのようにdisk上に格納されているか興味のある方

MySQLのバイナリデータ型

BINARYおよびVARBINARY列はバイナリデータを格納するデータ型です。文字列ではなくバイナリデータを格納するという以外はCHAR,VARCHARとほぼ同じです。

BINARY列は固定長列であり、VARBINARY列は可変長列です。

BINARY型

BINARYは固定長のバイナリデータを格納するデータ型です。カラムを定義する際に格納するバイナリデータのサイズを指定する必要があります。
保存できるバイナリデータの長さは0バイト以上255バイト以下の範囲となっています。
CHAR型と同様に格納時に必ず指定された長さになるように右側が0x00でパディングされます。

VARBINARY型

VARBINARYは可変長のバイナリデータを格納することができるデータ型です。カラムを定義する際に格納するバイナリデータのサイズを指定する必要があります。
指定できる文字数は0バイト以上65,535バイト以下となっています。
必要なスペースの量は、カラムに格納されるデータのサイズに依存します。

BINARYとVARBINARYのデータサイズ

BINARYは、カラム定義時に指定した文字数がストレージ内のデータサイズと一致します。

一方、VARBINARYは可変長であるため、バイナリデータの長さを格納するためのプレフィックスが必要であり、データの長さに応じて追加バイト数が変動します。

CHARやVARCHARとは異なり、BINARY,VARBINARYはCharacter Setに応じたエンコードが必要ありません。そのためバイナリデータを格納する際に長さ1あたり常に1バイトしか消費せず、ディスク効率がCHARやVARCHARよりも優れています。

VARBINARYの長さprefixのデータサイズ

データ長 追加サイズ
256未満 1バイト
256以上 2バイト

VARBINARYで指定した長さが2^8=256未満であれば長さprefixは1バイトに収まり、それ以上ならば長さprefixは2バイトとなります。

BINARYとVARBINARYのユースケース

バイナリデータをCHARやVARCHARに格納することは可能ですが、その際には数値を特定のCharacter Setに合わせてエンコードする必要があり、結果として追加のディスク領域が必要になります。
したがって、文字列表現を持たないバイナリデータを保存するには、BINARYやVARBINARYを使用することが適しています。

実際にはBINARYデータ型は、ハッシュ値(MD5、SHA-256など)やUUIDなどの固定長のバイナリデータを格納する場合によく使用されます。
一方、VARBINARYデータ型は、可変サイズのバイナリデータ(例: ファイルのバイナリコンテンツ)を格納する際に利用されます。

まとめ

MySQLのBINARYおよびVARBINARY列は、有効な文字列表現を持たないバイナリデータを効率的に保存する方法を提供します。
バイナリデータを扱う際はCHARやVARCHARではなく、BINARY,VARBINARYを使用することが推奨されます。

参考資料

https://planetscale.com/learn/courses/mysql-for-developers/schema/binary-strings
https://dev.mysql.com/doc/refman/8.0/ja/binary-varbinary.html

関連リンク

MySQLの整数値型 詳解
MySQLの小数値型 詳解
MySQLの文字列型 CHAR,VARCHAR 詳解
MySQLのTEXT、BLOB 詳解
MySQLのENUM型 詳解
MySQLの時間データ型 詳解
MySQLのJSON型 詳解
MySQL - innodbにおける各データ型のサイズまとめ

Discussion