MySQL道普請便り

第44回MySQLで扱える文字列型の種類を理解する

第41回では、CHAR型とVARCHAR型の違いについて簡単に説明を行いました。しかし、MySQLにはそれ以外にも文字列を扱うことができる型が複数あります。そこで今回はMySQLで扱える文字列型について紹介していきます。

検証環境

今回使用した環境は、CentOS7.1上にyumコマンドを利用してインストールしたMySQLの5.7.18上で動作検証を行っています。第41回と同様に、今回使う検証用のデータベースを作成しました。

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

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します。

mysql> CREATE TABLE bin(bin BINARY(4));
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO bin(bin) VALUES ('a b ');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO bin(bin) VALUES ('a b');
Query OK, 1 row affected (0.00 sec)

どのように格納されているか、CONCAT構文を利用して確認してみましょう。

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

CHAR型の時とは逆に、末尾にスペース(本当は0x00)が追加されていることが確認できます。

続けてVARBINARY型を見てみましょう。同様に型を作成し、以下のINSERT文をを実行しました。

mysql> CREATE TABLE varbin(bin VARBINARY(4));
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO varbin(bin) VALUES ('a b');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO varbin(bin) VALUES ('a b ');
Query OK, 1 row affected (0.00 sec)

それではどのように格納されているか確認してみましょう。

mysql> select CONCAT('(',bin,')') from varbin;
+---------------------+
| CONCAT('(',bin,')') |
+---------------------+
| (a b)               |
| (a b )              |
+---------------------+
2 rows in set (0.00 sec)

こちらは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, TINYTEXT255バイト
BLOB, TEXT65,535バイト
MEDIUMBLOB, MEDIUMTEXT16,777,255バイト
LONGBLOB, LONGTEXT4,294,967,295バイト

BLOB型とTEXT型は、BLOB型はVARBINARY型に、TEXT型はVARCHAR型にそれぞれ非常に良く似ています。しかし、保存できるデータが大きいため、インデックスを貼る際には最初の何文字・何バイトまでを対象とするかを考慮して使う必要があります。TEXT型を例に確認してみましょう。以下のようにテーブルを作成します。

mysql> CREATE TABLE text(text TEXT);
Query OK, 0 rows affected (0.01 sec)

続けて、textテーブルのtextカラムにインデックスをそのまま貼ってみるとどうなるか確認してみましょう。

mysql> ALTER TABLE text ADD INDEX index_text(text);
ERROR 1170 (42000): BLOB/TEXT column 'text' used in key specification without a key length

BLOB/TEXTのカラムは長さを指定しましょうというエラーが発生しています。次は長さに制約を付けてみましょう。以下では10文字という制限を付けてみました。カラム名の後ろの()でくくられた部分が長さを示しています。

mysql> ALTER TABLE text ADD INDEX index_text(text(10));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

無事作成できました。このように、TEXT型やBLOB型のカラムに対してインデックスを張りたい場合は長さに注意が必要です。また、特にTEXT型のカラムにマルチバイト文字を使用した場合は、その分長さが減ることにも注意しましょう。

一般的にこれらの型を使用する際には、保管するデータは非常に大きなものが入っていることが多いです。そのためアクセスする際には必要に応じて、たとえばこれらの型を含むテーブルからSELECTする時に、なるべくこの型を使ったカラムを入れないようにしたほうが良いでしょう。大きなデータを扱う時には、加えてmax_allowed_packetinnodb_log_file_sizeを十分に大きくしないとエラーが発生してしまうこともあるので注意してください。

BLOB型に画像や動画などの大きなファイルを入れたい場合は、別途MySQL以外のストレージも検討したほうが良いかもしれません。

ENUM型

ENUM型は、テーブル作成時に明示的に宣言されたデータのリストから選んだ値を保存する事ができる型です。都道府県の名前のように固定的で不変なデータを扱うのに向いています。

ENUM型のメリットとしては、2点あげられます。まず1点目としては、テーブル作成時に要素として定義された以外の値が入らないという点です。2点目としては、リストにある文字列を直接保存するのではなく、リストの何番目にあるかというデータだけを保存します。そのため、文字列を直接保存するよりもストレージを効率的に利用することができます。以下のようなテーブルを作って確認してみましょう。

CREATE TABLE enum(prefecture ENUM('北海道', '青森県',....)) DEFAULT CHARSET=utf8mb4;

続けてデータの追加を行ってみます。以下のように単純にINSERTしてSELECTしてみました。

mysql> INSERT INTO enum(prefecture) VALUES('北海道');
Query OK, 1 row affected (0.00 sec)

mysql> select * from enum;
+------------+
| prefecture |
+------------+
| 北海道     |
+------------+
1 row in set (0.00 sec)

以上から、問題なくINSERTされていることがわかります。では、ENUMの指定にない文字列をINSERTしようとするとどうなるでしょうか?

mysql> INSERT INTO enum VALUES ('お米券');
ERROR 1265 (01000): Data truncated for column 'prefecture' at row 1

上記のようにENUM型で指定していない文字列をINSERTしようとすると、エラーになることがわかります。続けて以下のようにデータを追加してみます。

