第41回では、
検証環境
今回使用した環境は、
mysql> CREATE DATABASE characters; Query OK, 1 row affected (0.00 sec) mysql> use characters Database changed
MySQLの文字列型
MySQLには大きく分けて、
- CHAR
- VARCHAR
- BINARY
- VARBINARY
- BLOB
- TEXT
- ENUM
- SET
続いて各型の紹介をしていきます。CHAR型、
BINARY型とVARBINARY型
BINARY型とVARBINARY型の関係はCHAR型とVARCHAR型の関係と非常によく似ています。BINARY型はデータを固定長で扱い、
同様に確認してみましょう。まずは固定長の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)
どのように格納されているか、
mysql> select CONCAT('(',bin,')') from bin;
+---------------------+
| CONCAT('(',bin,')') |
+---------------------+
| (a b ) |
| (a b ) |
+---------------------+
2 rows in set (0.00 sec)
CHAR型の時とは逆に、
続けてVARBINARY型を見てみましょう。同様に型を作成し、
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
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型は、
mysql> CREATE TABLE text(text TEXT); Query OK, 0 rows affected (0.01 sec)
続けて、
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/
mysql> ALTER TABLE text ADD INDEX index_text(text(10)); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
無事作成できました。このように、
一般的にこれらの型を使用する際には、max_やinnodb_を十分に大きくしないとエラーが発生してしまうこともあるので注意してください。
BLOB型に画像や動画などの大きなファイルを入れたい場合は、
ENUM型
ENUM型は、
ENUM型のメリットとしては、
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)
以上から、
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)
さらに、
mysql> select * from enum where prefecture = 1; +------------+ | prefecture | +------------+ | 北海道 | +------------+ 1 row in set (0.00 sec)
不思議なことにprefecture = 1という条件式で北海道が出てくるのかといえば、
ENUM型を使ったときのデメリットとしては、ALTER TABLEを行う必要があり少々大変です。また、
mysql> CREATE TABLE enum2(num ENUM('2','1'));
Query OK, 0 rows affected (0.01 sec)
上記で作成したようなテーブルを用意して、
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)
文字列と数値で結果が異なることになりました。これは非常にややこしく間違いやすいため、
他にもORDER BY等でこのカラムを利用して並び替えを行うと、
mysql> select * from enum2 order by num ASC; +------+ | num | +------+ | 2 | | 1 | +------+ 2 rows in set (0.00 sec)
numカラムを利用して昇順に並べ替えを行うと、
このように使い所が難しいものの、
SET型
SET型はテーブル作成時に設定した文字列を0個以上選択することが出来るデータ型です。検証するために以下のようなテーブルを作成します。
mysql> CREATE TABLE set_table(elem SET('a', 'b'));
Query OK, 0 rows affected (0.01 sec)
このテーブルのelemカラムでは、
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)
上記から、
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)
このように同じ要素を入力した場合や、
ここまでSET型がどのような値の保存の仕方をしているかを解説してきました。続けてSET型を利用する方法を少し説明していきます。
SET型は用いて値を保存したカラムを利用して検索を行うのは、likeやfind_を用いなければならないので、
次の例では'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)
このように(,)区切りで文字列を扱うのは大変なので、
まとめ
今回は、