MySQL道普請便り

第41回MySQLのCHAR型とVARCHAR型との違いを理解する

サービスを作成する時に、文字型を使わずに作成することはあまりないと思います。MySQLを使った場合でも、サービスを作成しようとした時には、やはり文字列型を使うことになります。その際に間違えやすいのがCHAR型とVARCHAR型です。2つの型は似た名前をしているのですが、あまり理解してないで使ってしまうと、手痛い目にあってしまうことも少なくありません。

そこで今回は、CHAR型とVARCHAR型に関して、実際にどのように違うのかを紹介していきたいと思います。

検証環境

今回使用した環境は、CentOS7.1上にyumコマンドを利用してインストールしたMySQL 5.7.17で行っています。

まず最初に、今回使う検証用のデータベースを以下のように作成しました。

mysql> CREATE DATABASE characters;
Query OK, 1 row affected (0.00 sec)
mysql> use characters
Database changed

また、サンプルとして使用しているテーブルは、全てlatin1の文字セットで確認しています。マルチバイト文字に関するお話は、最後にまとめて説明します。

CHAR型

CHAR型の特徴的な点は、値を格納された時に、もし文字列がテーブル作成時に指定された文字数よりも短かった場合、文字列の右側の末尾にスペースで補完します。そのため、この型の場合はデータは固定長で保存されます。たとえば以下のようなCREATE TABLE文を実行した場合、cカラムには4文字の長さの文字列を保存することができます。

mysql> CREATE TABLE ch(c CHAR(4)) CHARACTER SET latin1;
Query OK, 0 rows affected (0.03 sec)

また、ここで右側の末尾に付与されたスペースは、取り出す際に削除されて取り出されることを確認してみましょう。以下のように、4文字目に空白が入っているデータと空白を除去したデータをINSERTしました。

mysql> INSERT INTO ch(c) VALUES ('a b ');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO ch(c) VALUES ('a b');
Query OK, 1 row affected (0.01 sec)

続けて、chテーブルから先ほどINSERTしたデータをSELECTしたものが以下の結果になります。CONCATは文字列結合を行う関数で、今回空白が入っているかいないかがわかりにくいため使用しました。

mysql> SELECT CONCAT('(',c,')') FROM ch;
+-------------------+
| CONCAT('(',c,')') |
+-------------------+
| (a b)             |
| (a b)             |
+-------------------+
2 rows in set (0.00 sec)

上記の結果から、末尾に付いていたはずのスペースが除去されていることがわかります。どうしてこのような事になるかというと、末尾の足りない文字をスペースで埋めていることに起因しています。末尾にあるスペースがユーザのINSERTによるものなのか、足りない文字列を埋めるために追加されたものかどうかを判定する手段が無いため削られています。なので、末尾にスペースを保存する必要がある場合には使用できません。

CHAR型で指定できる長さ

CHAR型では、保存できる文字列の長さは0文字以上255文字以下の範囲で指定できます。

mysql> CREATE TABLE ch2(c CHAR(0)) CHARACTER SET latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE ch3(c CHAR(255)) CHARACTER SET latin1;
Query OK, 0 rows affected (0.01 sec)

上のように、0文字から255文字の間であればテーブルを作成できますが、以下のように256文字以上を指定した場合には、BLOG型かTEXT型を代わりに使うようにというエラーが発生しています。

mysql> CREATE TABLE ch4(c CHAR(256)) CHARACTER SET latin1;
ERROR 1074 (42000): Column length too big for column 'c' (max = 255); use BLOB or TEXT instead

この型を使う際は、ハッシュ値などの桁数が揃った文字列を効率よく保管したい場合などが良いでしょう。

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_TABLESSTRICT_ALL_TABLESをSQLモードに指定することで、無効な値をエラーにすることができます。設定が有効になっている場合は、以下のようにエラーが帰ってきます。

mysql> INSERT INTO ch(c) VALUES ('abcde');
ERROR 1406 (22001): Data too long for column 'c' at row 1

参考までに、5.5.54で実行した場合を以下に載せておきます。

mysql> INSERT INTO ch(c) VALUES ('abcde');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select c from ch;
+------+
| c    |
+------+
| abcd |
+------+
1 row in set (0.00 sec)

上記のように警告を出し、切り詰められてINSERTされていることがわかります。バージョンアップをする際には、暗黙のデフォルト値や設定のデフォルト値が変わる事に注意をしましょう。

VARCHAR型

VARCHAR型の特徴的な点として、CHAR型と違ってテーブル作成時に指定された文字列よりも短かった場合に、データに合わせた文字列として可変長で保存されます。このVARCHARでは可変長で保存するために、長さの情報を255バイトまでは1バイト、それ以上は2バイト余分に使って保存をしています。可変長で長さを保存できることから、CHAR型の様に末尾に付けた空白文字が消えることはありません。そのことを以下のように確認してみましょう。

mysql> CREATE TABLE vch(vc VARCHAR(4)) CHARACTER SET latin1;
Query OK, 0 rows affected (0.03 sec)

まずはテーブル作成を行っています。CHAR型で行ったときと同様に、vcのカラムに入る文字列は4文字という指定で行っています。CHAR型で試したときのように、4文字目に空白が入っているデータと空白を除去したデータをINSERTしました。また5文字以上の文字列をINSERTした場合VARCHAR型の場合も、CHAR型と同様に5.6以降とそれ以前で挙動が異なることに注意してください。

mysql> INSERT INTO vch(vc) VALUES ('a b ');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO vch(vc) VALUES ('a b');
Query OK, 1 row affected (0.01 sec)

