第41回では、CHAR型とVARCHAR型の違いについて簡単に説明を行いました。しかし、MySQLにはそれ以外にも文字列を扱うことができる型が複数あります。そこで今回はMySQLで扱える文字列型について紹介していきます。
検証環境
今回使用した環境は、CentOS7.1上にyumコマンドを利用してインストールしたMySQLの5.7.18上で動作検証を行っています。第41回と同様に、今回使う検証用のデータベースを作成しました。
MySQLの文字列型
MySQLには大きく分けて、文字列を扱える以下の8つの型が存在しています。
- CHAR
- VARCHAR
- BINARY
- VARBINARY
- BLOB
- TEXT
- ENUM
- SET
続いて各型の紹介をしていきます。CHAR型、VARCHAR型に関しては以前紹介させていただいたので割愛させていただきます。
BINARY型とVARBINARY型
BINARY型とVARBINARY型の関係はCHAR型とVARCHAR型の関係と非常によく似ています。BINARY型はデータを固定長で扱い、VARBINARY型はデータを可変長で扱います。そのためBINARY型ではパディングの処理に関してもCHAR型同様に注意する必要があります。
同様に確認してみましょう。まずは固定長のBINARYから確認します。以下のように長さが4のBINARY型のテーブルを用意します。続けて確認用に末尾にスペースがないa b
と末尾にスペースがあるa b
をINSERTします。
どのように格納されているか、CONCAT構文を利用して確認してみましょう。
CHAR型の時とは逆に、末尾にスペース(本当は0x00)が追加されていることが確認できます。
続けてVARBINARY型を見てみましょう。同様に型を作成し、以下のINSERT文をを実行しました。
それではどのように格納されているか確認してみましょう。
こちらはVARCHAR型の時と同様に長さに合わせた値として保存されていることがわかります。
それ以外のCHAR型とVARCHAR型との違いとしては、文字列を認識して保存するのではなく、文字列をバイト文字列として保存します。そのため文字セットの影響を受けず、ORDER BY
構文などを用いて並べ替えを行う際には、文字列の順序として並べ替えをするのではなく、バイトの文字列、つまり数字としてとしての比較を行い並べ替えます。条件句で指定をする際に、アルファベットの大文字と小文字が区別されることにも注意が必要です。他にも不正な文字列かどうかのバリデーションがチェックされません。そのためマルチバイト文字に関しては、これらの型を使用するのはやめておいたほうが良いでしょう。
BLOB型とTEXT型
BLOB(Binary Large OBjectの略)型とTEXT型は、VARBINARY型やVARCHAR型で扱うことができなかった大きなサイズのファイルや文字列を扱うために利用します。
BLOB型はTINYBLOB, BLOB, MEDIUMBLOB, LONGBLOBの4種類が存在します。またTEXT型でも同様にTINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT型の4種類が存在します。これらの違いは保存できるデータの最大長です。各型での最大長は以下の表の通りになります。
型名 | 最大長 |
TINYBLOB, TINYTEXT | 255バイト |
BLOB, TEXT | 65,535バイト |
MEDIUMBLOB, MEDIUMTEXT | 16,777,255バイト |
LONGBLOB, LONGTEXT | 4,294,967,295バイト |
BLOB型とTEXT型は、BLOB型はVARBINARY型に、TEXT型はVARCHAR型にそれぞれ非常に良く似ています。しかし、保存できるデータが大きいため、インデックスを貼る際には最初の何文字・何バイトまでを対象とするかを考慮して使う必要があります。TEXT型を例に確認してみましょう。以下のようにテーブルを作成します。
続けて、textテーブルのtextカラムにインデックスをそのまま貼ってみるとどうなるか確認してみましょう。
BLOB/TEXTのカラムは長さを指定しましょうというエラーが発生しています。次は長さに制約を付けてみましょう。以下では10文字という制限を付けてみました。カラム名の後ろの()でくくられた部分が長さを示しています。
無事作成できました。このように、TEXT型やBLOB型のカラムに対してインデックスを張りたい場合は長さに注意が必要です。また、特にTEXT型のカラムにマルチバイト文字を使用した場合は、その分長さが減ることにも注意しましょう。
一般的にこれらの型を使用する際には、保管するデータは非常に大きなものが入っていることが多いです。そのためアクセスする際には必要に応じて、たとえばこれらの型を含むテーブルからSELECTする時に、なるべくこの型を使ったカラムを入れないようにしたほうが良いでしょう。大きなデータを扱う時には、加えてmax_allowed_packet
やinnodb_log_file_size
を十分に大きくしないとエラーが発生してしまうこともあるので注意してください。
BLOB型に画像や動画などの大きなファイルを入れたい場合は、別途MySQL以外のストレージも検討したほうが良いかもしれません。
ENUM型
ENUM型は、テーブル作成時に明示的に宣言されたデータのリストから選んだ値を保存する事ができる型です。都道府県の名前のように固定的で不変なデータを扱うのに向いています。
ENUM型のメリットとしては、2点あげられます。まず1点目としては、テーブル作成時に要素として定義された以外の値が入らないという点です。2点目としては、リストにある文字列を直接保存するのではなく、リストの何番目にあるかというデータだけを保存します。そのため、文字列を直接保存するよりもストレージを効率的に利用することができます。以下のようなテーブルを作って確認してみましょう。
続けてデータの追加を行ってみます。以下のように単純にINSERTしてSELECTしてみました。
以上から、問題なくINSERTされていることがわかります。では、ENUMの指定にない文字列をINSERTしようとするとどうなるでしょうか?
上記のようにENUM型で指定していない文字列をINSERTしようとすると、エラーになることがわかります。続けて以下のようにデータを追加してみます。
さらに、以下のような一見何も結果の返ってこなさそうなSELECT文を実行してみます。
不思議なことに「北海道」という結果が帰ってきました。何故prefecture = 1
という条件式で北海道が出てくるのかといえば、ENUM型の定義で一番目の要素が北海道になっているからです。ENUM型は数字で保管されているものの、文字列と同様に扱えるのがENUM型の利点です。
ENUM型を使ったときのデメリットとしては、宣言されたデータの追加や削除などの変更にはALTER TABLE
を行う必要があり少々大変です。また、数字を文字列としてENUM型として扱うと、以下のようにややこしくなってしまう事があるため注意が必要です。
上記で作成したようなテーブルを用意して、以下の文字列でのINSERT文と数値でのINSERT文を実行してみます。
SELECT文を実行して結果を確認してみましょう。
文字列と数値で結果が異なることになりました。これは非常にややこしく間違いやすいため、数字に対してENUM型を利用するのは難しいことを理解していただけたかと思います。
他にもORDER BY
等でこのカラムを利用して並び替えを行うと、宣言時に宣言した順にソートが行われるため、一般的なソート順序の文字列や数字の順にはならない事に注意が必要です。
numカラムを利用して昇順に並べ替えを行うと、ENUMで定義した順番で並んでいることがわかると思います。
このように使い所が難しいものの、データの圧縮に非常に効果的なので、不変なデータを扱う場合には使用してみると良いかもしれません。
SET型
SET型はテーブル作成時に設定した文字列を0個以上選択することが出来るデータ型です。検証するために以下のようなテーブルを作成します。
このテーブルのelemカラムでは、以下で挙げるNULLと空文字列と'a'と'b'と'a,b'の合わせて5種類のデータが保存されます。以下のようなINSERT文を用意して確認してみましょう。
上記から、5種類のデータが保存されていることがわかりました。一旦テーブルを削除して、追加で以下のように複数要素を同時にINSERT文を実行するとどうなるか、見てみましょう。
このように同じ要素を入力した場合や、順序を入れ替えた場合にも先ほどと同様に、5種類の値が保存されています。また、複数同じ値を利用した場合にもユニークな値が保存されていることがわかります。
ここまでSET型がどのような値の保存の仕方をしているかを解説してきました。続けてSET型を利用する方法を少し説明していきます。
SET型は用いて値を保存したカラムを利用して検索を行うのは、他の文字列型に比べ少々面倒くさいです。elemの値が複数要素で構成されている時に保存されている文字列が、(,)区切りの文字列になってしまっているため、残念ながらlike
やfind_in_set
を用いなければならないので、少し煩雑です。
次の例では'b'という要素が含まれているかという条件で検索を行っています。
このように(,)区切りで文字列を扱うのは大変なので、この型を使うのはあまりおすすめはしません。
まとめ
今回は、MySQLで扱える文字列型について簡単に紹介しました。一度決めてしまったスキーマを変更することは非常に大変ですので、それぞれにメリット、デメリットがある事を認識しましょう。そしてどの型を利用するかの判断を適切にできるようにしましょう。