MySQL道普請便り

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

この記事を読むのに必要な時間:およそ 4.5 分

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型の使い方について紹介をしました。名前が似ているにもかかわらず挙動がかなり違うので,注意が必要です。それぞれどのような挙動になるかを掴んでいただけたでしょうか。後で困ってしまう事態にならないように理解して使い分けを行っていきましょう。

著者プロフィール

木村浩一郎(きむらこういちろう)

株式会社オプティム 技術統括本部のエンジニア。最近はミドルウェア・インフラ周りのことも少しずつ学習しています。趣味は将棋。好きな戦法は四間飛車。

Twitter:@kk2170