最近知ったSET型について
MySQL Casual Advent Calendar 2017の15日目の記事です。
MySQLには古くからSET型というカラムの型がありまして、知らない人もいるかもしれません。
私は聞いたことあるぐらいで、使ったこと、使っている現場に遭遇したことなかったです。
今回は初めてめぐり逢ったSET型について、今更ながら調べた話です。
ググってみるとあまり使用することをおすすめしないことが書いてあることが多いのですが、その理由は少しわかりました。
ざっくりSET型とは
- 文字列オブジェクト
- 0個以上のメンバーを格納
- 最大 64 個 の メンバー
- ENUM型と似ているが、ENUM型は1つのメンバーのみ格納できるが、SET型は複数メンバー可。
- 複数のメンバーをセットする場合はカンマで区切る
ex) set('a','b')の場合、以下が格納可能
null 'a' 'b' 'a,b'
- ストレージの消費量
| 要素数 | バイト数 |
|---|---|
| 1 - 8 | 1 |
| 9 - 16 | 2 |
| 17 - 24 | 3 |
| 25 - 32 | 4 |
| 33 - 64 | 8 |
- 2 進値 と 10 進値 で管理
ex) set('a','b','c','d')の場合
| 要素 | 2進値 | 10進値 |
|---|---|---|
| 'a' | 00000001 | 1 |
| 'b' | 00000010 | 2 |
| 'c' | 00000100 | 4 |
| 'd' | 00001000 | 8 |
'a,d' は 10進値 1 + 8 = 9で表すこともできる。
SET型の詳細はこちら。MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.4.5 SET 型
検索でインデックスが効かない
要素個別ではなくカラム全体にインデックスが作成される。
特定の要素を含むレコードを検索するときは FIND_IN_SET() 関数 か LIKE 演算子を使用するため、インデックスが効かない。
ex) 'b'を含むレコードを検索
SELECT * FROM t0 WHERE col like '%b%'; or SELECT * FROM t0 WHERE FIND_IN_SET(col,'b');
以下のように完全一致で 'a,d' を検索する場合は インデックスは効く。
ex) 'a,d' または 10進値 1 + 8 = 9
SELECT * FROM t0 WHERE col = 'a,d'; or SELECT * FROM t0 WHERE col = 9;
また カラム名 + 0 することで、そのレコードがもつ10進値の値を取得できるので、仮想列使ってうまくやれば特定の要素でもインデックス使えるかも。(全然思いついてないけど・・)
SELECT col , col+0 FROM t0 WHERE col = 'a,d'; +------+--------+ | col | col +0 | +------+--------+ | a,d | 9 | +------+--------+
要素リスト追加の罠
要素の追加は基本は即時で完了しますが、前述のストレージの消費量のバイトが増えるタイミングはALGORITHM=COPY の ALTER TABLE 発生。
9 , 17 , 25 , 33個目の要素追加時にテーブル全体のwrite lockが発生するので注意が必要。
もちろん、ALGORITHM=COPYなのでオンラインDDLが不可。
- ex ) 要素8 , 9個目追加
mysql > SHOW CREATE TABLE t0;
+-------+----------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------+
| t0 | CREATE TABLE `t0` (
`col` set('a','b','c') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+----------------------------------------------------------------------------------------------------+
mysql > ALTER TABLE t0 modify col set('a','b','c','d','e','f','g','h');
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql > ALTER TABLE t0 modify col set('a','b','c','d','e','f','g','h','i');
Query OK, 2 rows affected (0.10 sec) ← ALGORITHM=COPYで実行された
Records: 2 Duplicates: 0 Warnings: 0
要素リストのリネームができない
これはENUM型も同様ですが、
すでに変更する要素のデータが存在する場合は、リネームできない。
mysql > SHOW CREATE TABLE t0;
+-------+----------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------+
| t0 | CREATE TABLE `t0` (
`col` set('a','b','c') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+----------------------------------------------------------------------------------------------------+
mysql > SELECT * FROM t0;
+------+
| type |
+------+
| a,b |
| a |
+------+
mysql > SET sql_mode='STRICT_TRANS_TABLES';
mysql > ALTER TABLE t0 modify col set('zz','b','c');
ERROR 1265 (01000): Data truncated for column 'col' at row 1
Error (Code 1265): Data truncated for column 'col' at row 1
Error (Code 1317): Query execution was interrupted
変更する要素のデータが存在しない場合は、ALGORITHM=COPY の ALTER TABLE 発生。
mysql > SELECT * FROM t0;
+------+
| type |
+------+
| a,b |
| a |
+------+
mysql > SET sql_mode='STRICT_TRANS_TABLES';
mysql > ALTER TABLE t0 modify col set('a','b','zz');
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
以上、最近知ったSET型についてでした。