mysql> INSERT INTO enum(prefecture) VALUES('青森県');
Query OK, 1 row affected (0.00 sec)

さらに、以下のような一見何も結果の返ってこなさそうなSELECT文を実行してみます。

mysql> select * from enum where prefecture = 1;
+------------+
| prefecture |
+------------+
| 北海道     |
+------------+
1 row in set (0.00 sec)

不思議なことに「北海道」という結果が帰ってきました。何故prefecture = 1という条件式で北海道が出てくるのかといえば、ENUM型の定義で一番目の要素が北海道になっているからです。ENUM型は数字で保管されているものの、文字列と同様に扱えるのがENUM型の利点です。

ENUM型を使ったときのデメリットとしては、宣言されたデータの追加や削除などの変更にはALTER TABLEを行う必要があり少々大変です。また、数字を文字列としてENUM型として扱うと、以下のようにややこしくなってしまう事があるため注意が必要です。

mysql> CREATE TABLE enum2(num ENUM('2','1'));
Query OK, 0 rows affected (0.01 sec)

上記で作成したようなテーブルを用意して、以下の文字列でのINSERT文と数値でのINSERT文を実行してみます。

mysql> INSERT INTO enum2(num) VALUES('2');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO enum2(num) VALUES(2);
Query OK, 1 row affected (0.00 sec)

SELECT文を実行して結果を確認してみましょう。

mysql> select * from enum2;
+------+
| num  |
+------+
| 2    |
| 1    |
+------+
2 rows in set (0.00 sec)

文字列と数値で結果が異なることになりました。これは非常にややこしく間違いやすいため、数字に対してENUM型を利用するのは難しいことを理解していただけたかと思います。

他にもORDER BY等でこのカラムを利用して並び替えを行うと、宣言時に宣言した順にソートが行われるため、一般的なソート順序の文字列や数字の順にはならない事に注意が必要です。

mysql> select * from enum2 order by num ASC;
+------+
| num  |
+------+
| 2    |
| 1    |
+------+
2 rows in set (0.00 sec)

numカラムを利用して昇順に並べ替えを行うと、ENUMで定義した順番で並んでいることがわかると思います。

このように使い所が難しいものの、データの圧縮に非常に効果的なので、不変なデータを扱う場合には使用してみると良いかもしれません。

SET型

SET型はテーブル作成時に設定した文字列を0個以上選択することが出来るデータ型です。検証するために以下のようなテーブルを作成します。

mysql> CREATE TABLE set_table(elem SET('a', 'b'));
Query OK, 0 rows affected (0.01 sec)

このテーブルのelemカラムでは、以下で挙げるNULLと空文字列と'a'と'b'と'a,b'の合わせて5種類のデータが保存されます。以下のようなINSERT文を用意して確認してみましょう。

mysql> INSERT INTO set_table (elem) VALUES (NULL),(''),('a'),('b'),('a,b');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from set_table;
+------+
| elem |
+------+
| NULL |
|      |
| a    |
| b    |
| a,b  |
+------+
5 rows in set (0.00 sec)

上記から、5種類のデータが保存されていることがわかりました。一旦テーブルを削除して、追加で以下のように複数要素を同時にINSERT文を実行するとどうなるか、見てみましょう。

mysql> drop table set_table;
mysql> CREATE TABLE set_table(elem SET('a', 'b'));
mysql> INSERT INTO set_table (elem) VALUES ('a,b'),('a,b,a'),('a,a,a'),('b,a');

mysql> SELECT * from set_table;
+------+
| elem |
+------+
| a,b  |
| a,b  |
| a    |
| a,b  |
+------+
4 rows in set (0.00 sec)

このように同じ要素を入力した場合や、順序を入れ替えた場合にも先ほどと同様に、5種類の値が保存されています。また、複数同じ値を利用した場合にもユニークな値が保存されていることがわかります。

ここまでSET型がどのような値の保存の仕方をしているかを解説してきました。続けてSET型を利用する方法を少し説明していきます。

SET型は用いて値を保存したカラムを利用して検索を行うのは、他の文字列型に比べ少々面倒くさいです。elemの値が複数要素で構成されている時に保存されている文字列が、(,)区切りの文字列になってしまっているため、残念ながらlikefind_in_setを用いなければならないので、少し煩雑です。

次の例では'b'という要素が含まれているかという条件で検索を行っています。

mysql> SELECT * FROM set_table WHERE elem LIKE '%b%';
mysql> SELECT * FROM set_table WHERE FIND_IN_SET('b', elem)>0;
+------+
| elem |
+------+
| a,b  |
| a,b  |
| a,b  |
+------+
3 rows in set (0.00 sec)

このように(,)区切りで文字列を扱うのは大変なので、この型を使うのはあまりおすすめはしません。

まとめ

今回は、MySQLで扱える文字列型について簡単に紹介しました。一度決めてしまったスキーマを変更することは非常に大変ですので、それぞれにメリット、デメリットがある事を認識しましょう。そしてどの型を利用するかの判断を適切にできるようにしましょう。

おすすめ記事

記事・ニュース一覧