MySQLのset型について理解する。インデックス番号を付与して複数データを定義しよう

#近況報告

エンジニア転職成功しました。YouTubeでエンジニア転職したい方向けに情報発信しています。

https://www.youtube.com/channel/UCcXQG8lu_EdyP2Cbhtz1bcA



DBを構築・管理する際に欠かせないのがRDBMS（関係データベース管理システム）ですが、
僕はその中でも有名な「MySQL」というRDBMSを使ってDBの構築方法を勉強しています。

MySQLではDBを構築後、SQL文を投げることでDBを操作できます。

基本的には

1. アカウント・DB作成
2. Table作成
3. カラム(列)のデータ型を定義
4. レコードに値を挿入(Insert Intoなど)
5. 値をいじる(作成/更新/削除)
6. データ取り出し(SELECT * FROM テーブル名 ・・・)

という順で操作していくと思います。

その中で、データ型の勉強をしている際に「set」というデータ型の仕組みが面白いと感じたので、
今回きちんと理解する上でもQiitaに投稿したいと思います。

#set型とは？何ができるの？

set型とは、
**カラムに対して、引数に取った値を「複数のデータ」として格納できるデータ型**

のことを言います。


例えば、MySQLではテーブル生成時に各カラムのデータ型を以下のように定義します。

```sql:myapp.sql
-- usersテーブルを生成し、「id」「name」「score」「coins」というカラムを設定する
create table users (
  id int unsigned primary key auto_increment,
  name varchar(20) unique,
  score float default 0.0,
-- coinsに対してはset型を定義する
  coins set('gold', 'silver', 'bronze', 'white', 'black')
);
```

カラムの定義は

```sql:myapp.sql
name varchar(20) unique,
```

のように、
「カラム名」「データ型」「種類(上記では一意性のuniqueを付けてる)」
という順で定義しますが、setの場合だと、

```sql
coins set('gold', 'silver', 'bronze', 'white', 'black')
```

のように、setに5つの引数を定義してますね。

setは、複数のデータを格納できるデータ型ですから、
上記5つの値をcoinsと言うカラムに対して、set型で付与している訳です。

つまり、insert intoなどの文でレコードにデータを追加する前に、先に値を定義していると言うことです。
ただ、set型ではあくまで上記5つのデータを「定義」しているだけで、**格納はしていません**。

格納できる準備が整った、と言うことになります。

ここからが本題。


##set型の特徴

set型には、以下の特徴があります。

- setに定義していないデータをinsertすると、レコードにデータがinsertされない
- setで定義したデータは、カンマ区切りで複数のデータをinsertできる
- setで並べた順番でデータが定義されるため、insert時に並べた順番は無視される
- setで並べたデータには、前から順にインデックス番号が「2 の n 乗 2n」で付与されている
- 最大で64個のメンバーを格納可能

一つ一つ見ていきましょう。

###setに定義していないデータをinsertすると、レコードにデータがinsertされない

例えば以下のようにSQL文を投げる場合

```sql:myapp.sql
-- usersテーブルを作成
create table users (
  id int unsigned primary key auto_increment,
  name varchar(20) unique,
  score float default 0.0,
  coins set('gold', 'silver', 'bronze', 'white', 'black')
);

-- name,score,coinsにそれぞれ'yuuki','5.8','gold,silver,blue'と言う値を追加する
insert into users (name, score, coins) values ('yuuki', 5.8, 'silver,gold,blue');

-- usersテーブルから全てのカラムとレコードを読み出す
select * from users
```

抽出結果はこうなります。

```sql
+----+-------+-------+-------------+
| id | name  | score | coins       |
+----+-------+-------+-------------+
|  1 | yuuki |   5.8 | gold,silver |
+----+-------+-------+-------------+
```

coinsに対して「gold,silver,blue」をinsertしたのに、blue」というデータのみinsertされていませんね。

**なぜかと言うと、coinsに対してblueはset時に定義していないからです。**

setでcoinsに対して明確に格納できるデータをルール化したので、set時に存在しないデータをinsertするとその値は格納されず無視されます。


###setで定義したデータは、カンマ区切りで複数のデータをinsertできる

