📕

MySQLにおける非バイナリ文字列データ型についてまとめる

2024/07/28に公開

2件

はじめに

こんにちは、おっちーです。
業務ではMySQLを用いてバックエンドの開発を行っているのですが、文字列データ型についてあまり何も考えずに使用していました。
具体的にいうと、255文字以内の文字列を保存したいならVARCHAR型を使って、それ以上の文字列を保存したいならTEXT型を使うといった、何とも浅はかな使い分けです😅
これではまずいと思い、今回文字列のデータ型についての概要とその使い分けについてまとめてみることにしました。

あまり考えずに文字列データ型を使ってるよ、って人は当記事を読んでいただけると幸いです！

文字列データ型の種類

今回は、非バイナリ文字列（一般的なテキストデータ）に絞って解説していきます。

大きく分けて、CHAR型・VARCHAR型・TEXT型が存在します。
（今回の説明では、LONGTEXT型とTINYTEXT型を除外しています。）
簡潔にまとめると、以下のような特徴があります。

データ型	長さ	サイズ	特徴
CHAR型	固定長	0〜255文字	データの実体を保存
VARCHAR型	可変長	0〜65,535バイト	データの実体を保存
TEXT型	可変長	0〜65,535バイト	データの実体への参照を保存

CHAR型

CHAR型を使用した場合、固定長の文字列として保存されます。
保存できる文字列の長さは0文字以上255文字以下の範囲で指定できます。
これはどういうことかというと、
もし格納された文字列がテーブル作成時に指定された文字数よりも短かった場合、文字列の右側の末尾にスペースで補完されるということです。
例えば、以下のようなchar_testsテーブルがあった時に、char_columnカラムに'a b'を保存しようとすると、DBには'a b 'のように保存されます。

CREATE TABLE char_tests(char_column CHAR(4)) CHARACTER SET latin1;
INSERT INTO char_tests(char_column) VALUES ('a b');

VARCHAR型

VARCHAR型を使用した場合、可変長の文字列として保存されます。
先ほどのchar_testsテーブルの例で言うと、char_columnカラムに'a b'を保存しようとすると、DBには'a b'のように、そのままの文字列が保存されます。
また、VARCHARでは可変長で保存するために、長さの情報を255文字までは1バイト、それ以上は2バイト余分に使って保存をしています。
指定できる文字数は0文字以上65,535文字以下となっています。
ただし、MySQLにおける行サイズの制限が65,535byteであるため、このバイトを超えてしまうと保存できないです。
（デフォルトのMySQL8.0の設定だと厳密なSQLモードがオンになっているため、エラーになってしまいます。）

Reference: https://dev.mysql.com/doc/refman/8.0/ja/column-count-limit.html
ストレージエンジンがより大きな行をサポートできる場合でも、MySQL テーブルの内部表現の最大行サイズは 65,535 バイトです。

TEXT型

CHAR型・VARCHAR型とは異なり、
行データとしてはデータの実体への参照が保存され、データの実体は行の他の部分とは別に格納されます。
（データの実体への参照は、メモリで言うポインタのようなものをイメージすると良いと思います。）
したがって、行サイズの制限にはあまり影響しない一方で、余分なディスクI/Oが発生するためReadやWriteのクエリが遅くなる可能性があります。

Reference: https://dev.mysql.com/doc/refman/8.0/ja/column-count-limit.html#:~:text=されます。-,行サイズ制限,-特定のテーブル
BLOB および TEXT のカラムは、行サイズ制限に 9 から 12 バイトのみ寄与します。これは、その内容が行の他の部分とは別に格納されるためです。

注意点や共通点

ここまでCHAR型・VARCHAR型・TEXT型について概要を説明してきたので、
ここからは実際に使用する際の注意点や共通点を説明します。

CHAR型とVARCHAR型の共通点

データの実体を行データとして保存します。
したがって、行（1レコード単位）のサイズ制限を超えないように気をつける必要があります。

データのバイト数に気をつける

VARCHAR型とTEXT型どちらに関しても、保存するデータのバイト数が大きくなる可能性があるので、以下の観点で気をつける必要があるかなと思います。

行サイズを超えないようにする

MySQLにおける行サイズの制限が65,535byteであるので、これを超えないようにテーブル設計する必要があります。
こちらは行データに実体を保存するVARCHAR型のみ気をつける必要があります。
後述の「文字コードの違いによるバイト数に注意する」でも述べますが、文字数 = バイト数ではないことに注意です。

