MySQLに複数選択項目のデータを保存する際にビットフラグを利用するが項目数が64個以上存在する場合どうすれば良いか？

Question

Webサービスを作成していて、ユーザに入力してもらったデータをデータベースに保存したいとします。

ユーザに入力してもらうデータの中には「あなたの興味のあるものにチェックしてください」というような複数選択可能な項目があり「テレビゲーム」「ファッション雑誌」「ハイキング」というような項目が64項目以上あるとします。

もし複数選択可能な項目数が64以下である場合、8バイトのBIG INT型などを利用して8x8=64の64ビットを利用することで一つの整数にチェックされた項目の全てのパターンを保存することが可能になります。

しかし、項目数が64項目以上ある場合にはBIG INTでは足りません。代わりにvarcharを利用したりbinaryを利用したりできるのかな？と思ったのですが、解決方法がいろいろ考えられるため、最もシンプルかつ手間のかからない方法としてどんな手法が考えられるか意見をいただきたいと思い質問いたしました。

よろしくお願いいたしますm(_ _)m

Comments

[NOTEPAD]

回答ではなく質問なので、こちらに書きます。Bit の計算は Webサービスがするならば BIGINT だろうと BINARY だろうと手間は変わらなくないですか？

[hojo]

どういうことでしょうか？

Answer 1

オーソドックスな形ということなら、以下のようになると思います。

CREATE TABLE `Users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `Hobbies` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `linkUserHobby` (
  `user_id` int(11) unsigned NOT NULL,
  `hobby_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`user_id`,`hobby_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer 2

ビットにした場合インデックスが効きませんから、「xxxに興味がある人」を抽出するのに、必ず対象テーブルをフルスキャンすることになるため得策ではありません。

Answer 3

アンケートをbit管理するのは賢明ではありません

• 可変的ではない
• 集計が煩雑になる
• 選択肢が２択になる

やるなら設問idと設問番号、ユーザーid、回答カラムで更正された
テーブルを用意するとよいでしょう

設問ごとの上限を持ちたくない場合はピボットで処理すると楽です

Comments

[hojo]

「可変的ではない」というのは、例えば3番目のビットにテレビゲームを割り当てた場合に、後から番号を変更したくなった時に変更しづらいということですか？
また「選択肢が２択になる」というのはどういう意味でしょうか？

[yambejp]

>後から番号を変更したくなった時に変更しづらい

概ねそういう意図です。

>「選択肢が２択になる」というのは

今回の命題ではチェックボックスですので問題なですが、
たとえばラジオボタンによる複数択一の場合も想定されます
その際bit処理では0か1しか選べないため同じ構造が利用できないということです。

[Panzer_vor]

横槍失礼いたします。

手っ取り早さをとるなら確かに、
・ビット文字列でフラグ管理をする
が正解かもしれません。

ただし長期的に見ると、
ビット文字列での管理は得策じゃありません。

特にアンケート項目のように、
①質問項目自体の意味が変わりうるもの
②質問項目数に増減があるもの

上記のような可変の特性を持ちうる情報は「列持ち・文字列持ち」から「行持ち」構造に変換するのが保守性・柔軟性が高いです。
(行持ちにすると桁数・カラム数などのしがらみから解放されますしね。)

ビット文字列のデメリットして大きいのは、項目を増える分には精度を増やすだけですが、減らす際はフル桁利用した状態だとALTER時にエラーでこけます。
(末尾カットなどでデータを無理やり加工すれば定義変更はできるけど、やり方をミスるとデータの完全性が担保できなくなり危険。)

yambejpさんが提示されているように、
対応する余裕があり、
かつアンケート内容が今後変わり得るなら、
①ユーザマスタ
②アンケート項目マスタ
③アンケート回答テーブル

の3つぐらいに分けるのが無難ですね。

[hojo]

ユーザテーブルを除き、一つの項目に対して2つのテーブルを用意するのが大袈裟なように感じているのですが、該当する項目に対応するテーブルをENUM型を利用したものを利用することで1項目1テーブルにする方法がこちら（http://stackoverflow.com/questions/18486218/how-to-implement-mysql-set-concept-with-over-64-members）に掲載されているように思えたのですが、こちらについてはどう思われますでしょうか？

[Panzer_vor]

> hojoさん
http://qiita.com/Inagawa/items/6e9745d2598aa713a1f3

提示されている手法は、MySQL実装依存となるため、

・今後別のDBMSへの移行はない
・アンケート項目数に変更あるのは稀
上記であれば採用を検討しても良いかと思います。

ただ使う上では実装依存となることと、制約に柔軟性を持てないというデメリットを飲む必要はあります。

ちなみにSQLアンチパターンの「サーティワンフレーバー」という項に、
MySQLのenum型についての言及があるので、
興味があれば調べると良いです。
(リンク先でも触れてますが)

[退会済みユーザー]

ラジオなら

100
010
001

と選択項目数のビットを使えば規定数より増やせないな

[hojo]

> Panzer_vor
ありがとうございます！SQLアンチパターンを購入しましたのでこれから読んでみます！