サービスを作成する時に、文字型を使わずに作成することはあまりないと思います。MySQLを使った場合でも、サービスを作成しようとした時には、やはり文字列型を使うことになります。その際に間違えやすいのがCHAR型とVARCHAR型です。2つの型は似た名前をしているのですが、あまり理解してないで使ってしまうと、手痛い目にあってしまうことも少なくありません。
そこで今回は、CHAR型とVARCHAR型に関して、実際にどのように違うのかを紹介していきたいと思います。
検証環境
今回使用した環境は、CentOS7.1上にyumコマンドを利用してインストールしたMySQL 5.7.17で行っています。
まず最初に、今回使う検証用のデータベースを以下のように作成しました。
また、サンプルとして使用しているテーブルは、全てlatin1の文字セットで確認しています。マルチバイト文字に関するお話は、最後にまとめて説明します。
CHAR型
CHAR型の特徴的な点は、値を格納された時に、もし文字列がテーブル作成時に指定された文字数よりも短かった場合、文字列の右側の末尾にスペースで補完します。そのため、この型の場合はデータは固定長で保存されます。たとえば以下のようなCREATE TABLE文を実行した場合、cカラムには4文字の長さの文字列を保存することができます。
また、ここで右側の末尾に付与されたスペースは、取り出す際に削除されて取り出されることを確認してみましょう。以下のように、4文字目に空白が入っているデータと空白を除去したデータをINSERTしました。
続けて、chテーブルから先ほどINSERTしたデータをSELECTしたものが以下の結果になります。CONCATは文字列結合を行う関数で、今回空白が入っているかいないかがわかりにくいため使用しました。
上記の結果から、末尾に付いていたはずのスペースが除去されていることがわかります。どうしてこのような事になるかというと、末尾の足りない文字をスペースで埋めていることに起因しています。末尾にあるスペースがユーザのINSERTによるものなのか、足りない文字列を埋めるために追加されたものかどうかを判定する手段が無いため削られています。なので、末尾にスペースを保存する必要がある場合には使用できません。
CHAR型で指定できる長さ
CHAR型では、保存できる文字列の長さは0文字以上255文字以下の範囲で指定できます。
上のように、0文字から255文字の間であればテーブルを作成できますが、以下のように256文字以上を指定した場合には、BLOG型かTEXT型を代わりに使うようにというエラーが発生しています。
この型を使う際は、ハッシュ値などの桁数が揃った文字列を効率よく保管したい場合などが良いでしょう。
SQLモードの違い
今回の説明では、VARCHAR(4)に対して4文字までの文字をINSERTしていましたが、5文字以上の文字列をINSERTした場合どのような結果になるかを軽く説明していきたいと思います。今回使用したバージョンでは以下のようにエラーになります。しかし、利用したバージョンや設定によって結果が変わります。
原因は、バージョンによってSQLモードの暗黙のデフォルト値や設定ファイルにデフォルトの設定が違うためです。5.5系は暗黙のデフォルト値も設定ファイルのデフォルト値もstrictモードではないため、警告が出て切り詰めてINSERTされています。
5.6系からは暗黙のデフォルト値はstrictモードではありませんが、my.cnfファイル内にSQLモードが設定されているため、エラーになるように変更されました。
5.7からは暗黙のデフォルト値がstrictモードになっているため、設定ファイルには何も書かれていませんがエラーになります。また、5.5を使っている場合でも設定を行いたい場合はSTRICT_TRANS_TABLES
やSTRICT_ALL_TABLES
をSQLモードに指定することで、無効な値をエラーにすることができます。設定が有効になっている場合は、以下のようにエラーが帰ってきます。
参考までに、5.5.54で実行した場合を以下に載せておきます。
上記のように警告を出し、切り詰められてINSERTされていることがわかります。バージョンアップをする際には、暗黙のデフォルト値や設定のデフォルト値が変わる事に注意をしましょう。
VARCHAR型
VARCHAR型の特徴的な点として、CHAR型と違ってテーブル作成時に指定された文字列よりも短かった場合に、データに合わせた文字列として可変長で保存されます。このVARCHARでは可変長で保存するために、長さの情報を255バイトまでは1バイト、それ以上は2バイト余分に使って保存をしています。可変長で長さを保存できることから、CHAR型の様に末尾に付けた空白文字が消えることはありません。そのことを以下のように確認してみましょう。
まずはテーブル作成を行っています。CHAR型で行ったときと同様に、vcのカラムに入る文字列は4文字という指定で行っています。CHAR型で試したときのように、4文字目に空白が入っているデータと空白を除去したデータをINSERTしました。また5文字以上の文字列をINSERTした場合VARCHAR型の場合も、CHAR型と同様に5.6以降とそれ以前で挙動が異なることに注意してください。
続けて、vchテーブルから先程INSERTしたデータをSELECTしたものが以下の結果になります。
以上のように、末尾に保存されたスペースが残っていることがわかります。
このほか注意する点として、今回の話とは少し違いますが、検索文字列に入れた末尾の空白は無視されてしまいます。
上のように、スペースが末尾にない場合と下の末尾に空白が入っているものは、比較して同じデータが返ってくることがわかると思います。
VARCHAR型で指定できる長さ
VARCHARでも指定できる文字数は0文字以上65,535文字以下となっています。それではCHAR型のときと同様に試してみましょう。
上記の結果のように0文字の時は問題ありませんでした。それでは65,535文字を指定した場合はどうなるか確認してみましょう。
“行のサイズが長すぎます”というエラーが出てしまいました。CHAR型で最大値以上を指定したときと同様に、BLOBかTEXT型が推奨されています。実はこれはVARCHAR型の制約ではなくMySQLの制約で、1行あたりが65,535バイトと制限されているために発生したエラーです。では実際に幾つから使うことができるのか?という疑問が浮かぶと思いますが、NOT NULL制約が無い場合は65,532文字まで、NOT NULL制約がある場合には65,533文字まで保存することができます。
2カラム以上で作成する場合
また今回は1カラムだけで作成していますが、当然2カラムやそれ以上になった場合でも、1行あたりの上限は65,535バイトです。そのためカラムで分割した値になります。たとえば3カラムで等分になるように作成した場合には、65,535バイトから6バイトを引いて3で割った値=21,843が以下のように使用できます。
確認のためにvc1のカラムの型の値を1増やしてみると、以下のようにエラーになることがわかります。
これはVARCHAR型のみで起こる問題ではありませんが、コーディング中に度々出くわしてしまうことがあるので覚えておくと良いでしょう。
マルチバイト文字を使う場合
加えて注意をしなければならない事としては、今回説明に利用したlaten1はシングルバイト文字を扱う文字セットであることです。たとえば、他のutf8mb4等のマルチバイトを扱う文字セットを用いた場合はもう少し話が込み入ってきます。先ほど述べたとおり、1行あたりのバイト数の制限は65,535バイトとなります。そこからNOT NULL制約を付けてVARCHARを用いる場合、2バイト分引いた値である65,533バイトが実際に使える残りのバイト数になります。utf8mb4は4バイト文字も扱う文字セットなので、65,533を4割って端数を切り捨てた16383文字を以下のように利用することができます。
1増やして16,384文字を指定した場合を確認すると、以下のようにエラーになってしまいます。
蛇足ではありますが、utf8mb4とよく似た名前にutf8という文字セットがあるのですが、こちらはutf8の3バイト文字までを扱うセットであり、4バイト文字が扱えません。そのためutf8を指定した場合VARCHAR型の上限はutf8mb4とは異なり、65,533を3で割って端数を切り捨てた21844文字を利用することができます。
つまり、MySQLの上限ギリギリまで扱えるようにバイト数を詰めていた場合には、設定をutf8からutf8mb4に変更するときには最大で約5000文字減ってしまう可能性があるという事になります。今後新しくテーブルを作成する際にはutf8ではなくutf8mb4で設計することも考慮に入れた方が良いでしょう。
まとめ
今回はMySQLのCHAR型とVARCHAR型の使い方について紹介をしました。名前が似ているにもかかわらず挙動がかなり違うので、注意が必要です。それぞれどのような挙動になるかを掴んでいただけたでしょうか。後で困ってしまう事態にならないように理解して使い分けを行っていきましょう。