これは先程のinsert文を見るとわかりますね。

```sql:myapp.sql
insert into users (name, score, coins) values ('yuuki', 5.8, 'silver,gold,blue');
```

coins対して、カンマ区切りで値をinsertしています。

普通に見ると、silver,gold,blueは一つの文字列として格納しているように見えますが、
set型で定義したカラムに対しては、**カンマで一つ一つのデータを複数に分けてレコードに格納しています。**

その意味は次でわかります。


###setで並べた順番でデータが定義されるため、insert時に並べた順番は無視される

先ほどの抽出結果のcoinsカラムのデータの順番に注目してください。

```sql:myapp.sql
coins set('gold', 'silver', 'bronze', 'white', 'black')

insert into users (name, score, coins) values ('yuuki', 5.8, 'silver,gold,blue');

+----+-------+-------+-------------+
| id | name  | score | coins       |
+----+-------+-------+-------------+
|  1 | yuuki |   5.8 | gold,silver |-- ←「silver,gold」ではなく、「gold,silver」となっています。
+----+-------+-------+-------------+
```

insert intoで「siver,gold」と言う順番でinsertしたのに、
抽出結果は何故か「gold,silver」になっていますね。


これはどういうことかと言うと、set時に「gold,silver」と言う順番でデータを定義したので、
**たとえinsertで順番を変えても（silver,goldにしても）、順番は変わらず、抽出すると必ず「gold,silver」という順番で結果が表示されます。**

つまり、**set時にデータの順番（インデックス番号）を強制的に決めている、ということになります。**


##setで並べたデータには、前から順にインデックス番号が「2 の n 乗 2n」付与されている

2nとは、nを2の累乗と考えた時の計算結果を式で表したものになりますが、
**setで並べたデータには、前から順に「2n」のインデックス番号が付与されています。**

2nのnを、0,1,2,3,4・・・という順に考えると

0番目＝1（n=0で2n）
1番目＝2(n=1で2n)
2番目＝4(n=2で2n)
3番目＝8(n=3で2n)
4番目＝16(n=4で2n)

となります。

これを先ほどのsetのデータで考えてみます。

```sql:myapp.sql
  coins set('gold', 'silver', 'bronze', 'white', 'black')
```

- gold＝1
- silver＝2
- bronze＝4
- white＝8
- black＝16

それぞれのデータに、上記のインデックス番号が付与されているという訳です。

で、だから何？という話ですが、これはselect時に意味を成します。

例えば、以下のようにsql文を投げてみます。


```sql:myapp.sql
-- usersというテーブルを作成
create table users (
  id int unsigned primary key auto_increment,
  name varchar(20) unique,
  score float default 0.0,
  coins set('gold', 'silver', 'bronze', 'white', 'black')
);

-- coinsに対して「silver,gold,blue」というデータを格納
insert into users (name, score, coins) values ('yuuki', 5.8, 'silver,gold,blue');

-- coinsの値のインデックス番号が合計で3であるレコードを抽出
select * from users where coins = 3;

```

すると、抽出結果はこうなります。


```sql:myapp.sql
+----+-------+-------+-------------+
| id | name  | score | coins       |
+----+-------+-------+-------------+
|  1 | yuuki |   5.8 | gold,silver |
+----+-------+-------+-------------+
```

これはどういうことか、と言うと、insert時に追加できている「silver,gold」と言う値にはそれぞれ、2nで

- gold = 1
- silver = 2

と言うインデックス番号が付与されているため、
**1 + 2で合計で3となる、インデックス番号が合計で付与されているデータがcoinsカラムに存在するレコードを
抽出しています**

つまり、上記の抽出を**（select * from users where coins = 3;）**で行なっていると言うことですね。


その証拠に、selectでwhere coins = 6としてみます。

```sql:myapp.sql
select * from users where coins = 6;
```

こうすると、抽出結果は0となります。


##setの何が面白いと思ったか

基本情報技術者の勉強で学んだ2進数がset型で活かされていたので、
なんとなくsetというデータ型に親近感を覚え、理解するにつれて面白く感じました。


実際にどういったシーンでset型を使うのか、
まだ分かってないのでこれから学んでいこうと思います。