続けて、vchテーブルから先程INSERTしたデータをSELECTしたものが以下の結果になります。

mysql> SELECT CONCAT('(',vc,')') FROM vch;
+--------------------+
| CONCAT('(',vc,')') |
+--------------------+
| (a b )             |
| (a b)              |
+--------------------+
2 rows in set (0.00 sec)

以上のように、末尾に保存されたスペースが残っていることがわかります。

このほか注意する点として、今回の話とは少し違いますが、検索文字列に入れた末尾の空白は無視されてしまいます。

mysql> select CONCAT('(',vc,')') from vch where vc = 'a b';
+--------------------+
| CONCAT('(',vc,')') |
+--------------------+
| (a b )             |
| (a b)              |
+--------------------+
2 rows in set (0.00 sec)

上のように、スペースが末尾にない場合と下の末尾に空白が入っているものは、比較して同じデータが返ってくることがわかると思います。

mysql> select CONCAT('(',vc,')') from vch where vc = 'a b       ';
+--------------------+
| CONCAT('(',vc,')') |
+--------------------+
| (a b )             |
| (a b)              |
+--------------------+
2 rows in set (0.00 sec)

VARCHAR型で指定できる長さ

VARCHARでも指定できる文字数は0文字以上65,535文字以下となっています。それではCHAR型のときと同様に試してみましょう。

mysql> CREATE TABLE vch2(vc VARCHAR(0)) SET latin1;
Query OK, 0 rows affected (0.01 sec)

上記の結果のように0文字の時は問題ありませんでした。それでは65,535文字を指定した場合はどうなるか確認してみましょう。

mysql> CREATE TABLE vch3(vc VARCHAR(65535)) CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

“行のサイズが長すぎます⁠というエラーが出てしまいました。CHAR型で最大値以上を指定したときと同様に、BLOBかTEXT型が推奨されています。実はこれはVARCHAR型の制約ではなくMySQLの制約で、1行あたりが65,535バイトと制限されているために発生したエラーです。では実際に幾つから使うことができるのか?という疑問が浮かぶと思いますが、NOT NULL制約が無い場合は65,532文字まで、NOT NULL制約がある場合には65,533文字まで保存することができます。

mysql> CREATE TABLE vch3(vc VARCHAR(65532)) CHARACTER SET latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE vch4(vc VARCHAR(65533) NOT NULL) CHARACTER SET latin1;
Query OK, 0 rows affected (0.01 sec)

2カラム以上で作成する場合

また今回は1カラムだけで作成していますが、当然2カラムやそれ以上になった場合でも、1行あたりの上限は65,535バイトです。そのためカラムで分割した値になります。たとえば3カラムで等分になるように作成した場合には、65,535バイトから6バイトを引いて3で割った値=21,843が以下のように使用できます。

mysql> CREATE TABLE vch5(vc1 VARCHAR(21843)  NOT NULL,vc2 VARCHAR(21843)  NOT NULL,vc3 VARCHAR(21843)  NOT NULL) CHARACTER SET latin1;
Query OK, 0 rows affected (0.01 sec)

確認のためにvc1のカラムの型の値を1増やしてみると、以下のようにエラーになることがわかります。

mysql> CREATE TABLE vch6(vc1 VARCHAR(21844)  NOT NULL,vc2 VARCHAR(21843)  NOT NULL,vc3 VARCHAR(21843)  NOT NULL) CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

これはVARCHAR型のみで起こる問題ではありませんが、コーディング中に度々出くわしてしまうことがあるので覚えておくと良いでしょう。

マルチバイト文字を使う場合

加えて注意をしなければならない事としては、今回説明に利用したlaten1はシングルバイト文字を扱う文字セットであることです。たとえば、他のutf8mb4等のマルチバイトを扱う文字セットを用いた場合はもう少し話が込み入ってきます。先ほど述べたとおり、1行あたりのバイト数の制限は65,535バイトとなります。そこからNOT NULL制約を付けてVARCHARを用いる場合、2バイト分引いた値である65,533バイトが実際に使える残りのバイト数になります。utf8mb4は4バイト文字も扱う文字セットなので、65,533を4割って端数を切り捨てた16383文字を以下のように利用することができます。

mysql> CREATE TABLE vch7(vc VARCHAR(16383) NOT NULL) CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.03 sec)

1増やして16,384文字を指定した場合を確認すると、以下のようにエラーになってしまいます。

mysql>  CREATE TABLE vch7(vc VARCHAR(16384) NOT NULL) CHARACTER SET utf8mb4;
ERROR 1074 (42000): Column length too big for column 'c' (max = 16383); use BLOB or TEXT instead

蛇足ではありますが、utf8mb4とよく似た名前にutf8という文字セットがあるのですが、こちらはutf8の3バイト文字までを扱うセットであり、4バイト文字が扱えません。そのためutf8を指定した場合VARCHAR型の上限はutf8mb4とは異なり、65,533を3で割って端数を切り捨てた21844文字を利用することができます。

つまり、MySQLの上限ギリギリまで扱えるようにバイト数を詰めていた場合には、設定をutf8からutf8mb4に変更するときには最大で約5000文字減ってしまう可能性があるという事になります。今後新しくテーブルを作成する際にはutf8ではなくutf8mb4で設計することも考慮に入れた方が良いでしょう。

まとめ

今回はMySQLのCHAR型とVARCHAR型の使い方について紹介をしました。名前が似ているにもかかわらず挙動がかなり違うので、注意が必要です。それぞれどのような挙動になるかを掴んでいただけたでしょうか。後で困ってしまう事態にならないように理解して使い分けを行っていきましょう。

おすすめ記事

記事・ニュース一覧