max_sort_lengthを超えるとソートまたはグループ化に影響することに気をつける

max_sort_lengthを超えるとソートまたはグループ化に影響することに気をつける必要があります。
具体的にいうと、ORDER BYはもちろん、暗黙的にソートが発生するクエリ（GROUP BYなど）には注意です。

Reference: https://dev.mysql.com/doc/refman/8.0/ja/blob.html
ソート時には、カラムの max_sort_length バイトだけが使用されます。 max_sort_length のデフォルト値は 1024 です。サーバーの起動時または実行時に、max_sort_length の値を増やすことによって、ソートまたはグループ化に影響するバイトを増やすことができます。すべてのクライアントで max_sort_length セッション変数の値を変更できます。

クライアントとサーバー間で実際に送信できる最大値に気をつける

クライアントとサーバー間で実際に送信できる最大値に気をつける必要があります。

Reference: https://dev.mysql.com/doc/refman/8.0/ja/blob.html
BLOB または TEXT オブジェクトの最大サイズはその型で決まりますが、クライアントとサーバー間で実際に送信できる最大値は、使用可能なメモリーの容量と通信バッファーのサイズで決まります。 max_allowed_packet 変数の値を変更することでメッセージバッファーサイズを変更できますが、サーバーとクライアントプログラムの両方で変更する必要があります。

インデックスの最大キー長を超えないようにする

インデックスを貼る場合はデータの実体に対して貼るため、
インデックスを貼る場合はインデックスの最大キー長を超えないようにする必要があるでしょう。
最大キー長を超えている場合は、必要に応じてインデックスの長さを指定するようにしましょう。

CREATE TABLE example (
    id INT NOT NULL AUTO_INCREMENT,
    content TEXT,
    PRIMARY KEY (id),
    INDEX (content(500))
);

Reference: https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0032
基本的には単一カラムインデックスの最大キー長は767バイトまで作成できます。特定の条件ではインデックスの最大キー長を3072バイトまで拡張することができます。

文字コードの違いによるバイト数に注意する

文字コードにより1文字に対して使用する可能性のある最大バイト数が変わることに注意しましょう。
たとえば、文字コードがutf8の場合は1文字あたり最大で3バイト使用し、utf8mb4の場合は１文字あたり最大で4バイト使用します。

Reference: https://dev.mysql.com/doc/refman/8.0/ja/charset-unicode-conversion.html
utf8mb3 では、文字当たり最大 3 バイトが使用されます。utf8mb4 では、文字当たり最大 4 バイトが使用されます。

各データ型の使い分け（In My Opinion）

ここからは私の意見になりますが、各データ型の使い分けを述べていこうと思います！

CHAR型の使いどころ

固定長の文字列データを保存する場合に使用します。たとえば、郵便番号や国コードなど、長さが一定の文字列データに適しています。
CHAR型の場合、文字列の長さを保存しておく必要がないこともいい点です。

VARCHAR型の使いどころ

可変長の文字列データを保存する場合に使用します。たとえば、ユーザー名やメールアドレスなど、長さが変動する文字列データに適しています。
また、可変長のため、固定長の文字列と比較して、データの無駄なスペースを削減できる点が優れています。
ある程度のバイト数になりそうな文字列であっても、高速に書き込み・取り出しを行いたい場合はVARCHAR型を使用するのも悪くない選択肢だと思います。

TEXT型の使いどころ

ある程度のバイト数になりそうな文字列を保存したいかつ、行サイズを節約したい時に使用すると有効そうです。
ただ、65,535バイトを超えるような場合は、NoSQLデータベースやオブジェクト型のストレージといった場所に格納することを検討した方が良さそうです。

最後に

今回はMySQLにおける非バイナリ文字列のデータ型についてまとめてみました！
次回はバイナリ文字列のデータ型や、リスト構造を持つENUMやSETといったデータ型についても解説する記事を投稿しようと思っています。
投稿した際はお読みいただけますと幸いです。

参考文献

Discussion

もりた

ワンテーマでとてもよくまとまってて読みやすかったです！！！　いい記事でした！！　ありがとうございます！！

おっちー(O.S)

ありがとうございます！
そう言っていただけるとモチベーションになります！
次回作ができた際にも読んでいただけると嬉しいです！ 😄