🌊

MySQLのTEXT、BLOB　詳解

2023/09/06に公開

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

TEXT型

TEXT型は、CHAR,VARCHARと同様にテキスト文字列などの文字データを保存するために使用されます。
CHARやVARCHARとは異なり、TEXT型ははるかに大きなデータ量を保存できるため、長いテキストブロックを保存するのに有用です。
MySQLには4つの種類のTEXT型があります。（TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT）
各タイプには保存できるデータ量の制限があります。
TINYTEXT型は最大255バイトまで、TEXT型は最大65535(=65K)バイトまで、MEDIUMTEXT型は最大16777215（＝16M)バイトまで、LONGTEXT型は最大4,294,967,295（＝4G）バイトまでのデータを保存できます。

BLOB型

BLOB型はBINARY,VARBINARYと同様にバイナリデータを保存するために使用されます。
BINARY、VARBINARYと比較して、BLOB型もはるかに大きなデータ量を保存できる点でTEXT型と似ています。

テキスト列と同様に、BLOB列には4つの種類があります。（TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB）
それらは保存できるデータ量が異なります。
TINYBLOB型は最大255バイトまで、BLOB型は最大65535(=65K)バイトまで、MEDIUMBLOB型は最大16777215（＝16M)バイトまで、LONGBLOB型は最大4,294,967,295（＝4G）バイトまでのデータを保存できます。

TEXT, BLOB型のデータ保存場所と保存形式

TEXT,BLOB型の保存場所、保存形式はinnodb_file_formatおよびROW_FORMATパラメータで調整できます。
innodb_file_formatパラメータの定義は以下の通りです。
innodb_file_formatの値に応じて選択できるROW_FORMATが決定されます。

ファイルフォーマット	定義	選択可能なROW_FORMAT
Antelope	MySQL5.6までのデフォルトです。可変長カラム(BLOB, TEXT)の先頭768byteをB-treeノードのインデックスレコードに格納し、残りをオーバーフローページに格納します。	REDUNDANTまたはCOMPACT
Barracuda	MySQL5.7以降のデフォルトです。行の合計サイズがクラスタ化インデックス（主キーのインデックス）のB-treeページ内に収まる場合は、そのページ内に値が保存されます。収まらない場合は、行の合計サイズがB-treeページ内に収まるまで最も長いカラムをオーバーフローページ領域として選択します。オーバーフローページへの20バイトのポインタのみを格納し、元のデータはオーバーフローページに格納します。	COMPRESSEDまたはDYNAMIC

ROW_FORMATパラメータの定義は以下の通りです。

行フォーマット	定義
REDUNDANT	Antelopeとともにのみ使用可能です。古いバージョンのMySQLと互換性のあるROW_FORMATです。データを圧縮せずに保存します。データサイズが768byteを超えるかどうかに応じてB-treeにデータが格納されるかオーバーフローページに格納されるか決まります。
COMPACT	Antelopeとともにのみ使用可能です。COMPACTでは、最適化によりREDUNDANTと比べて行のデータサイズが削減されます。データサイズが768byteを超えるかどうかに応じてB-treeにデータが格納されるかオーバーフローページに格納されるか決まります。
DYNAMIC	Barracudaとともにのみ使用可能です。上記のBarracudaの説明の通りにオーバーフローページに格納されるカラムが選択されます。オーバーフローページに保存されるカラムは圧縮せずに格納されます。
COMPRESSED	Barracudaとともにのみ使用可能です。上記のBarracudaの説明の通りにオーバーフローページに格納されるカラムが選択されます。オーバーフローページに保存されるカラムは圧縮した状態で格納されます。

TEXT, BLOB型の制約

TEXT,BLOB型は全体の値に対してインデックスを貼ることはできず、先頭の何文字かに限定してインデックスを貼らなければなりません。特にTEXT型で長さを指定したインデックスを貼る際はマルチバイト文字使用時にその分長さが減ることにも注意が必要です。
TEXT,BLOB型は全体の値でソートすることができません。ソート時には、カラムのmax_sort_length分のバイトだけが使用されます。max_sort_lengthのデフォルト値は1024です。 以下のようにmax_sort_lengthの値を増やすことによって、ソートに影響するバイトを増やすことができます。

mysql> SET max_sort_length = 2000;

TEXT, BLOB型のユースケース

TEXT型はCHAR,VARCHARで保存できないようなとても長い文字列を保存する場合にのみ使用しましょう。 TEXT型は処理効率が良くないので、小規模な文字列の場合はTEXT型の代わりにCHAR,VARCHARを使用することを検討して下さい。

BLOB型は画像や音声ファイルなどのサイズの大きなバイナリデータを保持することが可能ですが、データベースに格納することは推奨されません。
通常、この種のファイルは別の場所に保存し、その場所へのポインタをVARCHAR列に残す方が良いでしょう。

まとめ

MySQLのTEXT型とBLOB型はサイズの大きなテキストデータ、バイナリデータを保存するための便利なデータ型です。保存するデータ量に応じて様々な異なる種類のデータ型を使用することができます。
TEXT型、BLOB型はパフォーマンス上の制約があるため、むやみに使用するのではなく必要性を十分に検討することが推奨されます。