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

#近況報告

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

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ではテーブル生成時に各カラムのデータ型を以下のように定義します。

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')
);

カラムの定義は

myapp.sql

name varchar(20) unique,

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

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文を投げる場合

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

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

+----+-------+-------+-------------+
| 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文を見るとわかりますね。

myapp.sql

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

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

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

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

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

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

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のデータで考えてみます。

myapp.sql

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

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

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

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

例えば、以下のように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;

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

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

myapp.sql

select * from users where coins = 6;

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

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

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

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